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

Table Lookups

Mar 17, 2008

Im having a little diff speeding up the below process

I have two tables table a contains 300,000 rows which are unique however the identifier can appear more than once. The fields im interested in are the identifier and a date/time field.

Table two also contains an identifier and a date field and again can contain multiple instances of the identifier with a variyity of dates.

For each row in table a i want to review the date and look up table b for the first date greater then or equal to the date linking by the identifier.

i have managed to do this via the code below however it takes 45 mins and i want to speed this up.

Select
a.*,
(select Min(DateB) as DateB From #tableB b where a.identifier = b.identifier and b.DateB >= a.DateA) asDATE
From #TableA a

View 2 Replies View Related

FAct TABLE LOOKUPs Data

Jun 19, 2007

Hi,
Please help me out in loading the fact tables

I had used lookup on DIM table to get my SUK and if I use union transformation to get the out put from each lookup and then loading the data with some condition the data in my fact is not loading in a proper format.

The union transformation is splitting the out put in to different records

Please do inform me about which transformation should be used to get the data from lookup tables.

Or please do inform me the approach to load the fact table in SSIS.

I��m basically INFORMATICA resource and I��m implementing in terms of INFORMATICA

View 6 Replies View Related

4 Lookups Against Single Large Table

May 4, 2006

I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table.

My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory.

Any ideas/suggestions would be appreciated.

View 4 Replies View Related

Single Source, Multiple Lookups Against Same Table

Nov 16, 2006

Let's say I have 4 columns coming from my OLE DB source.

Column1
Column2
Column3
Column4

I also have a table that I'll be using in a lookup, LUPTable. In LUPTable, I have two fields, LUPField, ReplaceField.

In my data flow, I need to take columns, 2-4, and look them up against LUPField in LUPTable. I then need to add the value of ReplaceField (when a match is found) into the data flow.

The problem that I'm running into is that I don't want to sequentially do the lookups in the dataflow, because that's just a waste of time/memory. I only need to build the in-memory lookup table once, because that exact same data (it is static, for the most part) will be used for the remaining lookups.

What is the best way to achieve this?

The goal is to have the following columns remaining in the dataflow:
Column1
NewColumn2 (containing value from ReplaceField)
NewColumn3 (containing value from ReplaceField)
NewColumn4 (containing value from ReplaceField)
Column2-4 can be dropped from the dataflow after the lookups.

Thanks,
Phil

View 7 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

DTS Lookups

Aug 24, 2001

I saw a DTS Lookup sample here on SWYNK, but I still have no idea what they're talking about.

Can someone dissect this piece of code and tell me what it does?

DTSLookups("TransformSpecialty").Execute(DTSSource ("LocalSpecialty").Value)

You can reference those tables they have in the example. http://www.swynk.com/friends/green/dtslookups.asp

Thanks!

Lost, ttlai

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 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 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

Problem With Too Many Lookups!!

Feb 28, 2008

Hi,
My process passing 1,000,000 rows to a data flow with about 20 lookups to get the keys that I wantted .
Most lookups have small number of rows except one with over 5,000,000 rows. I cannot get the process to run (the process hanged) probably because of memory issue. Any clue where/how I can tune it.
Thanks

View 1 Replies View Related

Date Lookups

Nov 13, 2006

I want to do a lookup on date column. My lookup date is of type smalldatetime, and my date is of type datetime (date with time component). My lookup is failing because of incompatible data types.

How do I perform the lookup with date columns having date and time components?

View 1 Replies View Related

Large Lookups

Apr 28, 2008

Hello,

I have a source table with few million rows in it. As a part of transformation, I need around 10 lookups in 10+ different tables, all of them having few million rows each. I am looking for an approach that would be reasonably speedy and easy to manage future changes.

Here are some of the things that I have tried...
(1) If I implement as lookup components, they cause developer machine to go really slow and takes forever to run.
(2) I tried having OLE DB Source query to fetch required data up front. But the source query becomes very complicated which will be even harder for future changes. And this big query cause SQL Server to become unresponsive.
(3) Update queries on target table are also causing server to be unresponsive.

What would you guys suggest for this type of implementation?

Kapil

View 5 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

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

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

Memory Problem With Lookups!!!

Mar 26, 2008

Hi,
We use lookups to join a few huge tables in SSIS (each has more than 40 million rows). The process took almost two days to complete when we select partial load on lookups. It stops/locks if we select full load on lookups.

We have a 32bit server so SSIS uses only 2-3GB of available memory no matter how big RAM we have. It seems the best solution for my problem is to move to 64bit server so SSIS uses up to 16GB of Ram.

For now I am researching for a remedy solution to get better performance from our current environment while we are waiting for the big server.

I’d like to hear your thoughts and options that may improve the performance of our package. Dose partitioning help? What else could be helpful?

View 4 Replies View Related

Transformation For Lookups Between Two Values

Jul 6, 2006

I have a dimension table for Retail Order Size. Each row in the dimension has a Starting Value and Ending Value column pair. In TSQL, the correct RetailOrderSize key is found by using the BETWEEN statement, like so:

SELECT RetailLevelKEY

FROM dbo.DimRetailOrderSize

WHERE @Sec1Retail BETWEEN StartingValue AND EndingValue

Is there a Data Flow Task Transformation in SSIS that replicates this functionality, or some other way of getting to the same answer in SSIS?

Thanks in advance for your help

View 1 Replies View Related

DTS Designer Lookups Error

Apr 16, 2008

Whenever we open a task and choose the lookups tab ellypsis button we get this error

Microsoft SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Any idea what might be causing this? Am I missing something?

View 3 Replies View Related

SSIS Non-Equijoin Lookups

Apr 25, 2006

When do a fact table load...I have to perform lookups against the dimension
tables. The dimensions tables I have support slow changes, however, and thus
have multiple rows for a single legacy key under different effective start
and end dates. In order to do this lookup, I have to not just join on the
legacy key, but also validate that the date of the transaction I'm loading is
between the effective date range of the dimension item.

It seems the Lookup task only supports equijoins. Am I missing something
here? How is this accomplished if you can use greater than or equal to and
less than type join conditions?

View 3 Replies View Related

High Performance Lookups

Dec 22, 2007

Ive got an ETL process I have written which takes about 10 million rows from a staging database and loads it into production database with an INSERT statement. The INSERT statement makes a function call to retrieve the surrogate key for each row. The function looks in a replicated copy of our production database so no load is on our production environment during this time.

So: INSERT INTO foo(...) SELECT name, address, zip, dbo.fnGetSurrKey( name, address)

It took about 12hrs to insert 6 million rows last night and Im wondering if there is a better way of doing this. Maybe a multithreaded way like SSIS might have.

Assuming my function is optimized as much as possible, does anyone have any tips for speeding this up?

Also, the machine this ran on has 16gb of RAM but was setup to use only 2GB during this process. I have already changed it to 12gb and restarted the process a week ago, but the change doesnt take affect until you reboot. Would I see a significant performance increase from that?

View 7 Replies View Related

Case Insensitive Lookups?

Feb 2, 2006

Is there an easy way to get the Lookup component to ignore case?

View 1 Replies View Related

Lookups And Their Error Flows

Nov 13, 2006

So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.

I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.

Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.

View 4 Replies View Related

Consolidating Multiple Lookups

Feb 18, 2007

In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)

Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.

Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?

View 4 Replies View Related

Passing Parameters To LookUps

Jun 1, 2007

I am trying to make a SSIS package..

In the dataflow of my package, I must check from one table whether a row exists, and if that row exists, I should get some other row from another table, and update that..

I think to check whether a row exists, i should use "Look Up"

But cant we pass parameters to LookUP?

I am trying to use this SQL:

SELECT count(*) FROM ServicePackets where ID = ? and CHANGEDATE > ? and status = 1

I should get if that row exists or not only... (true or false)

How can i use parameters in LookUps?

View 7 Replies View Related

How To Automate TONS Of Lookups

Apr 10, 2008

OK I have this table I am grbbing from Oracle and I need to take selected columns and do a value lookup against another table: IE Here is a list of fields I get from Oracle:

Code SnippetENTRY_ID
SUBMITTER
CREATE_DATE
ASSIGNEE
LAST_MODIFIED_BY
MODIFIED_DATE
STATUS
SHORT_DESCRIPTION
CATEGORY
TYPE
ITEM
SCHEMA_NAME
SCHEMA_KEYWORD
ID
FULL_NAME
USERNAME
PAGER
PAGERPIN
PAGING_METHOD
RECORD_TYPE
GROUP_ID
GROUP_ALIAS
APPLICATION
NOTIFICATION_SERVICE
ARS_GROUP_NAME
GROUP_TYPE
AUDIT_TRAIL
FULL_NAME_
RESPONSIBILITY
PAGER_EMAIL_ADDRESS

Now I need to do a value lookup on these fields:

Code SnippetSTATUS
RESPONSIBILITY

In this example it is only 2 but in other conversions it could be 20 or more... Now here is my select statement for each Field (The ? being the FIELD from before IE Status or Responsibility):

The is for Status:

Code Snippet
SELECT VALUE AS STATUS_VALUE
FROM Field_Values
WHERE (NAME = 'Project Name') AND (ENUMID = ?) AND (FIELDNAME = 'Status')

This is for Responsibility:

Code SnippetSELECT VALUE AS RESPONSIBILITY_VALUE
FROM Field_Values
WHERE (NAME = 'Project Name') AND (ENUMID = ?) AND (FIELDNAME = 'Responsibility')

So for this the way I am doing it now is I have 2 "Lookup" Components setup... It works fine... However as I said when I get say 20 or so it gets really tiresome. I was wondering if I could feed it a EXCEL or XML file saying these are the fields that need a value lookup where:

FIELD - Project - FIELDNAME VALUE - OUTPUT VALUE

So with this example I would have a file saying something like this:
STATUS - Project Name - Status - STATUS_VALUE
RESPONSIBILITY - Project Name - Responsibility - RESPONSIBILITY_VALUE

Then it runs whatever and returns the *_VALUE for each row it goes through... Any suggestions?

View 12 Replies View Related

How Not To Cache In Lookups To Oracle

Jul 20, 2006

Why can you not turn off the caching in a Lookup against Oracle?

I have an exceedingly complicated SQL statement like this -

SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE

If I turn off the cache for a lookup I get bombarded with this rubbish-

Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0

Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0

I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".

Any ideas ?

View 7 Replies View Related