Multi-statement Table-Valued Function

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With &&"sys&&"?
Hi all,
 
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
 
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
 
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything.  Does anyone know for certain if this is a bug in SQL2K?
 
Thanks, Jos
 
Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/
 
PRINT  @@VERSION
go
 
PRINT  'Scalar function with name "sys_" creates ok...'
go
 
CREATE FUNCTION sys_test
       ()
RETURNS       INT
AS
BEGIN
       RETURN 1
END
go
 
DROP   FUNCTION sys_test
go
 
PRINT  ''
go
 
 
PRINT  'In-line table-valued function with name "sys_" creates ok...'
go
 
CREATE FUNCTION sys_test
       ()
RETURNS       TABLE
AS
RETURN SELECT 1      c
go
 
DROP   FUNCTION sys_test
go
 
PRINT  ''
go
 
 
PRINT  'Multi-statement table-valued function with name "sys_" generates error 1706...'
go
 
CREATE FUNCTION sys_tmp
       ()
RETURNS       @t     TABLE
       (c     INT)
AS
BEGIN
 
       INSERT INTO @t       VALUES (1)
 
       RETURN
 
END
go
 
DROP   FUNCTION sys_test
go
 
PRINT  ''
go
 
/*
----------------------------------------------------------------------------------------------------
*/
 
And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
       May  3 2005 23:18:38
       Copyright (c) 1988-2003 Microsoft Corporation
       Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
 
Scalar function with name "sys_" creates ok...
 
In-line table-valued function with name "sys_" creates ok...
 
Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.

View Replies !

---

Inline Table-valued Function With Multi-value Parameter
Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View Replies !

---

Multi-statement Table-valued Functions
Hello

I am trying to do the following:

1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter.
2. Then, use it like this: "Select * from table T, mstvF1( T.id )"

It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id.

If I do
Select * from table T, mstvF1( 5 ), then it works.

Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T?

Thanks for any help.

View Replies !

---

Multi Statement Table-valued UDF Without Declaring Columns?
Hey
I have created a multi-statement
table valued function

alter 
function
fn_x(@x int)returns
@tbl table
(
          position int identity primary key,          i
int)
as
begin         
insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          returnend
 



Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'" 
The other solution is declaring
each udf separately as one statement udf.Thanks 

View Replies !

---

Can I Use If Statement In A Table Valued Function?
hi,
I am using a function in sql server 2005 like this:
...... myfunction(... @FlagOn int)
.......
begin
return
(

if(@FlagOn = 1)
select * from.......
else
select * form....
)
end
 
But it keeps complaining there is some syntax error around if. What is it?
 
Thanks.
 

View Replies !

---

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View Replies !

---

In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,
 
I executed the following sql script successfuuly:
 
shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO
 
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
 
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
 
shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

 
I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

 
Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
 
Thanks in advance,
Scott Chang

View Replies !

---

Table-Valued Function
I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this?
 
Thanks

Mangala

View Replies !

---

Trigger On Table-valued Function?
Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.

View Replies !

---

Table Valued Function And Constraints
 

Is it possible to define a constraint for Primary Key on more than 1 column or an alternate index on a column in a return table from an inline table valed function?
 
Example Header:
 

alter FUNCTION [dbo].[fntMetaFrame] (@ii_CompanyID int)

RETURNS @tbl_MetaFrame TABLE ( pk_Key int Identity(1,1) primary key,






ObjectID int ,







Seq int null )



 
I want the primary key to be pk_Key, ObjectID
 
OR
 
I want to add another index on ObjectID.

View Replies !

---

How To Join Using A Table-valued Function?
Hi there.    I've hit some gap in my SQL fundementals.   I'm playing with table-valued functions but I can't figure out how to join those results with another table.   I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this.   What am I misunderstanding here?

The Given Objects:
function Split(stringToSplit, delimiter)   returns table (column:  token)
table Words  (column: Words.word)     -- table of predefined words
table Sentences  (column:  Sentences.sentence)    --  table of sentences; tokens may not be in Words table, etc

The Problems:
1) how do I query a set of Sentences and their Tokens?   (using Split)
2) how do I join tables Sentences and Words using the Split function?

The Attempts:
A)
select word, sentence, token
from Words,
       Sentences,
       dbo.Split(sentence, ' ')      -- implicitly joins Split result with Sentences?
where  word = token

resulting error:   "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

B)
select word, sentence
from Words, Sentences
where word in (select token from dbo.Split(sentence, ' '))   -- correlated subquery?

resulting error:   "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

View Replies !

---

Indexing Table-valued Function?
I am using a multi-statement table-valued function to assemble data from several tables and views for a report.  To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row.  Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns. 
 
The problem I'm having is that the UPDATEs are taking forever to execute.  I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns.
 
In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function.
 
Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions?  I think that would improve the UPDATE performance dramatically.
 
Thanks,
Lee Silverman
JackRabbit Sports

View Replies !

---

Join With Table Valued Function
Hi,

I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method ..
eg..
SELECT A.Col1,A.Col2,B.Col1,B.Col2
FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B
ON A.Col1 = B.Col1

any body help me ... thanx in advance..

View Replies !

---

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View Replies !

---

Table-valued Function Return Error
 

I have this tsql

Declare @IDtable table(id uniqueidentifier, [Value] varchar(50))

Declare @iDoc Int

exec sp_xml_preparedocument @iDoc Output, @Data

insert into @IDtable(id,[Value])

Select *

From OpenXML(@iDoc, '/listing/Item', 2)

With (Id VarChar(40),

Value varchar(20))

exec sp_xml_removedocument @iDoc

 
but when I tried to right table value function of it so I can use it many place with just change paremeter value like this
 

ALTER FUNCTION [dbo].[splitXMLvalue](@editors xml)

RETURNS @etable table(id uniqueidentifier, [name] varchar(50))

AS

BEGIN





Declare @iDoc Int

exec sp_xml_preparedocument @iDoc Output, @Data

insert into @IDtable(id,[Value])

Select *

From OpenXML(@iDoc, '/listing/Item', 2)

With (Id VarChar(40),

Value varchar(20))

exec sp_xml_removedocument @iDoc

--select id as parentId, [Value] as parentValeu from @IDtable

RETURN

END

 
it gave me this error
 
 

Msg 557, Level 16, State 2, Line 1

Only functions and extended stored procedures can be executed from within a function.

 
can any one help me what I'm doing wrongthanks

View Replies !

---

Indexes On Table Variable Of Table Valued Function
Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View Replies !

---

Table-Valued Function Result Vs. Calculation Table
 

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

 DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

View Replies !

---

Problem Passing A Variable Into A Table-valued Function
Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View Replies !

---

Performance Of Table-valued Function And Execution Plan
I am using SQL2005 EE with SP1. The server OS is windows 2K3 sp2
 
I have a table-valued function (E.g. findAllCustomer(Name varchar(100), gender varchar(1)) to join some tables and find out the result set base the the input parameters.
 
I have created indexes for the related joinning tables.
 
I would like to check the performance of a table-valued function and optimize the indexing columns by the execution plan.
 
I found the graphic explanation only show 1 icon to represent the function performance. I cannot find any further detail of the function. (E.g. using which index in joinning)
 
If I change the function to stored procedure, I can know whether the T-SQL is using index seek or table scan. I also found the stored procedure version subtree cost is much grether that the table-valued function
 
I would like to know any configureation in management studio can give more inform for the function performance?
 
Thanks
 
 
 

View Replies !

---

Temporary Table Vs. Table Valued Function
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call. 

 

View Replies !

---

Table-valued Function Run Once For Each Row In A Table Variable.
I have a stored produre.  Inside this stored procedure I have table variable with one column.  Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function.  The table-valued function may return any number of rows.  I would like all the rows the TVF returns to be returned from the stored procedure as a single result set.  I would also like to do this without defining a table variable to hold the results of the table-value function.
 



Code Snippet

declare @IdTable table
(
 EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees
 
/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )

 
 



 
Any help is very much appreciated.
Andrew
 

View Replies !

---

Table-valued Function Into A @table Variable
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?




ALTER   PROCEDURE [dbo].[rpt]

(


@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'

)
AS


--split CourtIDs into a table
DECLARE @tbCourtIDs     table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')

View Replies !

---

Table-valued Function Does Not Accept Chinese Characters As Parameter
I have a table-valued function in mssql 2005 as below:
ALTER FUNCTION fn_test{   @test nvarchar(1000)}RETURNS@TEMP TABLE{   test nvarchar(1000)}ASBEGIN   INSERT INTO @TEMP   SELECT @test
   RETURNEND
Everytime, I passed in chinese character (@test), such as æ¸¬é©—, the function will return ????.  What should I do for the table-valued function, so that the chinese character can be passed in?  Please help.
Note: I can search and get the chinese characters if I use stored procedures; and the columns in the tables can store chinese chararcters as well.   Only table-valued function is not working with the chinese characters.  Is it a bug from MSSQL 2005?
 
 

View Replies !

---

System.DirectoryServices Performance Issue In Table-valued Function
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).

Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?

Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants"    ''' <summary>    ''' The connection string for Active Directory.    ''' </summary>    'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string>    ''' <summary>    ''' The LDAP search filter need to find a user in Active Directory.    ''' </summary>    'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region    ''' <summary>    ''' Gets all active directory groups for the user.    ''' </summary>    ''' <returns>All dataset permissions for the user.</returns>    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _    Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable        ' Setup the active directory search.        Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING)        searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName)        searcher.SearchScope = SearchScope.Subtree        searcher.PropertiesToLoad.Add("distinguishedname")        ' Run the active directory search.         Dim result As SearchResult = searcher.FindOne()        Dim userEntry As DirectoryEntry = result.GetDirectoryEntry()        Dim userGroups As New ArrayList        GetActiveDirectoryGroupsForEntry(userEntry, userGroups)        Return userGroups    End Function    Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars)        GroupID = New SqlChars(CType(source, String))    End Sub    ''' <summary>    ''' Recursively gets the active directory groups for the directory entry.    ''' </summary>    ''' <param name="entry">The active directory entry.</param>    ''' <param name="groups">The list of groups.</param>    Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList)        For i As Integer = 0 To entry.Properties("memberOf").Count - 1            Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString())            groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString())            GetActiveDirectoryGroupsForEntry(memberEntry, groups)        Next    End SubEnd Class

View Replies !

---

Can A Stored Procedure Called From An Inline Table-Valued Function
Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View Replies !

---

Handling Large Queries In A Table Valued User Defined Function
Hello,

We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .

What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.

Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:

Sec /  Actual Rows Returned
0        0
10      237
20      447
30      481
40      585
50      655
60      725
70      793
80      860
90      940
100    1013
110    1081
120    1115
130    1151
140    1217
150    1250
160    1325
170    1325
180    1430
190    1467
200    1502
210    1539
220    1574
230    1610
240    1645
250    1679
260    1715
270    1750
280    1787
290    1822
300    1857
310    1892
320    1923
330    1956
340    1988
350    1988
360    2022
370    2060
380    2094
390    2094
400    2130
410    2160
420    2209
430    2237
440    2237
450    2274
460    2274
470    2308
480    2342
490    2380
500    2380
510    2418
520    2418
530    2451
540    2480
550    2493
560    2531
570    2566

It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).

The minute breakdown during my trial is as follows:
1 = 655   (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)

As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.

Allow me to explain the code in further detail:

[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{

ArrayList listItems = new ArrayList();

SPToSQLService service = new SPToSQLService();

[...]

DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service

//Load the DS to the ArrayList


return listItems;
}


public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{

ArrayList item = (ArrayList) obj;


col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}

As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.


Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.

Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.

Thanks,

Dragan Radovic

View Replies !

---

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.

 




Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}



return (IEnumerable)myList;

}

}

 

When command.ExecuteReader is called, I am getting an "Object not defined" error.  However, the stored procedure can be used in SQL Management Studio just fine.

 




Code SnippetEXEC GetObject 'Item'

 

Is there some sorf of trick I am missing?

 

Thank you!

 

View Replies !

---

IIF And Multi-valued Parameters
Hi

 

i have a report that can show upto 8 charts, dependant on the selection(s) made in  a multi-valued parameter.

 

basically, the report gets loads of data back from DataCube.

 

if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.

 

but

i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart

 

so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)

 

am doing this by trying to get the selected value or "xxxxx" if noting selected 

to hide the second chart when only one category has been selected, tried

 

e.g. for the third chart

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

Parameters!LOOKUPITEMCategoryDescription.Label(2),

"xxxxx")

 

doesnt work - gives an #Error as a reult

 

But it works fine when there are > 2 categories selected

 

also

 

this works fine when there is 2 or >2 category selected

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

"more than 2",

"not more than 2")

 

 

it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!

 

help please

 thx

View Replies !

---

IIF And Multi-Valued Parameters
I have a MultiValued parameter called Owners, which stores Owner Ids.
 
I wish to set the value of a textbox to the following:
 
=iif(3 IN(Parameters!Owners.Value),"foo","bar")
 
but the "IN" is not recognised.
 
How can I achieve this functionality using an expression?
 
Thanks,
Kate

View Replies !

---

&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
    -- Add the parameters for the function here
    @dateStart DATETIME,
    @dateEnd DATETIME
)
RETURNS @arDetail TABLE 
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)

INSERT INTO @arTemp
   SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
  (SELECT SUM(Pims.AmtReq)
       FROM Pims
       WHERE Pims.Insurer = @arTemp.Insurer AND
             Pims.NABP = @arTemp.NABP AND
             Pims.NumRx = @arTemp.NumRx
  );

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View Replies !

---

Problem With Multi-valued Parameters Since SP1
Hi,

after the installation of SP1 I have a problem with multi-valued report parameters.
The option to select all values are gone.
Is there a bug within SP1?

Before the installtion of SP1 multi-valued report parameters works fine, the option to select all values was added automatically within the preview in Visual Studio 2005 and in published reports on the server.

The server was migrated from SQL2000 SP4 to SQL2005.

So long
Lemmi

View Replies !

---

Displaying Multi-valued Parameters
Hi There.

 

I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.

This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!

 

Thanks, Mike

View Replies !

---

Passing Multi-valued Parameter To A Sp
Hello,

 

I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.

 

Any help appereciated.

 

Thanks

View Replies !

---

Charts And Multi-valued Parameters
I am creating a Line chart report from a table. The source table includes a code column (String), date column (Datetime), and 12 statistic columns (Int). The dates are end of month dates only. ("1/31/2006", "2/28/2006","3/31/2006", etc.)  There are about 8 different codes for each month. The statisic columns are the totals for the month for each code for a particular statistic.

 

PeriodEndDate  Code  Statistic1  Statistic2  Statistic3  Statistic4 ...
 XX/XX/XXXX        XX         999          999               999            999  ...

 

On my line chart I want to show one or more statistics for one year for one or more codes. (A line for each statistic for a given code)  The report has multi-valued parameters for the codes and the statistics. I have figured out how to create the chart for one or more codes for a given statistic. How can I select one or more statistic?  Can any one help me out?

Thanks,

Fred

 

View Replies !

---

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all,
 
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed.  Please help and advise.
 
Thanks in advance,
Scott Chang
 

View Replies !

---

Parameterizing Multi Valued Parameter In Data Set
Hi,
I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.
To consider a sample code:
 
EXEC('select * from country where country_key in ('+@country+')')
 
where @country is a multi-valued report parameter.
 
Regards,
Emil

View Replies !

---

Selecting All Values In Multi-Valued Parameters
 
Is there a way to fill a multi value parameter with values from a dataset, and set them all as checked without re-executing the query? 
 
Thanks
BobP

View Replies !

---

Setting Defaults On A Multi-valued Parameter
I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report).  However, using RS2005, I can't seem to figure out how to do this.  I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.

Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty?  Checking "Allow Null" or "Allow Blank" doesn't fix this problem.

I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this.  I'm not sure if it will let you do that anyway...

Trent

View Replies !

---

Passing Multi-Valued Parameter To Subreport
I have a multi-valued parameter that I want to pass to a subreport. The values are 11, 12, 13, and 14.

So here's what I've done:

1. For the properties of the clickable field of the first report, I have gone to the Navigation tab and chosen the subreport in the "jump to report" pulldown.

2. Then I clicked on the Parameters button and added a parameter name for the multi-valued parameter I am trying to send. For the Parameter value, I have tried 11,12,13,14. I have also tried =Split(11,12,13,14)

3. On the subreport, I create a report parameter with the same name, data type is string, multi-value is checked, and the available values pulldown is populated by a query.

So I run the first report and click on the link that brings me to the subreport, but all the subreport shows is the results for 11, not 12, 13, and 14.

How can I get the second report to understand that I want it to show all the records related to 11, 12, 13, and 14? If I bypass the first report and simply use the drop-down to choose 11, 12, 13, and 14 in the subreport, it works fine. I just can't seem to figure out how to correctly have the first report tell the subreport that it wants 11, 12, 13, and 14.

Thanks in advance for any advice!

Dan

View Replies !

---

Examine Multi Valued Parameters In Expressions
I am trying to selectively show or hide a table based on the values in a multi valued parameter.  How can I examine all values (simulate the like functionality) within an expression?

View Replies !

---

Passing Multi-Valued Parameters Between Reports.
 

Hello,
 
I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
 
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
 
Can any one help me with this. Its very urgent.
 
Thanks,
SqlNew

View Replies !

---

Referencing A Multi-valued Parameter In A Dataset
 

Hello,
I am attempting to create a cascading parameter.
Parameter's 1 & 2 are multi-valued.
Datasets 1 & 2 supply Parameter's 1 & 2.
The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'.
The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1.
Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing.
What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?
 
Any help is appreciated.

View Replies !

---

Accessing Multi-valued AD Objects Through ADSI
Hello,
 
I have to pull the AD field businessCategory into a SQL database as part of a report I need to run regularly. I have set up a linked server to the domain controller in SQL and I can pull most values using the OPENQUERY statement with no problem. It works great. My problem is that businessCategory is not a single valued attribute and I am unable to change that property. I have read that this is a limitation of the ADSI component in SQL (http://support.microsoft.com/kb/299410) does anyone have another idea of how to do this? I know my way around SQL server and Transact SQL but I am not much with VB€¦.
 
 
Any help would be greatly appreciated!!!!
 
Nick

View Replies !

---

String Type Multi-Valued Parameter Issue
Hi There.

 

I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about?

 

Thanks, Mike

View Replies !

---

Returning All Selected Values In Multi-valued Parameter
How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where

(Expression)

=Fields!LOCATION_ID.Value 

(Operator)

=

(Value)

=Parameters!Loc.Value(0)

This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.

 

View Replies !

---

Working With Multi-valued, Query Based Parameters
   I am currently working with 3 multi-valued parameters whose data sources are queries.  The first 2 are required to have entries, 100% of the time, but the third one may or may not require selecting a value.  Parm3's data source is filtered by the selections of Parm1 & Parm2.  The data source for my report references Parm3 in a derived table that is then LEFT OUTER JOINed.

 In the cases where the report does not require any selection from Parm3 I am still required to pick at least 1 entry.  Can anyone shed some light on this, or provide a solution so I am not forced to pick any if I don't want? 

 

Thanks in advance,

 

Michael

View Replies !

---

How To Change Report Name Based On Multi Valued Parameter
Hi All,
 
I'm having trouoble with changing my report name when I swtich to different parameter values.
 
I use the following expression.
 

IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total"))

 
This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work.
 
Is there any solution for this?
 
Thanks

View Replies !

---

Expanding The Multi-Valued Parameter Dropdown Menu
Hello Everyone,
I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects.
Is this a bug in SSRS, and is there another way to accomplish this. Please let me know.
 
Thank You!!!
 

View Replies !

---

How Do You Do A Multi-Table Insert In One Statement?
Is there a way to insert data into two tables with one statement in my SPROC? Something like: Insert into ThisTable,ThatTable (my columns) values (my values). I don't want to have to write two statements if I can do it with one.

View Replies !

---

Building A Multi Statement Table UDF
Hi All,While in the process of building my table (40 or so Insert statments)can I then query ("select * from @Table_variable") and use the resultsup to theat point for another insert into @Table_varible? If you lookfor stepID -15 I have commented that section out due to it notretuning the correct values.Thank you in advanceStephen PattenTable Code:ALTER FUNCTION dbo.BidContract(@MixHeaderID int,@MaterialEffectiveDate nvarchar(10),@LaborEffectiveDate nvarchar(10),@AreaTypeID int,@NailingParam int,@TapingParam int)/*@MixHeaderID int = 2,@MaterialEffectiveDate nvarchar(10) = '2003-01-01',@LaborEffectiveDate nvarchar(10) = '2003-01-01',@AreaTypeID int = 1,@NailingParam int = -1,@TapingParam int = -1*/RETURNS @table_variable TABLE (IDintIDENTITY(1,1) PRIMARY KEY CLUSTERED,StepIDdecimal (18,1)NOT NULL ,JobMasterIDintNOT NULL ,MixHeaderIDintNOT NULL ,BidSubtypeIDintNOT NULL ,WorkTypeIDintNOT NULL ,UnitNamenvarchar (64)NOT NULL ,UnitQuantityintNOT NULL ,ItemDescriptionnvarchar (256)NOT NULL ,ItemQuantitydecimal(18, 4)NOT NULL ,ScaleValuedecimal(18, 4)NOT NULL ,ExtendedPricedecimal (18,4)NOT NULL ,IsVisiblebitNULL ,WSQtyAdjdecimal(18,4)NULL)ASBEGIN/*/////////////////////////////////////////////////////////////////////////////////////////////MATERIALSUBTYPE 1/////////////////////////////////////////////////////////////////////////////////////////////*//*STEP -1WALLBOARDALL MATERIAL THAT HAS A MATERIAL CATEGORY OF 1 (WALLBOARD)NOTE: THIS WILL ALSO GIVE YOU THE TOTAL SQUARE FEET TO BE USED INLATER CALCULATIONS*/INSERT INTO @table_variableSELECT - 1 AS StepID, MixHeader.JobMasterID,MixLineItem.MixHeaderID, 1 AS BidSubTypeID, 0 AS WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription,SUM(MixLineItem.FloorPlanQuantity *(FloorPlanLineItem.Quantity * Dimension.Area)) / 1000 AS ItemQuantity,MaterialScale.Price AS ScaleValue,SUM(MixLineItem.FloorPlanQuantity *(FloorPlanLineItem.Quantity * Dimension.Area)) / 1000 *MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible,0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINMaterialScale ONMaterialItemMaster.MaterialItemMasterID =MaterialScale.MaterialItemMasterID LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINDimension ON FloorPlanLineItem.DimensionID =Dimension.DimensionIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialScale.AreaTypeID = @AreaTypeID) AND(MaterialScale.EffectiveDate = @MaterialEffectiveDate) OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialScale.AreaTypeID = @AreaTypeID) AND(MaterialScale.EffectiveDate = @MaterialEffectiveDate)GROUP BY MaterialItemMaster.MaterialItemMasterDescription,MixBuilding.MixBuildingDescription, MixHeader.JobMasterID,MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantity,MaterialScale.PriceHAVING (MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 1 AS StepID, MixHeader.JobMasterID,MixHeader.MixHeaderID, 1 AS BidSubtypeID, 0 AS WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area /1000) AS ItemQuantity,MaterialScale.Price AS ScaleValue,SUM(FloorPlanLineItem.Quantity * Dimension.Area / 1000 *MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible,0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINMaterialScale ONMaterialItemMaster.MaterialItemMasterID =MaterialScale.MaterialItemMasterID LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINDimension ON FloorPlanLineItem.DimensionID =Dimension.DimensionIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialScale.AreaTypeID = @AreaTypeID) AND(MaterialScale.EffectiveDate = @MaterialEffectivedate)GROUP BY MaterialItemMaster.MaterialItemMasterDescription,FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,'')+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +'~' + ISNULL(FloorPlanHeader.Attribute3, ''), MixHeader.MixHeaderID,MixHeader.JobMasterID,MixLineItem.FloorPlanQuantity,MaterialScale.PriceHAVING (MixHeader.MixHeaderID = @MixHeaderID)/*STEP -2STOCKINGScale * Total Wallboard sq ft*/INSERT INTO @table_variableSELECT -2, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,UnitName, UnitQuantity, N'STOCKING', SUM(ItemQuantity),dbo.BidContract_GetMaterialScaleValue(830, @AreaTypeID,@MaterialEffectiveDate), SUM(ItemQuantity) *dbo.BidContract_GetMaterialScaleValue(830, @AreaTypeID,@MaterialEffectiveDate), IsVisible, WSQtyAdjFROM @table_variableWHERE StepID = - 1GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,UnitQuantity, IsVisible, WSQtyAdj/*/////////////////////////////////////////////////////////////////////////////////////////////MISC MATERIALSUBTYPE 2/////////////////////////////////////////////////////////////////////////////////////////////*//*STEP -3NAILS AND SCREWSScale * Total Wallboard sq ft1 box covers 4000 sq ft of wallboardThis makes sure we are dealing with whole boxes of nails:ROUND(((TotalSquareFoot)/4000 + .4999), 0)SELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(ItemQuantity)/4) + .4999,0), dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),ROUND((SUM(ItemQuantity)/4) + .4999, 0) *dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),IsVisible, WSQtyAdjFROM bidunitWHERE StepID = - 1GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,UnitQuantity, IsVisible, WSQtyAdj*/INSERT INTO @table_variableSELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(ItemQuantity)/4), 0),dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),ROUND((SUM(ItemQuantity)/4), 0) *dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),IsVisible, WSQtyAdjFROM @table_variableWHERE StepID = - 1GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,UnitQuantity, IsVisible, WSQtyAdj/*MUDStep -4Select just a subset of the already inserteddata to give us a distinct list of UNITS to pass to the MUD functionNOTE: this type of select will be used a couple of more times, alwaysuse StepID = -2 (STOCKING)@THE_VALUE = isnull(@WALLBOARD/250,0) + isnull(@FIRETAPING/500,0) +isnull(@METAL/125,0) + isnull(@CEIL_SQ_FT/900,0)*/INSERT INTO @table_variableSELECT - 4, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,UnitQuantity, N'MUD', dbo.BidContract_GetMudValue(@MixHeaderID,UnitName), dbo.BidContract_GetMaterialScaleValue(828, @AreaTypeID,@MaterialEffectiveDate), dbo.BidContract_GetMudValue(@MixHeaderID,UnitName) * dbo.BidContract_GetMaterialScaleValue(828, @AreaTypeID,@MaterialEffectiveDate), IsVisible, WSQtyAdjFROM @table_variableWHERE StepID = - 2/*TAPEStep -5ROUND(SUM(ItemQuantity)/1100, 0)*/INSERT INTO @table_variableSELECT - 5, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,UnitQuantity, N'TAPE', dbo.BidContract_GetTapeValue(@MixHeaderID,UnitName), dbo.BidContract_GetMaterialScaleValue(832, @AreaTypeID,@MaterialEffectiveDate), dbo.BidContract_GetTapeValue(@MixHeaderID,UnitName) * dbo.BidContract_GetMaterialScaleValue(832, @AreaTypeID,@MaterialEffectiveDate), IsVisible, WSQtyAdjFROM @table_variableWHERE StepID = - 2/*METALStep -6SUM(Z395*1.1)/1000*/INSERT INTO @table_variableSELECT - 6 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtype, 0 AS WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity), 0) * 1.1) / 1000AS ItemQuantity, MaterialScale.Price ASScaleValue, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity), 0) * 1.1) / 1000* MaterialScale.Price AS ExtendedPrice, 0 ASIsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINMaterialScale ONMaterialItemMaster.MaterialItemMasterID =MaterialScale.MaterialItemMasterID LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialCategoryID = 2) AND(MaterialScale.AreaTypeID = @AreaTypeID) AND(MaterialScale.EffectiveDate = @MaterialEffectiveDate) OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 2) AND(MaterialScale.AreaTypeID = @AreaTypeID) AND(MaterialScale.EffectiveDate = @MaterialEffectiveDate)GROUP BY MixBuilding.MixBuildingDescription,FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantity,MaterialItemMaster.MaterialItemMasterDescription, MaterialScale.PriceHAVING (MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 6 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, (FloorPlanLineItem.Quantity * 1.1) / 1000 ASItemQuantity, MaterialScale.Price AS ScaleValue,(FloorPlanLineItem.Quantity * 1.1) / 1000 *MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINMaterialScale ONMaterialItemMaster.MaterialItemMasterID =MaterialScale.MaterialItemMasterID LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 2) AND(MaterialScale.AreaTypeID = @AreaTypeID) AND(MaterialScale.EffectiveDate = @MaterialEffectiveDate)GROUP BY FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,'')+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +'~' + ISNULL(FloorPlanHeader.Attribute3, ''),MaterialItemMaster.MaterialItemMasterDescription,MaterialScale.Price, FloorPlanLineItem.Quantity,MixLineItem.FloorPlanQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID)/*MISC MATERIALStep -7*/INSERT INTO @table_variableSELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtype,FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, ISNULL(SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity), 0)AS ItemQuantity, MaterialScale.Price ASScaleValue, ISNULL(SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity), 0)* MaterialScale.Price AS ExtendedPrice, 0 ASIsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINMaterialScale ONMaterialItemMaster.MaterialItemMasterID =MaterialScale.MaterialItemMasterID LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID ISNOT NULL) AND(NOT (MaterialItemMaster.MaterialCategoryID IN(1, 2))) OR(MixLineItem.MixHeaderID = @MixHeaderID) AND(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID ISNULL) AND(NOT (MaterialItemMaster.MaterialCategoryID IN(1, 2)))GROUP BY MaterialItemMaster.MaterialItemMasterDescription,MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantity,MaterialScale.Price, FloorPlanLineItem.WorkTypeIDHAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidMiscMaterialExemptionListDescriptionFROMclsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID<> 3)UNION ALLSELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,'') + '~' + ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, FloorPlanLineItem.Quantity AS ItemQuantity,MaterialScale.Price AS ScaleValue,SUM(FloorPlanLineItem.Quantity *MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINMaterialScale ONMaterialItemMaster.MaterialItemMasterID =MaterialScale.MaterialItemMasterID LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND(MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelID ISNULL) AND(NOT (MaterialItemMaster.MaterialCategoryID IN(1, 2)))GROUP BY MaterialItemMaster.MaterialItemMasterDescription,FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,'')+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +'~' + ISNULL(FloorPlanHeader.Attribute3, ''),FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MaterialScale.Price,FloorPlanLineItem.WorkTypeID, MixLineItem.FloorPlanQuantity,FloorPlanLineItem.QuantityHAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidMiscMaterialExemptionListDescriptionFROMclsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID<> 3)/*COUNT OF LIVING UNITSStep -8****HOUSE***SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,N'SUNDRIES' AS ItemDescription,SUM(MixLineItem.FloorPlanQuantity) ASItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,@MaterialEffectiveDate)AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity* dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,@MaterialEffectiveDate))AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanHeader INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND (FloorPlanHeader.IsLivingUnit ='1')GROUP BY FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID)****HOUSE****/INSERT INTO @table_variableSELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity, N'SUNDRIES' ASItemDescription,SUM(MixLineItem.FloorPlanQuantity) ASItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,@MaterialEffectiveDate)AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity* dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,@MaterialEffectiveDate))AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanHeader INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND (FloorPlanHeader.IsLivingUnit= '1') OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL)GROUP BY FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixBuilding.MixBuildingDescription, MixBuilding.MixBuildingQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,N'SUNDRIES' AS ItemDescription,1 AS ItemQuantity,dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,@MaterialEffectiveDate)AS ScaleValue, SUM(1 *dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,@MaterialEffectiveDate))AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanHeader INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND (FloorPlanHeader.IsLivingUnit ='1')GROUP BY FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID)/*FIRE HAULT - CHECK FOR FIREPROOFINGStep -9*/IF EXISTS (SELECT 'true' AS Expr1FROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ONFloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderIDINNER JOINJobMaster ON FloorPlanHeader.JobMasterID =JobMaster.JobMasterIDWHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND(MaterialItemMaster.MaterialItemMasterDescription = N'FIRETAPING') AND(JobMaster.ProjectTypeID <> 1))BEGININSERT INTO @table_variableSELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidsubtypeID, 0 AS WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity, N'FIRE HAULT' ASItemDescription,ISNULL(SUM(MixBuilding.MixBuildingQuantity *MixLineItem.FloorPlanQuantity) / 2, 0) AS ItemQuantity,dbo.BidContract_GetMaterialScaleValue(579,@AreaTypeID, @MaterialEffectiveDate) AS ScaleValue,ISNULL(SUM(MixBuilding.MixBuildingQuantity *MixLineItem.FloorPlanQuantity) / 2, 0) *dbo.BidContract_GetMaterialScaleValue(579,@AreaTypeID, @MaterialEffectiveDate) ASExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanHeader INNER JOINMixLineItem ONFloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderIDINNER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND (FloorPlanHeader.IsLivingUnit= '1') OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL)GROUP BY MixBuilding.MixBuildingDescription,FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity, N'FIREHAULT' AS ItemDescription,ISNULL(SUM(MixLineItem.FloorPlanQuantity) / 2,0) AS ItemQuantity, dbo.BidContract_GetMaterialScaleValue(579,@AreaTypeID,@MaterialEffectiveDate) AS ScaleValue,ISNULL(SUM(MixLineItem.FloorPlanQuantity) / 2, 0) *dbo.BidContract_GetMaterialScaleValue(579,@AreaTypeID, @MaterialEffectiveDate) ASExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID =MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderIDWHERE (MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelIDIS NULL) AND (FloorPlanHeader.IsLivingUnit = '1')GROUP BY FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID)END/*//////////////////////////////////////////////////////////////////////////////////////////////////NAILING LABORSUBTYPE 3//////////////////////////////////////////////////////////////////////////////////////////////////*//*PRODUCTION WALLBOARDStep -10.x*/IF (@NailingParam = -1) --ProgressiveBEGININSERT INTO @table_variableSELECT - 10.1 AS StepID, NailingLabor.JobMasterID,NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,NailingLabor.UnitName,NailingLabor.UnitQuantity, RTRIM(NailingLabor.ItemDescription) + N' '+ dbo.Height.HeightDescription AS ItemDescription,NailingLabor.ItemQuantity, Scale.Price +dbo.Height.NailingRate + NailingLabor.ProductionNailingLaborIncreaseAS ScaleValue,NailingLabor.ItemQuantity * (Scale.Price +dbo.Height.NailingRate + NailingLabor.ProductionNailingLaborIncrease)AS ExtendedPrice, 0 AS IsVisible,0 AS WSQtyAdjFROM dbo.BidContract_NailingLabor_Wallboard_Production( @MixHeaderID)NailingLabor INNER JOINdbo.Height ON NailingLabor.HeightID = dbo.Height.HeightID INNER JOINdbo.BidContract_NailingLabor_Scale() Scale ONNailingLabor.ItemDescription = Scale.ItemDescriptionWHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =@LaborEffectiveDate)ORDER BY NailingLabor.WorkTypeID DESC,RTRIM(NailingLabor.ItemDescription) + N' ' +dbo.Height.HeightDescriptionINSERT INTO @table_variableSELECT - 10.1 AS StepID, NailingLabor.JobMasterID,NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,NailingLabor.UnitName,NailingLabor.UnitQuantity, RTRIM(NailingLabor.ItemDescription) + N' '+ dbo.Height.HeightDescription AS ItemDescription,NailingLabor.ItemQuantity, Scale.Price +dbo.Height.NailingRateGarage +NailingLabor.ProductionNailingLaborIncrease AS ScaleValue,NailingLabor.ItemQuantity * (Scale.Price +dbo.Height.NailingRateGarage +NailingLabor.ProductionNailingLaborIncrease) AS ExtendedPrice, 0 ASIsVisible,0 AS WSQtyAdjFROM dbo.BidContract_NailingLabor_Wallboard_Production_ Garage(@MixHeaderID)NailingLabor INNER JOINdbo.Height ON NailingLabor.HeightID =dbo.Height.HeightID INNER JOINdbo.BidContract_NailingLabor_Scale() Scale ONNailingLabor.ItemDescription = Scale.ItemDescriptionWHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =@LaborEffectiveDate)ORDER BY NailingLabor.WorkTypeID DESC,RTRIM(NailingLabor.ItemDescription) + N' ' +dbo.Height.HeightDescriptionENDIF (@NailingParam = -2) --NonprogressiveBEGININSERT INTO @table_variableSELECT - 10.2 AS StepID, NailingLabor.JobMasterID,NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,NailingLabor.UnitName,NailingLabor.UnitQuantity,RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescriptionAS ItemDescription, NailingLabor.ItemQuantity,Scale.Price +NailingLabor.ProductionNailingLaborIncrease AS ScaleValue,NailingLabor.ItemQuantity * (Scale.Price +NailingLabor.ProductionNailingLaborIncrease) AS ExtendedPrice, 0 ASIsVisible, 0 AS WSQtyAdjFROM BidContract_NailingLabor_Wallboard_Production(@Mix HeaderID)NailingLabor INNER JOINHeight ON NailingLabor.HeightID =Height.HeightID INNER JOINBidContract_NailingLabor_Scale() Scale ONNailingLabor.ItemDescription = Scale.ItemDescriptionWHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =@LaborEffectiveDate)ORDER BY NailingLabor.WorkTypeID DESC,RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription--TODO: add garageENDIF (@NailingParam = -3) --StraightBEGININSERT INTO @table_variableSELECT - 10.3 AS StepID, NailingLabor.JobMasterID,NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,NailingLabor.UnitName,NailingLabor.UnitQuantity,RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescriptionAS ItemDescription, NailingLabor.ItemQuantity,NailingLabor.ProductionNailingLaborStraight ASScaleValue,NailingLabor.ItemQuantity *NailingLabor.ProductionNailingLaborStraight AS ExtendedPrice, 0 ASIsVisible, 0 AS WSQtyAdjFROM BidContract_NailingLabor_Wallboard_Production(@Mix HeaderID)NailingLabor INNER JOINHeight ON NailingLabor.HeightID =Height.HeightIDORDER BY NailingLabor.WorkTypeID DESC,RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription--TODO: add garageEND/*'ALL OTHER MATERIALStep -11*/INSERT INTO @table_variableSELECT - 11 AS StepID, MixHeader.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescription AS ItemDescription,SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)AS ItemQuantity,BidContract_NailingLabor_Scale.Price AS ScaleValue,SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity * BidContract_NailingLabor_Scale.Price) ASExtendedPrice, 0 AS IsVisible,0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINBidContract_NailingLabor_Scale()BidContract_NailingLabor_Scale ONMaterialItemMaster.MaterialItemMasterDescription= BidContract_NailingLabor_Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINDimension ON FloorPlanLineItem.DimensionID =Dimension.DimensionIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialCategoryID <> 1) AND(MaterialItemMaster.MaterialItemMasterID <> 606)AND (MaterialItemMaster.Attribute2 = N'1') AND(BidContract_NailingLabor_Scale.EffectiveDate =@LaborEffectiveDate) AND (BidContract_NailingLabor_Scale.AreaTypeID =@AreaTypeID) OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID <> 1) AND(MaterialItemMaster.MaterialItemMasterID <> 606)AND (MaterialItemMaster.Attribute2 = N'1') AND(BidContract_NailingLabor_Scale.EffectiveDate =@LaborEffectiveDate) AND (BidContract_NailingLabor_Scale.AreaTypeID =@AreaTypeID)GROUP BY MaterialItemMaster.MaterialItemMasterDescription,FloorPlanLineItem.WorkTypeID, MixBuilding.MixBuildingDescription,MixHeader.JobMasterID,MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantity, BidContract_NailingLabor_Scale.PriceHAVING (FloorPlanLineItem.WorkTypeID = 2) AND(MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 11 AS StepID, MixHeader.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, SUM(MixLineItem.FloorPlanQuantity) AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,Scale.Price AS ScaleValue,SUM(FloorPlanLineItem.Quantity * Scale.Price) ASExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINBidContract_NailingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINDimension ON FloorPlanLineItem.DimensionID =Dimension.DimensionIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID <> 1) AND(MaterialItemMaster.MaterialItemMasterID <> 606)AND (MaterialItemMaster.Attribute2 = N'1') AND (Scale.EffectiveDate =@LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)GROUP BY MaterialItemMaster.MaterialItemMasterDescription,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),MixHeader.JobMasterID, MixLineItem.MixHeaderID, Scale.PriceHAVING (FloorPlanLineItem.WorkTypeID = 2) AND(MixLineItem.MixHeaderID = @MixHeaderID)/*'PRELIM WALLBOARD LESS SPECIALStep -12*/INSERT INTO @table_variableSELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,VT.ItemQuantity,Scale.Price, VT.UnitQuantity * Scale.Price AS ExtendedPrice,VT.IsVisible, VT.WSQtyAdjFROM (SELECT - 12 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescription + N' ' +WorkLocation.WorkLocationDescription AS ItemDescription,SUM(MixLineItem.FloorPlanQuantity * (FloorPlanLineItem.Quantity *Dimension.Area)) AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ONFloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderIDINNER JOINMixHeader ONMixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOINDimension ONFloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOINJobMaster ONFloorPlanHeader.JobMasterID = JobMaster.JobMasterID ANDMixHeader.JobMasterID =JobMaster.JobMasterID LEFT OUTER JOINMixBuilding ONMixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOINWorkLocation ONFloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationIDWHERE (MixLineItem.MixBuildingID IS NOTNULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialCategoryID = 1)AND(MaterialItemMaster.Attribute2 = N'1') AND (NOT(MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidNailingLaborExemptionListDescriptionFROMclsBidNailingLaborExemptionList))) OR(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID ISNULL) AND (MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialItemMaster.Attribute2 = N'1') AND (NOT(MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidNailingLaborExemptionListDescriptionFROMclsBidNailingLaborExemptionList)))GROUP BYMaterialItemMaster.MaterialItemMasterDescription + N' ' +WorkLocation.WorkLocationDescription, FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription, JobMaster.JobMasterID,MixLineItem.MixHeaderID, MixBuilding.MixBuildingQuantityHAVING (FloorPlanLineItem.WorkTypeID = 1)AND (MixLineItem.MixHeaderID = @MixHeaderID)) VT INNER JOINBidContract_NailingLabor_Scale() Scale ONVT.ItemDescription = Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)UNION ALLSELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,VT.ItemQuantity,Scale.Price, VT.UnitQuantity * Scale.Price ASExtendedPrice, VT.IsVisible, VT.WSQtyAdjFROM (SELECT - 12 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName+ '~' + ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' +ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescription + N' ' +WorkLocation.WorkLocationDescription AS ItemDescription,SUM(FloorPlanLineItem.Quantity * Dimension.Area) AS ItemQuantity, 0 ASIsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ONFloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderIDINNER JOINMixHeader ONMixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOINDimension ONFloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOINJobMaster ONFloorPlanHeader.JobMasterID = JobMaster.JobMasterID ANDMixHeader.JobMasterID =JobMaster.JobMasterID LEFT OUTER JOINMixBuilding ONMixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOINWorkLocation ONFloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationIDWHERE (MixLineItem.MixBuildingID IS NULL)AND (MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialItemMaster.Attribute2 = N'1') AND (NOT(MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidNailingLaborExemptionListDescriptionFROMclsBidNailingLaborExemptionList)))GROUP BYMaterialItemMaster.MaterialItemMasterDescription + N' ' +WorkLocation.WorkLocationDescription, FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName+ '~' + ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3,''), JobMaster.JobMasterID, MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantityHAVING (FloorPlanLineItem.WorkTypeID = 1)AND (MixLineItem.MixHeaderID = @MixHeaderID)) VT INNER JOINBidContract_NailingLabor_Scale() Scale ONVT.ItemDescription = Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)/*PRELIM SPECIAL BOARDStep -13*/INSERT INTO @table_variableSELECT - 13 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription,SUM(MixLineItem.FloorPlanQuantity *(FloorPlanLineItem.Quantity * Dimension.Area)) AS ItemQuantity,Scale.Price AS ScaleValue,SUM(MixLineItem.FloorPlanQuantity * (FloorPlanLineItem.Quantity *Dimension.Area) * Scale.Price) AS ExtendedPrice, 0 AS IsVisible,0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ON FloorPlanLineItem.MaterialItemMasterID= MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINDimension ON FloorPlanLineItem.DimensionID =Dimension.DimensionID INNER JOINJobMaster ON FloorPlanHeader.JobMasterID =JobMaster.JobMasterID AND MixHeader.JobMasterID =JobMaster.JobMasterID INNER JOINBidContract_NailingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINWorkLocation ON FloorPlanLineItem.WorkLocationID= WorkLocation.WorkLocationIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialItemMaster.Attribute2 = N'1') AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID) OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialItemMaster.Attribute2 = N'1') AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID)GROUP BY FloorPlanLineItem.WorkTypeID,MaterialItemMaster.MaterialItemMasterDescription,MixBuilding.MixBuildingDescription, JobMaster.JobMasterID,MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantity, Scale.PriceHAVING (FloorPlanLineItem.WorkTypeID = 1) AND(MixLineItem.MixHeaderID = @MixHeaderID) AND(MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidNailingLaborExemptionListDescriptionFROMclsBidNailingLaborExemptionList))UNION ALLSELECT - 13 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area)AS ItemQuantity,Scale.Price AS ScaleValue,SUM(FloorPlanLineItem.Quantity * Dimension.Area * Scale.Price) ASExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINDimension ON FloorPlanLineItem.DimensionID =Dimension.DimensionID INNER JOINJobMaster ON FloorPlanHeader.JobMasterID = JobMaster.JobMasterID ANDMixHeader.JobMasterID = JobMaster.JobMasterID INNER JOINBidContract_NailingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINWorkLocation ON FloorPlanLineItem.WorkLocationID =WorkLocation.WorkLocationIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID = 1) AND(MaterialItemMaster.Attribute2 = N'1') AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID)GROUP BY FloorPlanLineItem.WorkTypeID,MaterialItemMaster.MaterialItemMasterDescription,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),JobMaster.JobMasterID, MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantity, Scale.PriceHAVING (FloorPlanLineItem.WorkTypeID = 1) AND(MixLineItem.MixHeaderID = @MixHeaderID) AND(MaterialItemMaster.MaterialItemMasterDescription IN(SELECTclsBidNailingLaborExemptionListDescriptionFROMclsBidNailingLaborExemptionList))/*'ALL OTHER PRELIM MATERIALStep -14*/INSERT INTO @table_variableSELECT - 14 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity)AS ItemQuantity, Scale.Price AS ScaleValue,SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity *Scale.Price) AS ExtendedPrice,0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINJobMaster ON FloorPlanHeader.JobMasterID =JobMaster.JobMasterID AND MixHeader.JobMasterID =JobMaster.JobMasterID INNER JOINBidContract_NailingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINWorkLocation ON FloorPlanLineItem.WorkLocationID= WorkLocation.WorkLocationIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialCategoryID <> 1) AND(MaterialItemMaster.Attribute2 = N'1') AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID) OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID <> 1) AND(MaterialItemMaster.Attribute2 = N'1') AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID)GROUP BY MaterialItemMaster.MaterialItemMasterDescription,FloorPlanLineItem.WorkTypeID, MixBuilding.MixBuildingDescription,JobMaster.JobMasterID,MixLineItem.MixHeaderID, MixBuilding.MixBuildingQuantity, Scale.PriceHAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineItem.WorkTypeID = 1) AND(MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 14 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 3 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,Scale.Price AS ScaleValue,SUM(FloorPlanLineItem.Quantity * Scale.Price) ASExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINJobMaster ON FloorPlanHeader.JobMasterID =JobMaster.JobMasterID AND MixHeader.JobMasterID =JobMaster.JobMasterID INNER JOINBidContract_NailingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingID LEFT OUTER JOINWorkLocation ON FloorPlanLineItem.WorkLocationID= WorkLocation.WorkLocationIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialCategoryID <> 1) AND(MaterialItemMaster.Attribute2 = N'1') AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID)GROUP BY MaterialItemMaster.MaterialItemMasterDescription,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),JobMaster.JobMasterID, MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantity, Scale.PriceHAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineItem.WorkTypeID = 1) AND(MixLineItem.MixHeaderID = @MixHeaderID)/*'FOREMANStep -15INSERT INTO @table_variableSELECT StepID, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,UnitName, UnitQuantity, ItemDescription, ItemQuantity, ExtendedPrice /ItemQuantity AS ScaleValue,ExtendedPrice, IsVisible, WSQtyAdjFROM (SELECT- 15 AS StepID,JobMasterID,MixHeaderID,BidSubtypeID,0 AS WorkTypeID,UnitName,UnitQuantity,N'FOREMAN' AS ItemDescription,SUM(ItemQuantity) AS ItemQuantity,(SELECTSUM(T2.ExtendedPrice)FROM @table_variable T2WHERE T2.UnitName = T1.UnitName AND T2.BidSubtypeID = 3) * .08AS ExtendedPrice,IsVisible,WSQtyAdjFROM @table_variable T1WHERE (StepID IN (- 10.1, - 10.2, - 10.3, - 12, -13))GROUP BY UnitName, JobMasterID, MixHeaderID,BidSubtypeID, UnitQuantity, WSQtyAdj, IsVisible)VT*//*/////////////////////////////////////////////////////////////////////////////////////////////////////////TAPING LABORSUBTYPE 4/////////////////////////////////////////////////////////////////////////////////////////////////////////*//*WALLLBOARD W/ HEIGHTStep - 16.x*/IF (@TapingParam = -1) --ProgressiveBEGININSERT INTO @table_variableSELECT - 16.1 AS StepID, TapingLabor.JobMasterID,TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,TapingLabor.WorkTypeID, TapingLabor.UnitName,TapingLabor.UnitQuantity,CASE TapingLabor.ItemDescription WHEN'WALLBOARD' THEN Height.HeightDescription ELSETapingLabor.ItemDescription + ' ' + Height.HeightDescriptionEND AS ItemDescription,TapingLabor.ItemQuantity, Scale.Price +TapingLabor.TapingLaborIncrease + Height.TapingRate AS ScaleValue,TapingLabor.ItemQuantity * (Scale.Price +TapingLabor.TapingLaborIncrease + Height.TapingRate) AS ExtendedPrice,TapingLabor.IsVisible,TapingLabor.WSQtyAdjFROM BidContract_TapingLabor_Wallboard(@MixHeaderID)TapingLabor INNER JOINHeight ON TapingLabor.HeightID = Height.HeightIDINNER JOINBidContract_TapingLabor_Scale() Scale ONTapingLabor.ItemDescription = Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)ENDIF (@TapingParam = -2) --NonprogressiveBEGININSERT INTO @table_variableSELECT - 16.2 AS StepID, TapingLabor.JobMasterID,TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,TapingLabor.WorkTypeID, TapingLabor.UnitName,TapingLabor.UnitQuantity,CASE TapingLabor.ItemDescription WHEN'WALLBOARD' THEN Height.HeightDescription ELSETapingLabor.ItemDescription + ' ' + Height.HeightDescriptionEND AS ItemDescription,TapingLabor.ItemQuantity, Scale.Price +TapingLabor.TapingLaborIncrease AS ScaleValue,TapingLabor.ItemQuantity * (Scale.Price +TapingLabor.TapingLaborIncrease) AS ExtendedPrice,TapingLabor.IsVisible, TapingLabor.WSQtyAdjFROM BidContract_TapingLabor_Wallboard(@MixHeaderID)TapingLabor INNER JOINHeight ON TapingLabor.HeightID = Height.HeightIDINNER JOINBidContract_TapingLabor_Scale() Scale ONTapingLabor.ItemDescription = Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)ENDIF (@TapingParam = -3) --StraightBEGININSERT INTO @table_variableSELECT - 16.3 AS StepID, TapingLabor.JobMasterID,TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,TapingLabor.WorkTypeID, TapingLabor.UnitName,TapingLabor.UnitQuantity,CASE TapingLabor.ItemDescription WHEN'WALLBOARD' THEN Height.HeightDescription ELSETapingLabor.ItemDescription + ' ' + Height.HeightDescriptionEND AS ItemDescription,TapingLabor.ItemQuantity, TapingLabor.TapingLaborStraight ASScaleValue,TapingLabor.ItemQuantity *TapingLabor.TapingLaborStraight AS ExtendedPrice,TapingLabor.IsVisible, TapingLabor.WSQtyAdjFROM BidContract_TapingLabor_Wallboard(@MixHeaderID) TapingLaborINNER JOINHeight ON TapingLabor.HeightID = Height.HeightIDINNER JOINBidContract_TapingLabor_Scale() Scale ON TapingLabor.ItemDescription= Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)END/*METAL AND MISC ITEMSStepID -17*/INSERT INTO @table_variableSELECT - 17 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 4 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *FloorPlanLineItem.Quantity)AS ItemQuantity, Scale.Price AS ScaleValue,SUM((MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)* (Scale.Price + JobMaster.TapingLaborIncrease))AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID =MixHeader.MixHeaderID INNER JOINJobMaster ON FloorPlanHeader.JobMasterID =JobMaster.JobMasterID INNER JOINBidContract_TapingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID =MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialItemMasterID IN (605, 795,589, 584, 586, 583, 585, 587, 582, 588)) AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID) OR(MixLineItem.MixBuildingID IS NOT NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialItemMasterID IN (605, 795, 589,584, 586, 583, 585, 587, 582, 588)) AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID)GROUP BY JobMaster.JobMasterID,MaterialItemMaster.MaterialItemMasterDescription,MixBuilding.MixBuildingDescription, MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantity, Scale.Price,FloorPlanLineItem.WorkTypeIDHAVING (MixLineItem.MixHeaderID = @MixHeaderID)UNION ALLSELECT - 17 AS StepID, JobMaster.JobMasterID,MixLineItem.MixHeaderID, 4 AS BidSubtypeID,FloorPlanLineItem.WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescriptionAS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,Scale.Price AS ScaleValue,SUM(FloorPlanLineItem.Quantity * (Scale.Price +JobMaster.TapingLaborIncrease)) AS ExtendedPrice, 0 AS IsVisible, 0 ASWSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID= MixLineItem.FloorPlanHeaderID INNER JOINMixHeader ON MixLineItem.MixHeaderID = MixHeader.MixHeaderIDINNER JOINJobMaster ON FloorPlanHeader.JobMasterID =JobMaster.JobMasterID INNER JOINBidContract_TapingLabor_Scale() Scale ONMaterialItemMaster.MaterialItemMasterDescription =Scale.ItemDescription LEFT OUTER JOINMixBuilding ON MixLineItem.MixBuildingID = MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL) AND(MaterialItemMaster.MaterialItemMasterID IN (605, 795, 589, 584,586, 583, 585, 587, 582, 588)) AND(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =@AreaTypeID)GROUP BY JobMaster.JobMasterID,MaterialItemMaster.MaterialItemMasterDescription,FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,'') + '~' + ISNULL(FloorPlanHeader.Attribute2,'') + '~' + ISNULL(FloorPlanHeader.Attribute3, ''),MixLineItem.MixHeaderID,MixLineItem.FloorPlanQuantity, Scale.Price,FloorPlanLineItem.WorkTypeIDHAVING (MixLineItem.MixHeaderID = @MixHeaderID)/*BRACKETSStepID -18*/INSERT INTO @table_variableSELECT Brackets.StepID, Brackets.JobMasterID,Brackets.MixHeaderID, Brackets.BidSubtypeID, Brackets.WorkTypeID,Brackets.UnitName,Brackets.UnitQuantity, Brackets.ItemDescription,Brackets.ItemQuantity, Scale.Price AS Price, Brackets.ItemQuantity *Scale.Price AS ExtendedPrice,Brackets.IsVisible, Brackets.WSQtyAdjFROM (SELECT - 18 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity, N'BRACKETS' ASItemDescription,SUM(MixLineItem.FloorPlanQuantity) AS ItemQuantity, 0 AS IsVisible, 0AS WSQtyAdjFROM MixLineItem INNER JOINFloorPlanHeader ONMixLineItem.FloorPlanHeaderID = FloorPlanHeader.FloorPlanHeaderID LEFTOUTER JOINMixBuilding ONMixLineItem.MixBuildingID = MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NOTNULL) AND (MixLineItem.MixLevelID IS NOT NULL) OR(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID ISNULL)GROUP BY MixBuilding.MixBuildingDescription,FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantityHAVING (MixLineItem.MixHeaderID =@MixHeaderID)) Brackets INNER JOINBidContract_TapingLabor_Scale() Scale ONBrackets.ItemDescription = Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)UNION ALLSELECT Brackets.StepID, Brackets.JobMasterID,Brackets.MixHeaderID, Brackets.BidSubtypeID, Brackets.WorkTypeID,Brackets.UnitName,Brackets.UnitQuantity, Brackets.ItemDescription,Brackets.ItemQuantity, Scale.Price AS Price, Brackets.ItemQuantity *Scale.Price AS ExtendedPrice,Brackets.IsVisible, Brackets.WSQtyAdjFROM (SELECT - 18 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' +ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,MixLineItem.FloorPlanQuantity AS UnitQuantity, N'BRACKETS' ASItemDescription,SUM(MixLineItem.FloorPlanQuantity)AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdjFROM MixLineItem INNER JOINFloorPlanHeader ONMixLineItem.FloorPlanHeaderID = FloorPlanHeader.FloorPlanHeaderID LEFTOUTER JOINMixBuilding ONMixLineItem.MixBuildingID = MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NULL) AND(MixLineItem.MixLevelID IS NULL)GROUP BY FloorPlanHeader.PlanName + '~' +ISNULL(FloorPlanHeader.Attribute1, '') + '~' +ISNULL(FloorPlanHeader.Attribute2, '')+ '~' +ISNULL(FloorPlanHeader.Attribute3, ''), FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, MixLineItem.FloorPlanQuantityHAVING (MixLineItem.MixHeaderID = @MixHeaderID))Brackets INNER JOINBidContract_TapingLabor_Scale() Scale ONBrackets.ItemDescription = Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)/*FOREMANStepID -19*/INSERT INTO @table_variableSELECT StepID, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,UnitName, UnitQuantity, ItemDescription, ItemQuantity, ExtendedPrice /ItemQuantity AS ScaleValue,ExtendedPrice, IsVisible, WSQtyAdjFROM (SELECT - 19 AS StepID, JobMasterID, MixHeaderID, BidSubtypeID,0 AS WorkTypeID, UnitName, UnitQuantity, N'FOREMAN' ASItemDescription,SUM(ItemQuantity) AS ItemQuantity,(SELECTSUM(T2.ExtendedPrice)FROM @table_variableT2WHERE T2.UnitName =T1.UnitName AND T2.BidSubtypeID = 4) * .06 AS ExtendedPrice,IsVisible, WSQtyAdjFROM @table_variable T1WHERE (StepID IN (- 16.1, - 16.2, - 16.3))GROUP BY UnitName, JobMasterID, MixHeaderID,BidSubtypeID, UnitQuantity, WSQtyAdj, IsVisible)VT/*//////////////////////////////////////////////////////////////////////////////////////////////////PICKUPSUBTYPE 5//////////////////////////////////////////////////////////////////////////////////////////////////*//*CUT & SCRAPEStepID -20Same as Total Taping Labor Wallboard less the Garages*/INSERT INTO @table_variableSELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,VT.ItemQuantity,Scale.Price AS ScaleValue, VT.ItemQuantity *Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdjFROM (SELECT - 20 AS StepID, JobMasterID, MixHeaderID, 5AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'CUT &SCRAPE' AS ItemDescription,SUM(ItemQuantity) / 1000 ASItemQuantity, IsVisible, WSQtyAdjFROM @table_variable T1WHERE (NOT (ItemDescription LIKE N'%Garage%')) AND(StepID IN (- 16.1, - 16.2, - 16.3))GROUP BY JobMasterID, MixHeaderID, UnitName,UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOINPickupScale Scale ON VT.ItemDescription =Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)/*SANDING / HIGHStepID -21Wallboard and Round 10 feet and above from taping labor*/INSERT INTO @table_variableSELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,VT.ItemQuantity,Scale.Price AS ScaleValue, VT.ItemQuantity *Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdjFROM (SELECT - 21 AS StepID, JobMasterID, MixHeaderID, 5AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'SANDING'AS ItemDescription,SUM(ItemQuantity) / 1000 ASItemQuantity, IsVisible, WSQtyAdjFROM @table_variable T1WHERE (StepID IN (- 16.1, - 16.2, - 16.3)) AND (NOT(ItemDescription LIKE N'% 8%')) AND (NOT (ItemDescription LIKE N'%9%')) AND(NOT (ItemDescription LIKEN'%GARAGE%'))GROUP BY JobMasterID, MixHeaderID, UnitName,UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOINPickupScale Scale ON VT.ItemDescription =Scale.ItemDescriptionWHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND(Scale.AreaTypeID = @AreaTypeID)/*WINDOWSStepID -22*/INSERT INTO @table_variableSELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,VT.ItemQuantity,Scale.Price AS ScaleValue, VT.ItemQuantity *Scale.Price AS ExtendedPrice, VT.IsVisible, VT.WSQtyAdjFROM (SELECT - 22 AS StepID, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID, 5 AS BidSubtypeID, 0 AS WorkTypeID,MixBuilding.MixBuildingDescription AS UnitName,MixBuilding.MixBuildingQuantity AS UnitQuantity,MaterialItemMaster.MaterialItemMasterDescription AS ItemDescription,SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdjFROM FloorPlanLineItem INNER JOINMaterialItemMaster ONFloorPlanLineItem.MaterialItemMasterID =MaterialItemMaster.MaterialItemMasterID INNER JOINFloorPlanHeader ONFloorPlanLineItem.FloorPlanHeaderID =FloorPlanHeader.FloorPlanHeaderID INNER JOINMixLineItem ON FloorPlanHeader.FloorPlanHeaderID =MixLineItem.FloorPlanHeaderID LEFT OUTER JOINMixBuilding ONMixLineItem.MixBuildingID = MixBuilding.MixBuildingIDWHERE (MixLineItem.MixBuildingID IS NOTNULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND(MaterialItemMaster.MaterialItemMasterID = 802) OR(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID ISNULL) AND (MaterialItemMaster.MaterialItemMasterID = 802)GROUP BYMaterialItemMaster.MaterialItemMasterDescription,MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,MixLineItem.MixHeaderID,MixBuilding.MixBuildingQuantityHAVING (MixLineItem.MixHeaderID =@MixHeaderID)) VT INNE

View Replies !

---