Multi-Table Join Help

Feb 7, 2002

I want only one row for each contact that contains the most recent calendar as enddate and most recent history as ondate. I'm getting multiple rows for each matching contact.

--------------------------------------
select c1.contact ,ca.enddate ,ch.ondate

from ca

join c1 on ca.accountno = c1.accountno

join ch on ca.accountno = ch.accountno

where ca.ondate in (select max(ondate) from ca group by accountno)
------------------------------------------

View 1 Replies


ADVERTISEMENT

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Multi-table Join Problem

Jan 9, 2008

Hello All,

I have three tables A, B, and C.
What i want to do is basically left join A with B, then left join B with C.
However when try to do this it won't work out as i imagined.
Is there a better way (one that works) for accomplishing what i am trying to do?

Any help is much appreciated.

Thanks,

View 7 Replies View Related

Ambiguous Column Names In Multi-Table Join

Jun 21, 2006

Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S

View 6 Replies View Related

Help With Multi Join Or Multi Tier Select.

Jul 20, 2005

Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid

View 5 Replies View Related

Join Multi Server

Oct 9, 2006

hi,consider this:i have american users in a db called "user" in a server called AMERICAand rest of the world users in a db called "user" in a server called OTHERhow can i do a join betwhen  American's users and Rest of the word's User db?   

View 3 Replies View Related

Multi Database Join

Apr 8, 1999

Is it possible to create a join between two SQL Server DB's on the same server? What is the syntax?

View 1 Replies View Related

Multi Join Stored Proc

Nov 20, 2007

Hi,
Why does the below not return any results???
Colorcodes mark the related keys.

Thanks.


Tables:
FundClient (ClientID PK, Client)

FundPortfolio (PortfolioID PK, Portfolio, ClientID FK)

Staff(StaffID PK, SeniorMgr, ClientID FK, FundID FK)

myLegal(myID PK, LegalCounsel FK, ClientID FK, FundID FK)

FullLegalList(LegalID PK, LegalName)





Code Block
@LegalCounsel int = 0,
@ClientID int = 0,
@FundID int = 0

DECLARE @thisQuery as varchar(max)

SET @thisQuery = 'SELECT p.Portfolio, SeniorMgr, fl.Legal FROM FundClient f'
BEGIN
SET @thisQuery = @thisQuery + ' INNER JOIN FundPortfolio p
ON p.ClientID = f.ClientID'
END
BEGIN
SET @thisQuery = @thisQuery + ' LEFT OUTER JOIN Staff s
ON (s.ClientID = p.ClientID AND s.FundID = p.PortfolioID AND s.ClientID = f.ClientID)'
END
BEGIN
SET @thisQuery = @thisQuery + ' LEFT OUTER JOIN myLegal l
ON (l.ClientID = p.ClientID AND l.FundID = p.PortfolioID)
INNER JOIN FullLegalList fl
ON fl.LegalID = l.LegalCounsel'
END
BEGIN
IF @Legal != 0
SET @thisQuery3 = @thisQuery3 + ' WHERE rl.Legal = ' + cast(@LegalCounsel as varchar(11))
END
BEGIN
IF @ClientID != 0
SET @thisQuery = @thisQuery + ' AND p.ClientID = ' + cast(@ClientID as varchar(11))
END
BEGIN
IF @FundID != 0
SET @thisQuery = @thisQuery + ' AND p.PortfolioID = ' + cast(@FundID as varchar(11))
END
BEGIN
SET @thisQuery = @thisQuery + ';'
END





View 12 Replies View Related

Help Me! Convert SQL Syntax To LINQ With Multi Join

May 5, 2008

Hi...
I have 3 tables:SportTeams (TeamID, TeamName)SportAthletes (TeamID, AthleteID, AthleteName)SportMedals (AthleteID, Medal)
I want to have a brief medal list (TeamID, G, S, B). I can write query in systax:
Select a.TeamID, a.TeamName_en,g.G,s.S,b.B from SportTeams aLeft Join(Select c.TeamID,Count(*) as G from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'G'Group By c.TeamID) g On a.TeamID = g.TeamIDLeft Join(Select c.TeamID,Count(*) as S from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'S'Group By c.TeamID) s On a.TeamID = s.TeamIDLeft Join(Select c.TeamID,Count(*) as B from SportMedals d Inner Join SportAthletes c On d.AthleteID = c.AthleteIDWhere Medal = 'B'Group By c.TeamID) b On a.TeamID = b.TeamIDOrder By g.G desc, s.S desc, b.B desc, a.TeamID asc
But I can't write it in LINQ syntax (I am beginner with C#, LINQ)
Can you help this sample to LINQ systax?
Thanks!

View 5 Replies View Related

Left Outer Join / Multi-part Identifier Error

Jul 22, 2014

I'm writing a query where I have multiple left-outer joins but I keep getting multi-part identifier error. See the query below?

SELECT gl.seg5 Natural
,gl.seg2 Office
,gl.seg3 Dept
,gl.seg4 Team
,gl.seg6 Sub
,gl.seg7 Tkpr
,gl.seg1 Comp
,'CHK' Source

[Code] ....

Errors

Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.baid" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.cknum" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.ckline" could not be bound.

View 5 Replies View Related

Select From Linked Server Using Join, The Multi-part Identifier Could Not Be Bound.

Nov 6, 2006

Hello,

i am facing a bizarre problem, accessing data from a remote server which has been linked to my sql.
My SQL server is 2005 and the remote server is SQL 2000.
i have linked the remote server (called LinkedServer) so that when i run this query:
SELECT * FROM LinkedServer.SomeDB.dbo.SomeTable
executes successfully! However, when i run a similar query like this:
SELECT Column1 AS Col1,
LinkedServer.SomeDB.dbo.SomeTable.Column2 as Col2,
Alias.Column3 as Col3
FROM LinkedServer.SomeDB.dbo.SomeTable
INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable1 ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = LinkedServer.SomeDB.dbo.SomeOtherTable1.Column3

INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable2 AS Alias
ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = Alias.Column3

It gives me this error:
The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column2" could not be bound.
The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column3" could not be bound.
I have noticed that this error is generated only for the selected columns whose path has either been repeated (e.g. "LinkedServer.SomeDB.dbo.SomeTable.Column2" as opposed to "Column2") or for columns which are from aliased tables.

What is going on?!?!?!??!?!?!

Any Help would be tremendously appreciated!!!! 8..)

View 8 Replies View Related

Transact SQL :: Create Hierarchies Table Or Query From Multi Parent Table?

May 21, 2015

convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.

ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent

View 13 Replies View Related

How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?

Dec 1, 2006

I have one control table to store all related table name
 Table ID                   TableName
     1                           TableA
     2                           TableB
 
In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3
 
In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
 How can I get the result by select the Table list first and then combine the data in table A and table B?
 
Thank you!

View 1 Replies View Related

Multi-Table Update??

May 26, 2004

Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?

What is the alternatives??

I have to match two tables up and use records from one to update the other and creating a view isn't working....

View 5 Replies View Related

Multi Table Lookups

Sep 12, 2014

I have some SQL experience, but nothing past basic commands. I'm trying to take some data held by an application to use as CSV import into another application.I have two tables from an application, one holds references made in another.The first tables holds details about a person:

field1=name field2=age field3=country

Joe,50,1

Country is held as a number, then there is another table that holds all the countries:

field1=id field2=description

1,USA
2,France
3,Germany

I want to do a lookup where it returns:

Joe,50,USA

View 1 Replies View Related

Multi Table Source

Jul 9, 2007

I am wondering how I can create an OLE DB Source component that can store a multi-table DataSet object. Is this something that is possible or do I need some custom object to do this? I'm sure I can create a multi-table destination object and create sources for each data table needed however, I need to get the data for 5 tables and do this about 30K times. I'm thinking this approach will perform better.



Here is what I've been trying to get working. (Note there is only one parameter that all the queries use - @keyName)



SELECT * FROM Table1
WHERE (Key = ?)



SELECT * FROM Table2
WHERE (Key = ?)



SELECT * FROM Table3
WHERE (Key = ?)



SELECT * FROM Table4
WHERE (Key = ?)



SELECT * FROM Table5
WHERE (Key = ?)



TIA



Ian

View 4 Replies View Related

How Do You Do A Multi-Table Insert In One Statement?

Mar 31, 2004

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

View 2 Replies View Related

Multi-table Queries For M:N Situations

May 29, 2006

Hi friends,

when I write multi-table queries which involve two tables which are joined via a bridging table (M:N),

do I just join the tables or do I have to reference the bridging table as well in the queries?

Cm

View 3 Replies View Related

MULTI-Table Update Queries

Jul 23, 2005

I'm new to adp w/ sql server but I have to use it on a project i'mdoing...One of the MUSTS for this project is the ability to update a 00 - 09text value with the appropriate text description from another table...Easy as pie in .mdb. Of course In the stored procedure it barks at meand tells me that an update query can only have one table.. ouch thathurts...I'm currently reading on the subject but this group has been veryhelpful in the past.....I found this link...http://www.sqlservercentral.com/col...stheeasyway.aspUnfortunetly I'm using MSDE not Enterprise so I don't think I can usethe query analyser.. But I tryed it in my Access ADP anywayit barked at me..I tried to go from this....SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1FROM dbo.TEST INNER JOINdbo.LU_SEX ON dbo.TEST.SEX_DP1 =dbo.LU_SEX.SEX_DECTo this...UPDATE dbo.TEST.SEX_DP1SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODEFROM dbo.LU_SEX INNER JOINdbo.TEST ON dbo.LU_SEX.SEX_DEC =dbo.TEST.SEX_DP1Maybe I need a good book on this?Thanks,Charles

View 2 Replies View Related

Multi-table UDF Not Returning All Rows

Sep 7, 2005

I've been tearing my hair out over this UDF. The code works within astored procedure and also run ad-hoc against the database, but does notrun properly within my UDF. We've been using the SP, but I do need aUDF instead now.All users, including branch office, sub-companies and companies and soon up the lines are in the same table. I need a function which returnsa row for each level, eventually getting to the master company all theway at the top, but this UDF acts as though it can't enter the loop andonly inserts the @userID and @branchID rows. I have played with theWHILE condition to no avail.Any ideas on what I am missing?(Running against SQL Server 2000)---------------------------------------------------ALTER FUNCTION udfUplineGetCompany (@userID int)RETURNS @upline table (companyID int, companyname varchar(100), infovarchar(100))ASBEGINDECLARE @branchID intDECLARE @companyID intDECLARE @tempID int--Insert the original user dataINSERT INTO @uplineSELECT tblusersid, companyname, 'userID'FROM tblusersWHERE tblusersid = @useridSELECT @branchID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @userid--Up one levelINSERT INTO @uplineSELECT tblusersid, companyname, 'branchID'FROM tblusersWHERE tblusersid = @branchidSET @tempID = @branchIDWHILE @@ROWCOUNT <> 0BEGINSELECT @companyID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @tempIDAND tblUsersId <> 6--Insert a row for each level upINSERT INTO @uplineSELECT tblusersid, companyname, 'companyID'FROM tblusersWHERE tblusersid = @companyIDSET @tempID = @companyIDENDRETURNEND

View 2 Replies View Related

Building A Multi Statement Table UDF

Jul 20, 2005

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

View 1 Replies View Related

RDA - Multi Table Error For Tracking

Apr 18, 2007



Hello,



I receive an error message when I try to Push data that the table is not tracked. However, when I try to turn on the tracking option it gives me an error that the table is a multi query table and therefore cannot be tracked. Here is my code to Pull the table.

string TPDAPull = string.Format("SELECT Table1.Field1,Table1.Field2, Table1.Field3, Table1.Field4, from Table1 Left Join Table2 on Table1.Field1 =Table2.Field1 WHERE Table2.Field12='{0}'", this.FindWorker(var));

rda.Pull("Table1", TPDAPull, rdaOleDbConnectString, RdaTrackOption.TrackingOn);



This table does not have a primary key. I was wondering what can I do in this situation? I do not want to Pull the whole table. Any suggestions would be greatly appreciated. I am working in VS 2005, NCF2.0, C#, WM5.0.



Thanks in advance!

View 6 Replies View Related

Adding Multi Records To An SQL Table

May 7, 2007

I'm trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.

I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:

use [c:developesqlsqldatapsw.listener]
append from 'c:developesqldatalistener.txt' type sdf
goand then I pressed F5. I get an error that says:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'.No entry found with that name. Make sure that the name isentered correctly.
I checked in the directory c:developesqlsqldata and the filepsw.mdf is there and when I look in the psw database, the tablelistener is also there.
Could someone tell me what is going wrong? Also, how do Iappend the whole dbf table onto an SQL table. there's about6000 records.

Thanks.

View 2 Replies View Related

SQL 2012 :: Disaster Recovery Options For Multi-Database Multi-Instance Environment

Sep 23, 2014

Disaster Recovery Options based on the following criteria.

--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.

What I have looked into is:

1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.

View 1 Replies View Related

SQL 2012 :: MSDTC In Multi-node / Multi-instanced Cluster

Aug 17, 2015

More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.

1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?

View 9 Replies View Related

The Multi Delete &&amp; Multi Update - Stored Procedure Not Work Ok

Feb 4, 2008

the stored procedure don't delete all the records
need help



Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 2 Replies View Related

Multi Relational Table Design Question

Dec 10, 2006

Hi! Im working on a webapplication and has serious thoughts about howto optimize my table structure. To explain:
My tablestructure today



(simplified):tbl_customerscust_idname.....tbl_contactscon_idname.....tbl_groupsgrp_idname..... 
My subtables look like this(alternative 1):tbl_sub_phonephone_idparent_typeparent_idphone_areaphone_nr.....tbl_sub_emailmail_idparent_typeparent_idemail..... 
As seen above every contact, group and customer can be assigned an unlimited amount of phonenumbers or emailadresses.For example when entering a new email or a customer following will be inserted in tbl_sub_email: parent_type = 'cst', parent_id= '2' (the cust_id from tbl_customers), email = 'gwerg@fe.com'The problem is i am uncertain if this is a very unefficient way of handling it? i see two alternatives:
Alternative 2:i create x subtables for each table  for example tbl_customers will get its mailadresses and phonenumbers contained in tbl_customers_phone and tbl_customers_emailWhat i am uncertain of here is if this would make things alot more troublesome when searching på example after a specific phonenumber.Alternative 3:



(simplified):tbl_customerscust_idname.....tbl_contactscon_idname.....tbl_groupsgrp_idname..... 

tables connection objects to subobjects
tbl_customers_phoneidcust_idphone_idtbl_contacts_phoneidcon_idphone_idtbl_customers_mailidcust_idmail_id 
subtables tbl_sub_phonephone_idphone_areaphone_nr.....tbl_sub_emailmail_idemail..... 
Ranking these three models, wich would be the most efficient and most inefficient performanswise?What i want to avoid is performanceproblems when listing the objects, my indexing skills are a bit limited although im doing alot of reading and testing regarding this.So thats why im asking for advice so that i can minimize the need of rebuilding the table structure when the application already has been starting to get used.I also have another general question.
I have alot of select querys when i need to fetch data from several different tables.Most of them is that i for example get an application from tbl_applications table, and that tables contains the columns cat1, cat2 and cat3 (wich are categories and contain the primary key integer to the tbl_sub_categorys table)With 3 joins i retrieve these 3 category names returning 1 result with all the info i need.Since ive been getting som strange results from the query analyzer(i got results that using clustered indexing for the primary key resulted in a slower query (higher cost)) i actually have another question.Can it generally be summed up that a single query(join or subquery) generaly ils faster than getting the data in separate selects?In the example above this i have the options either of using joins = 1 query or doing 2 querys and sorting the categorys codewise in aspx pages or doing 4 querys, one for the app followed by 1 for every category.Any input regarding this?
As i said earlier im looking for the most efficient way of doing the things abov, would greatly appriechiate any input!

View 3 Replies View Related

Anyone Know Table Limits In Multi-schema Environment?

Mar 27, 2007

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?thanks-c 

View 2 Replies View Related

Bind Multi-table Dataset To A Datagrid

Jul 20, 2004

I have created a SQL procedure that returns a dataset with a varying number of tables like the following example:

RepID---- PhoneUPS
----------- -----------
3---------- 3

RepID---- PhoneUPS
----------- -----------
4---------- 0

RepID---- PhoneUPS
----------- -----------
5---------- 2

No more results.
(9 row(s) returned)
@RETURN_VALUE = 0


All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)

Cheers,
Andrew

View 6 Replies View Related

Opinion Requested: Multi-Table Design

Dec 23, 2004

I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:


big_table [one-to-one] big_table_has_sections [many-to-many] sections


or, more concretely:


BuildingConstructionType
relates to
ConstructionHasTypes
relates to
Types


Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).

Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?

View 2 Replies View Related

Checking Column Name From Multi Database's Table

Aug 18, 2006

hi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks

View 2 Replies View Related

Multi-lang, Dynamic Table And SP Problem

Oct 18, 2004

Hello all,

I got a problem with Multi language when it comes to Dynamic Tables in Store procedures.

If I do the following :


SET @StringTableName = N'System_Strings'
SET @StringID = N'100'
SET @LanguageID = N'2'
SET @StringText = N'أثممخ ًخقمي'
SET @Mandatory = N'0'

EXEC (N'INSERT ' + @StringTableName + N' (StringID, LanguageID, StringText, Mandatory) VALUES(''' + @StringID + ''', ''' + @LanguageID + ''', ''' + @StringText + ''', ''' + @Mandatory +''')')


The StringText column shows up as ????????? in the Db.

However if I do the following:


INSERT System_Strings (StringID, LanguageID, StringText, Mandatory) VALUES(@StringID, @LanguageID, @StringText, @Mandatory)


The StringText column shows with the correct Arabic text.

Does anybody know how to use Dynamic Tables aswell as Multi Language?

Best Wishes,
Farek

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved