build sql script: process

Showing posts with label process. Show all posts

Thursday, March 22, 2012

Bulk Insert Related Tables - PKs

Hi all,
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:

> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Tuesday, March 20, 2012

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> > Hi! We are in the process of adding few columns to our large tables (200
> > million rows) and altering few columns from varchar to char. One of the
> > option I am thinking is to bcp data out, change the schema and bulk
insert
> > data in. Looks like with either bcp.exe or bulk insert command, you
won't
> > be
> > able to load the data in, if the schema of table get changed. Is this
> > true?
> > I know that I can use DTS export/import to do this task but since bulk
> > insert is the fastest method I would like to try that option if
possible.
> > Besides, Bulk insert I could also change schema with Alter table
command.
> > I
> > don't know if thats better than unloading/change/reload method that I
> > mentioned above.
> > I would appreiciate it, if anyone who have export/change schema/import
> > large
> > table, give me some direction here.
> >
> > thanks
> >
> >
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>> If you use the Native mode I don't think you can do it but have you
> actually
>> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
>> back in. If native wont work I am pretty sure char mode will.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "james" <kush@.brandes.com> wrote in message
>> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
>> > Hi! We are in the process of adding few columns to our large tables
>> > (200
>> > million rows) and altering few columns from varchar to char. One of the
>> > option I am thinking is to bcp data out, change the schema and bulk
> insert
>> > data in. Looks like with either bcp.exe or bulk insert command, you
> won't
>> > be
>> > able to load the data in, if the schema of table get changed. Is this
>> > true?
>> > I know that I can use DTS export/import to do this task but since bulk
>> > insert is the fastest method I would like to try that option if
> possible.
>> > Besides, Bulk insert I could also change schema with Alter table
> command.
>> > I
>> > don't know if thats better than unloading/change/reload method that I
>> > mentioned above.
>> > I would appreiciate it, if anyone who have export/change schema/import
>> > large
>> > table, give me some direction here.
>> >
>> > thanks
>> >
>> >
>>
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >I have tried both native and character mode and both erroed out. Which is
> > kind of expected, since schema got changed, the program doesn't have any
> > way
> > of knowing which column in datafile (native or char) maps to which
column
> > in
> > table.
> > I haven't tried format file so far, which is what I am going to do next
> > and
> > see if I can alter format file and make this thing work.
> > Thanks for your Input.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >> If you use the Native mode I don't think you can do it but have you
> > actually
> >> tried? BCP out a few thousand rows, create anew table and Bulk Insert
it
> >> back in. If native wont work I am pretty sure char mode will.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "james" <kush@.brandes.com> wrote in message
> >> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> >> > Hi! We are in the process of adding few columns to our large tables
> >> > (200
> >> > million rows) and altering few columns from varchar to char. One of
the
> >> > option I am thinking is to bcp data out, change the schema and bulk
> > insert
> >> > data in. Looks like with either bcp.exe or bulk insert command, you
> > won't
> >> > be
> >> > able to load the data in, if the schema of table get changed. Is this
> >> > true?
> >> > I know that I can use DTS export/import to do this task but since
bulk
> >> > insert is the fastest method I would like to try that option if
> > possible.
> >> > Besides, Bulk insert I could also change schema with Alter table
> > command.
> >> > I
> >> > don't know if thats better than unloading/change/reload method that I
> >> > mentioned above.
> >> > I would appreiciate it, if anyone who have export/change
schema/import
> >> > large
> >> > table, give me some direction here.
> >> >
> >> > thanks
> >> >
> >> >
> >>
> >>
> >
> >
>

Wednesday, March 7, 2012

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
Nick
Hi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:

> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
NickHi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:

> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
NickHi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:
> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Thursday, February 16, 2012

Bulk Copy Errors

I am getting a "The process could not bulk copy into table '[dbo].[ ... "
error when attempting set up transactional replication between two SQL Server
2000 servers. The error occurs when copying the snapshot. The funny thing is
that the specific error states there is a primary key violation. However the
target table is empty and the source table does not contain any primary key
violations. Any ideas on what's causing this and how to address it?
Don't have a clue. I have never heard of this error before.
I'd try logging as per this kb article in hopes it can shed more light on
the matter.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:6073D5C2-709B-441C-A62D-EB2182A3ADCF@.microsoft.com...
>I am getting a "The process could not bulk copy into table '[dbo].[ ... "
> error when attempting set up transactional replication between two SQL
> Server
> 2000 servers. The error occurs when copying the snapshot. The funny thing
> is
> that the specific error states there is a primary key violation. However
> the
> target table is empty and the source table does not contain any primary
> key
> violations. Any ideas on what's causing this and how to address it?

Sunday, February 12, 2012

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 results
up to theat point for another insert into @.Table_varible? If you look
for stepID -15 I have commented that section out due to it not
retuning the correct values.

Thank you in advance
Stephen Patten

Table 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)
AS
BEGIN
/*
/////////////////////////////////////////////////////////////////////////////////////////////

MATERIAL
SUBTYPE 1

/////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -1
WALLBOARD
ALL MATERIAL THAT HAS A MATERIAL CATEGORY OF 1 (WALLBOARD)
NOTE: THIS WILL ALSO GIVE YOU THE TOTAL SQUARE FEET TO BE USED IN
LATER CALCULATIONS
*/
INSERT INTO @.table_variable
SELECT - 1 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 1 AS BidSubTypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS 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 WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (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.Price
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS 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 WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (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.Price
HAVING (MixHeader.MixHeaderID = @.MixHeaderID)

/*
STEP -2
STOCKING
Scale * Total Wallboard sq ft
*/
INSERT INTO @.table_variable
SELECT -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, WSQtyAdj
FROM @.table_variable
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
/*
/////////////////////////////////////////////////////////////////////////////////////////////

MISC MATERIAL
SUBTYPE 2

/////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -3
NAILS AND SCREWS
Scale * Total Wallboard sq ft
1 box covers 4000 sq ft of wallboard
This 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, WSQtyAdj
FROM bidunit
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
*/
INSERT INTO @.table_variable
SELECT - 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, WSQtyAdj
FROM @.table_variable
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
/*
MUD
Step -4
Select just a subset of the already inserted
data to give us a distinct list of UNITS to pass to the MUD function
NOTE: this type of select will be used a couple of more times, always
use 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_variable
SELECT - 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, WSQtyAdj
FROM @.table_variable
WHERE StepID = - 2
/*
TAPE
Step -5
ROUND(SUM(ItemQuantity)/1100, 0)
*/
INSERT INTO @.table_variable
SELECT - 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, WSQtyAdj
FROM @.table_variable
WHERE StepID = - 2
/*
METAL
Step -6
SUM(Z395*1.1)/1000
*/
INSERT INTO @.table_variable
SELECT - 6 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtype, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0) * 1.1) / 1000
AS ItemQuantity, MaterialScale.Price AS
ScaleValue, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0) * 1.1) / 1000
* MaterialScale.Price AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (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.Price
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS ItemDescription, (FloorPlanLineItem.Quantity * 1.1) / 1000 AS
ItemQuantity, MaterialScale.Price AS ScaleValue,
(FloorPlanLineItem.Quantity * 1.1) / 1000 *
MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (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.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
/*
MISC MATERIAL
Step -7
*/
INSERT INTO @.table_variable
SELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtype,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0)
AS ItemQuantity, MaterialScale.Price AS
ScaleValue, ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0)
* MaterialScale.Price AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixHeaderID = @.MixHeaderID) AND
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NOT NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2))) OR
(MixLineItem.MixHeaderID = @.MixHeaderID) AND
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2)))
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,
MaterialScale.Price, FloorPlanLineItem.WorkTypeID
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidMiscMaterialExemptionListDescription
FROM
clsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID
<> 3)
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS ItemDescription, FloorPlanLineItem.Quantity AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity *
MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixHeaderID = @.MixHeaderID) AND
(MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelID IS
NULL) 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.Quantity
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidMiscMaterialExemptionListDescription
FROM
clsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID
<> 3)
/*
COUNT OF LIVING UNITS
Step -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) AS
ItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @.AreaTypeID,
@.MaterialEffectiveDate)
AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity
* dbo.BidContract_GetMaterialScaleValue(843, @.AreaTypeID,
@.MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID
WHERE (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.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
****HOUSE***
*/
INSERT INTO @.table_variable
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'SUNDRIES' AS
ItemDescription,
SUM(MixLineItem.FloorPlanQuantity) AS
ItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @.AreaTypeID,
@.MaterialEffectiveDate)
AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity
* dbo.BidContract_GetMaterialScaleValue(843, @.AreaTypeID,
@.MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (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.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
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,
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 WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID
WHERE (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.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
/*
FIRE HAULT - CHECK FOR FIREPROOFING
Step -9
*/
IF EXISTS (
SELECT 'true' AS Expr1
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID
WHERE (MixLineItem.MixHeaderID = @.MixHeaderID) AND
(MaterialItemMaster.MaterialItemMasterDescription = N'FIRETAPING') AND
(JobMaster.ProjectTypeID <> 1))
BEGIN
INSERT INTO @.table_variable
SELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidsubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'FIRE HAULT' AS
ItemDescription,
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) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (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.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
SELECT - 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'FIRE
HAULT' 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) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID =
MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID
WHERE (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.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
END
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

NAILING LABOR
SUBTYPE 3

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
PRODUCTION WALLBOARD
Step -10.x
*/
IF (@.NailingParam = -1) --Progressive
BEGIN
INSERT INTO @.table_variable
SELECT - 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.ProductionNailingLaborIncrease
AS ScaleValue,
NailingLabor.ItemQuantity * (Scale.Price +
dbo.Height.NailingRate + NailingLabor.ProductionNailingLaborIncrease)
AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract_NailingLabor_Wallboard_Production( @.MixHeaderID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.HeightID = dbo.Height.HeightID INNER JOIN
dbo.BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @.AreaTypeID) AND (Scale.EffectiveDate =
@.LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' +
dbo.Height.HeightDescription

INSERT INTO @.table_variable
SELECT - 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 AS
IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract_NailingLabor_Wallboard_Production_ Garage(@.MixHeaderID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.HeightID =
dbo.Height.HeightID INNER JOIN
dbo.BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @.AreaTypeID) AND (Scale.EffectiveDate =
@.LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' +
dbo.Height.HeightDescription

END
IF (@.NailingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @.table_variable
SELECT - 10.2 AS StepID, NailingLabor.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription
AS ItemDescription, NailingLabor.ItemQuantity,
Scale.Price +
NailingLabor.ProductionNailingLaborIncrease AS ScaleValue,
NailingLabor.ItemQuantity * (Scale.Price +
NailingLabor.ProductionNailingLaborIncrease) AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM BidContract_NailingLabor_Wallboard_Production(@.Mix HeaderID)
NailingLabor INNER JOIN
Height ON NailingLabor.HeightID =
Height.HeightID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @.AreaTypeID) AND (Scale.EffectiveDate =
@.LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription

--TODO: add garage

END
IF (@.NailingParam = -3) --Straight
BEGIN
INSERT INTO @.table_variable
SELECT - 10.3 AS StepID, NailingLabor.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription
AS ItemDescription, NailingLabor.ItemQuantity,
NailingLabor.ProductionNailingLaborStraight AS
ScaleValue,
NailingLabor.ItemQuantity *
NailingLabor.ProductionNailingLaborStraight AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM BidContract_NailingLabor_Wallboard_Production(@.Mix HeaderID)
NailingLabor INNER JOIN
Height ON NailingLabor.HeightID =
Height.HeightID
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' + Height.HeightDescription

--TODO: add garage

END
/*
'ALL OTHER MATERIAL
Step -11
*/
INSERT INTO @.table_variable
SELECT - 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) AS
ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
BidContract_NailingLabor_Scale()
BidContract_NailingLabor_Scale ON
MaterialItemMaster.MaterialItemMasterDescription
= BidContract_NailingLabor_Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (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.Price
HAVING (FloorPlanLineItem.WorkTypeID = 2) AND
(MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (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.Price
HAVING (FloorPlanLineItem.WorkTypeID = 2) AND
(MixLineItem.MixHeaderID = @.MixHeaderID)
/*
'PRELIM WALLBOARD LESS SPECIAL
Step -12
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (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 WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOIN
Dimension ON
FloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOIN
JobMaster ON
FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID =
JobMaster.JobMasterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1)
AND
(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))) OR

(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND (MaterialItemMaster.MaterialCategoryID = 1) AND

MixBuilding.MixBuildingDescription, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, MixBuilding.MixBuildingQuantity
HAVING (FloorPlanLineItem.WorkTypeID = 1)
AND (MixLineItem.MixHeaderID = @.MixHeaderID)) VT INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
VT.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
UNION ALL
SELECT 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.WSQtyAdj
FROM (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 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOIN
Dimension ON
FloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOIN
JobMaster ON
FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID =
JobMaster.JobMasterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NULL)
AND (MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND

(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList)))
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription, FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName
+ '~' + ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3,
''), JobMaster.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (FloorPlanLineItem.WorkTypeID = 1)
AND (MixLineItem.MixHeaderID = @.MixHeaderID)) VT INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
VT.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
PRELIM SPECIAL BOARD
Step -13
*/
INSERT INTO @.table_variable
SELECT - 13 AS StepID, JobMaster.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 * Dimension.Area)) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity * (FloorPlanLineItem.Quantity *
Dimension.Area) * Scale.Price) AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON FloorPlanLineItem.MaterialItemMasterID
= MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID AND MixHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID
= WorkLocation.WorkLocationID
WHERE (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.Price
HAVING (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @.MixHeaderID) AND

(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area)
AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Dimension.Area * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID = JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID =
WorkLocation.WorkLocationID
WHERE (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.Price
HAVING (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @.MixHeaderID) AND

(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))
/*
'ALL OTHER PRELIM MATERIAL
Step -14
*/
INSERT INTO @.table_variable
SELECT - 14 AS StepID, JobMaster.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, Scale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity *
Scale.Price) AS ExtendedPrice,
0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID AND MixHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID
= WorkLocation.WorkLocationID
WHERE (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.Price
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID AND MixHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID
= WorkLocation.WorkLocationID
WHERE (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.Price
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(N'WINDOWS', N'ANGELS'))) AND (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @.MixHeaderID)
/*
'FOREMAN
Step -15
INSERT INTO @.table_variable
SELECT StepID, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, ItemDescription, ItemQuantity, ExtendedPrice /
ItemQuantity AS ScaleValue,
ExtendedPrice, IsVisible, WSQtyAdj
FROM (SELECT
- 15 AS StepID,
JobMasterID,
MixHeaderID,
BidSubtypeID,
0 AS WorkTypeID,
UnitName,
UnitQuantity,
N'FOREMAN' AS ItemDescription,
SUM(ItemQuantity) AS ItemQuantity,
(SELECT
SUM(T2.ExtendedPrice)
FROM @.table_variable T2
WHERE T2.UnitName = T1.UnitName AND T2.BidSubtypeID = 3) * .08
AS ExtendedPrice,
IsVisible,
WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 10.1, - 10.2, - 10.3, - 12, -
13))
GROUP BY UnitName, JobMasterID, MixHeaderID,
BidSubtypeID, UnitQuantity, WSQtyAdj, IsVisible)VT
*/

/*
/////////////////////////////////////////////////////////////////////////////////////////////////////////

TAPING LABOR
SUBTYPE 4

/////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
WALLLBOARD W/ HEIGHT
Step - 16.x
*/
IF (@.TapingParam = -1) --Progressive
BEGIN
INSERT INTO @.table_variable
SELECT - 16.1 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END 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.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@.MixHeaderID)
TapingLabor INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
TapingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
END
IF (@.TapingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @.table_variable
SELECT - 16.2 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, Scale.Price +
TapingLabor.TapingLaborIncrease AS ScaleValue,
TapingLabor.ItemQuantity * (Scale.Price +
TapingLabor.TapingLaborIncrease) AS ExtendedPrice,
TapingLabor.IsVisible, TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@.MixHeaderID)
TapingLabor INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
TapingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
END
IF (@.TapingParam = -3) --Straight
BEGIN
INSERT INTO @.table_variable
SELECT - 16.3 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, TapingLabor.TapingLaborStraight AS
ScaleValue,
TapingLabor.ItemQuantity *
TapingLabor.TapingLaborStraight AS ExtendedPrice,
TapingLabor.IsVisible, TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@.MixHeaderID) TapingLabor
INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON TapingLabor.ItemDescription
= Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
END
/*
METAL AND MISC ITEMS
StepID -17
*/
INSERT INTO @.table_variable
SELECT - 17 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS 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 WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (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.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
UNION ALL
SELECT - 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.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * (Scale.Price +
JobMaster.TapingLaborIncrease)) AS ExtendedPrice, 0 AS IsVisible, 0 AS
WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID = MixHeader.MixHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (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.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)
/*
BRACKETS
StepID -18
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (SELECT - 18 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,

MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'BRACKETS' AS
ItemDescription,

SUM(MixLineItem.FloorPlanQuantity) AS ItemQuantity, 0 AS IsVisible, 0
AS WSQtyAdj
FROM MixLineItem INNER JOIN
FloorPlanHeader ON
MixLineItem.FloorPlanHeaderID = FloorPlanHeader.FloorPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) OR

(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL)
GROUP BY MixBuilding.MixBuildingDescription,
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID =
@.MixHeaderID)) Brackets INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
Brackets.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
UNION ALL
SELECT 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.WSQtyAdj
FROM (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' AS
ItemDescription,
SUM(MixLineItem.FloorPlanQuantity)
AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM MixLineItem INNER JOIN
FloorPlanHeader ON
MixLineItem.FloorPlanHeaderID = FloorPlanHeader.FloorPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (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.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID))
Brackets INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
Brackets.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
FOREMAN
StepID -19
*/
INSERT INTO @.table_variable
SELECT StepID, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, ItemDescription, ItemQuantity, ExtendedPrice /
ItemQuantity AS ScaleValue,
ExtendedPrice, IsVisible, WSQtyAdj
FROM (SELECT - 19 AS StepID, JobMasterID, MixHeaderID, BidSubtypeID,
0 AS WorkTypeID, UnitName, UnitQuantity, N'FOREMAN' AS
ItemDescription,
SUM(ItemQuantity) AS ItemQuantity,
(SELECT
SUM(T2.ExtendedPrice)
FROM @.table_variable
T2
WHERE T2.UnitName =
T1.UnitName AND T2.BidSubtypeID = 4) * .06 AS ExtendedPrice,
IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
BidSubtypeID, UnitQuantity, WSQtyAdj, IsVisible)VT
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

PICKUP
SUBTYPE 5

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
CUT & SCRAPE
StepID -20
Same as Total Taping Labor Wallboard less the Garages
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (SELECT - 20 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'CUT &
SCRAPE' AS ItemDescription,
SUM(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (NOT (ItemDescription LIKE N'%Garage%')) AND
(StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
SANDING / HIGH
StepID -21
Wallboard and Round 10 feet and above from taping labor
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (SELECT - 21 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'SANDING'
AS ItemDescription,
SUM(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3)) AND (NOT
(ItemDescription LIKE N'% 8%')) AND (NOT (ItemDescription LIKE N'%
9%')) AND
(NOT (ItemDescription LIKE
N'%GARAGE%'))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
WINDOWS
StepID -22
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (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 WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID =
MixLineItem.FloorPlanHeaderID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND

(MaterialItemMaster.MaterialItemMasterID = 802) OR

(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND (MaterialItemMaster.MaterialItemMasterID = 802)
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID =
@.MixHeaderID)) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
UNION ALL
SELECT 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.WSQtyAdj
FROM (SELECT - 22 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 5 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' +
ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription AS
ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity, 0 AS
IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID LEFT
OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialItemMasterID = 802)
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' +
ISNULL(FloorPlanHeader.Attribute2, '') + '~' +
ISNULL(FloorPlanHeader.Attribute3, ''), FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID)) VT
INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
CAULKING
StepID -23
*/
INSERT INTO @.table_variable
SELECT Caulking.StepID, Caulking.JobMasterID,
Caulking.MixHeaderID, Caulking.BidSubtypeID, Caulking.WorkTypeID,
Caulking.UnitName,
Caulking.UnitQuantity, Caulking.ItemDescription,
Caulking.ItemQuantity, Scale.Price, Caulking.ItemQuantity *
Scale.Price AS ExtendedPrice,
Caulking.IsVisible, Caulking.WSQtyAdj
FROM (SELECT - 23 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'CAULKING'
AS ItemDescription,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @.table_variable
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription
HAVING (ItemDescription = N'SUNDRIES'))
Caulking INNER JOIN
PickupScale Scale ON Caulking.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
PRE-PAINT
StepID -24
NOTE: COPIED FROM CUT & SCRAPE
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (SELECT - 24 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'PRE-PAINT'
AS ItemDescription,
SUM(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (NOT (ItemDescription LIKE
N'%Garage%')) AND (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
FINALS
StepID -25
NOTE: COPIED FROM CUT & SCRAPE
*/
INSERT INTO @.table_variable
SELECT 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.WSQtyAdj
FROM (SELECT - 25 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'FINALS' AS
ItemDescription,
SUM(ItemQuantity) / 1000 AS ItemQuantity,
IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (NOT (ItemDescription LIKE
N'%Garage%')) AND (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY JobMasterID, MixHeaderID, UnitName, UnitQuantity,
WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
CAPITAL
StepID -26
NOTE: SAME TOTAL ITEMVALUE AS TAPING LABOR FOREMAN
BUT WITH A SCALE THAT IS PULLED FORM THE PICKUP SCALE
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 26 AS StepID, JobMasterID, MixHeaderID, 5
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'CAPITAL'
AS ItemDescription,
SUM(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
PickupScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

FRAMING
SUBTYPE 8

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -27
*/
INSERT INTO @.table_variable
SELECT - 27 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 8 AS BidSubTypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity)
AS ItemQuantity, FramingScale.Price AS
ScaleValue, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity * FramingScale.Price)
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
FramingScale ON
MaterialItemMaster.MaterialItemMasterDescription =
FramingScale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FramingScale.AreaTypeID =
@.AreaTypeID) AND
(FramingScale.EffectiveDate =
@.MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FramingScale.AreaTypeID =
@.AreaTypeID) AND
(FramingScale.EffectiveDate =
@.MaterialEffectiveDate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, FramingScale.Price,
FloorPlanLineItem.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID) AND
(FloorPlanLineItem.WorkTypeID = 3)
UNION ALL
SELECT - 27 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 8 AS BidSubTypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
FramingScale.Price AS ScaleValue,
FloorPlanLineItem.Quantity * FramingScale.Price AS ExtendedPrice,
SUM(0) AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
FramingScale ON
MaterialItemMaster.MaterialItemMasterDescription =
FramingScale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FramingScale.AreaTypeID =
@.AreaTypeID) AND
(FramingScale.EffectiveDate =
@.MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FramingScale.AreaTypeID =
@.AreaTypeID) AND
(FramingScale.EffectiveDate =
@.MaterialEffectiveDate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName, MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity,
FramingScale.Price, FloorPlanLineItem.WorkTypeID,
FloorPlanLineItem.Quantity * FramingScale.Price
HAVING (MixLineItem.MixHeaderID = @.MixHeaderID) AND
(FloorPlanLineItem.WorkTypeID = 3)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

SUBCONTRACTOR
SUBTYPE 6

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
PRELIM SCRAP
STEP -28
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 28 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.1 AS WorkTypeID, UnitName, UnitQuantity,
N'PRELIM SCRAP' AS
ItemDescription, SUM(ItemQuantity) / 1000 AS ItemQuantity, IsVisible,
WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 12, - 13))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
SCRAP
STEP -29
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 29 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.2 AS WorkTypeID, UnitName, UnitQuantity,
N'SCRAP' AS ItemDescription, SUM(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
CLEAN-UP
STEP -30
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 30 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.3 AS WorkTypeID, UnitName, UnitQuantity,
N'CLEAN-UP' AS ItemDescription,
SUM(ItemQuantity) / 1000 AS
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
WINDOWS
STEP -31
*/
INSERT INTO @.table_variable
SELECT Window.StepID, Window.JobMasterID, Window.MixHeaderID,
Window.BidSubtypeID, Window.WorkTypeID, Window.UnitName,
Window.UnitQuantity, Window.ItemDescription,
Window.ItemQuantity, Scale.Price, Window.ItemQuantity * Scale.Price AS
ExtendedPrice,
Window.IsVisible, Window.WSQtyAdj
FROM (SELECT - 31 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -1.4 AS WorkTypeID, UnitName, UnitQuantity, 'WINDOWS'
AS ItemDescription,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @.table_variable
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription
HAVING (ItemDescription = N'SUNDRIES'))
Window INNER JOIN
SubcontractorScale Scale ON
Window.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
TOOLS
STEP -32
*/
INSERT INTO @.table_variable
SELECT Tool.StepID, Tool.JobMasterID, Tool.MixHeaderID,
Tool.BidSubtypeID, Tool.WorkTypeID, Tool.UnitName,
Tool.UnitQuantity, Tool.ItemDescription,
Tool.ItemQuantity, Scale.Price, Tool.ItemQuantity * Scale.Price AS
ExtendedPrice,
Tool.IsVisible, Tool.WSQtyAdj
FROM (SELECT - 32 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, 'TOOLS' AS
ItemDescription,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @.table_variable
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription
HAVING (ItemDescription = N'SUNDRIES'))
Tool INNER JOIN
SubcontractorScale Scale ON Tool.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
SPRAYING
STEP -33
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS ExtendedPrice,
VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 33 AS StepID, JobMasterID, MixHeaderID, 6 AS
BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, N'SPRAYING' AS
ItemDescription,
SUM(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID, UnitQuantity,
WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
SUBSISTENCE
STEP -38 (NAILING LABOR FOREMAN / 1000)
*/
INSERT INTO @.table_variable
SELECT Nailing.StepID, Nailing.JobMasterID, Nailing.MixHeaderID, 6
AS BidSubtypeID, Nailing.WorkTypeID, Nailing.UnitName,
Nailing.UnitQuantity,
Nailing.ItemDescription, Nailing.ItemQuantity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQuantity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisible,
Nailing.WSQtyAdj
FROM (SELECT - 38 AS StepID, JobMasterID, MixHeaderID,
BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, 'SUBSISTENCE'
AS ItemDescription,
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable Nailing
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription,
BidSubtypeID
HAVING (ItemDescription = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SubcontractorScale Scale ON
Nailing.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
MASKING
STEP -39
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 39 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, N'MASKING'
AS ItemDescription,
SUM(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
PREP COAT
STEP -40
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 40 AS StepID, JobMasterID, MixHeaderID, 6
AS BidSubtypeID, -2 AS WorkTypeID, UnitName, UnitQuantity, N'PREP
COAT' AS ItemDescription,
SUM(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SubcontractorScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

SUPERVISION
SUBTYPE 7

//////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
NAILING
STEP -34
*/
INSERT INTO @.table_variable
SELECT Nailing.StepID, Nailing.JobMasterID, Nailing.MixHeaderID, 7
AS BidSubtypeID, Nailing.WorkTypeID, Nailing.UnitName,
Nailing.UnitQuantity,
Nailing.ItemDescription, Nailing.ItemQuantity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQuantity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisible,
Nailing.WSQtyAdj
FROM (SELECT - 34 AS StepID, JobMasterID, MixHeaderID,
BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'NAILING' AS
ItemDescription,
ItemQuantity, IsVisible,
WSQtyAdj
FROM @.table_variable Nailing
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription,
BidSubtypeID
HAVING (ItemDescription = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SupervisionScale Scale ON
Nailing.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
TAPING
STEP -35
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 35 AS StepID, JobMasterID, MixHeaderID, 7 AS
BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'TAPING' AS
ItemDescription,
SUM(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID, UnitQuantity,
WSQtyAdj, IsVisible) VT INNER JOIN
SupervisionScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
PICK-UP
STEP -36
*/
INSERT INTO @.table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.ItemQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 36 AS StepID, JobMasterID, MixHeaderID, 7
AS BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, N'PICK-UP'
AS ItemDescription,
SUM(ItemQuantity) / 1000
AS ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable T1
WHERE (StepID IN (- 16.1, - 16.2, - 16.3))
GROUP BY UnitName, JobMasterID, MixHeaderID,
UnitQuantity, WSQtyAdj, IsVisible) VT INNER JOIN
SupervisionScale Scale ON VT.ItemDescription =
Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID)
/*
PROJ MGR
STEP -37
*/
INSERT INTO @.table_variable
SELECT Nailing.StepID, Nailing.JobMasterID, Nailing.MixHeaderID, 7
AS BidSubtypeID, Nailing.WorkTypeID, Nailing.UnitName,
Nailing.UnitQuantity,
Nailing.ItemDescription, Nailing.ItemQuantity
/1000 as UnitQuantity, Scale.Price, (Nailing.ItemQuantity/1000) *
Scale.Price AS ExtendedPrice, Nailing.IsVisible,
Nailing.WSQtyAdj
FROM (SELECT - 37 AS StepID, JobMasterID, MixHeaderID,
BidSubtypeID, 0 AS WorkTypeID, UnitName, UnitQuantity, 'PROJ MGR' AS
ItemDescription,
ItemQuantity, IsVisible, WSQtyAdj
FROM @.table_variable Nailing
GROUP BY JobMasterID, MixHeaderID, UnitName,
UnitQuantity, ItemQuantity, WSQtyAdj, IsVisible, ItemDescription,
BidSubtypeID
HAVING (ItemDescription = N'FOREMAN') AND
(BidSubtypeID = 3)) Nailing INNER JOIN
SupervisionScale Scale ON
Nailing.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @.LaborEffectiveDate) AND
(Scale.AreaTypeID = @.AreaTypeID);
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

SCALE CHANGES -- NOT USED-- PERFORMED FROM INSIDE OF WRAPPER PROC

//////////////////////////////////////////////////////////////////////////////////////////////////
In this case, we needed two subqueries: one to pick up the values that
we wanted and another to apply these to the correct rows on the outer
table.
*/
/*
update @.table_variable
set scalevalue =(select scalevalue from ScaleChange
where JobMasterID = ScaleChange.JobMasterID AND BidSubtypeID =
ScaleChange.BidSubtypeID AND ItemDescription =
ScaleChange.ItemDescription)
where exists (select * from ScaleChange
where JobMasterID = ScaleChange.JobMasterID AND BidSubtypeID =
ScaleChange.BidSubtypeID AND ItemDescription =
ScaleChange.ItemDescription)
*/
RETURN
ENDStephen Patten (stephenpatten@.hotmail.com) writes:
> While in the process of building my table (40 or so Insert statments)
> can I then query ("select * from @.Table_variable") and use the results
> up to theat point for another insert into @.Table_varible? If you look
> for stepID -15 I have commented that section out due to it not
> retuning the correct values.

Could I ask you that next time you post a question, to be kind to be a
little more elaborate about what your problem is, and include any error
messages you get? At the same time, could you please trim down the
amount of code you post to the relevant parts? 2000 lines of code is a
little wee bit too much.

I have to decline to answer your actual question, because I don't see
what you are getting at. I can only give the recommendation that
you always specify which columns you are inserting into. This makes
the code easier to read, less sensitive to changes in the table
definition.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 22, 2012

Bulk Insert Related Tables - PKs

Tuesday, March 20, 2012

bulk insert question

bulk insert question

bulk insert question

Wednesday, March 7, 2012

Bulk insert causing timeout

Bulk insert causing timeout

Bulk insert causing timeout

Thursday, February 16, 2012

Bulk Copy Errors

Sunday, February 12, 2012

building a multi statement table UDF

build sql script

Blog Archive

About Me