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 RepliesHi 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 RelatedHello
I am trying to do the following:
1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter.
2. Then, use it like this: "Select * from table T, mstvF1( T.id )"
It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id.
If I do
Select * from table T, mstvF1( 5 ), then it works.
Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T?
Thanks for any help.
I'm creating a Multi-statement Table-Valued Function...
Is it possible to insert variables into the table? In other words, is it possible
to have something like
declare
@value1 varchar(10)
@value2 varchar(10)
BEGIN
<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2
instead of
BEGIN
<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE
Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do
Source_table
col1 col2
Mike 10
Mike 20
Ben 50
John 15
John 25
John 35
The table that my function needs to create should look like this
col1 col2 col3
Mike 10 1
Mike 20 2
Ben 50 1
John 15 1
John 25 2
John 35 3
I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.
Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.
Thank you.
Hey
I have created a multi-statement
table valued function
alter
function
fn_x(@x int)returns
@tbl table
(
position int identity primary key, i
int)
as
begin
insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) returnend
Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'"
The other solution is declaring
each udf separately as one statement udf.Thanks
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
Hi all,
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?
Thanks, Jos
Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/
PRINT @@VERSION
go
PRINT 'Scalar function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'In-line table-valued function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go
CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN
INSERT INTO @t VALUES (1)
RETURN
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
/*
----------------------------------------------------------------------------------------------------
*/
And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Scalar function with name "sys_" creates ok...
In-line table-valued function with name "sys_" creates ok...
Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.
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.
I hope I can explain this!!
I am trying to insert data from one table into another:
from table "sheet1" into table "actoremployments". sheet1 is a temp table that had data imported into it from an Excel doc.
Here is what I used
INSERT ACTOREMPLOYMENTS
(EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID, EmployerStreet)
SELECT EmployerCity, EmployerZip, EmployerFax, EmployerPhone, EmployerEmail, EmployerBlockNbr, StateID, EmployerStreet
FROM Sheet1
WHERE (BarNum = ACTORS.BarNum)
ACTOREMPLOYMENTS has a column named ActorID (FK) and key links to a table.column called ACTORS.ActorID. I need to insert the data from sheet1 into actoremployments where the BarNum is linked to ActorID. Sheet1 table also has a column called BarNum.
Does this make sense? How can I make this work?
On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!
Thanks
Capella07
I've 2 tables as follow, --> Full script and data as attachment, Scripts.zip
CREATE TABLE [dbo].[myMenuCollection](
[menuCollection_idx] [int] NOT NULL,
[parentID] [int] NULL,
[code]....
You can see - User select 3 Menu, which is the Menu Id is 1, 4, 10.If the Parent Id for Menu is 0, there is 1 record only to insert. If the Parent Id for Menu != 0, we've to make sure the Insert statement will insert the Parent Menu automatically
Based on Photo Above, there's 3 Menu is selected. But, in back-end - Insert statement will insert 4 record. Please see Menu Id = 10. The Parent Id = 9. So, we need to insert Menu Id = 9 automatically into myInsertedMenu table
Hi,A conflict error occured when I insert data using Formview.The INSERT statement conflicted with the FOREIGN KEY constraint "FK_personal_personal_category". The conflict occurred in database "E:INETPUBWWWROOTWEBSITEAPP_DATADATABASE.MDF", table "dbo.personal_category", column 'cat_id'.The statement has been terminated. ----------------------------------------------------Personal TablepID (primary)pNamecat_id (foreign key from personal_category, which has a relationship with it's primary key cat_id)pID | pName | cat_id1 | Scott | 112 | John | 122 | Mick | 13-----------------------------------------------------Personal_Category Tablecat_id (primary)cat_Descriptioncat_id | cat_description11 | Dean12 | Student13 | Professor-----------------------------------------------------I'm inserting a new data on Personal Table while choosing a category usinga DropdownList which is binded to another DataSource (LinqDataSource2).... <asp:FormView ID="FormView1" runat="server" DataKeyNames="tut_id" DataSourceID="LinqDataSource1" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DefaultMode="Insert" ForeColor="Black" GridLines="Vertical"> <FooterStyle BackColor="#CCCC99" /> <RowStyle BackColor="#F7F7DE" /> <InsertItemTemplate> <asp:TextBox ID="pName" runat="server" Text='<%# Bind("pName") %>' /> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="LinqDataSource2" DataTextField="cat_desc" DataValueField="cat_id" DataSource='<%# Bind("tut_cat") %>' > </asp:DropDownList> </InsertItemTemplate> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> </asp:FormView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="PersonalsDataContext" TableName="Personal" EnableInsert = "true"> </asp:LinqDataSource> <asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="PersonalCategoriesDataContext" TableName="Personal_categories" EnableInsert="false" > </asp:LinqDataSource>... Hope you could help.cheers,imperialx
View 2 Replies View Relatedhi.
i cant get this quite right.
i have a table and i need to insert one column with data from another table. it goes something like this (although i know this is wrong, just here for a visual explaination) :
Code:
INSERT INTO List
(list_date, email_address, list_status, list_email)
values
(
GetDate()
, 'name@rice.edu'
, 0
, SELECT emailAddress FROM Users WHERE id = '72'
)
so, list_email needs the email address from the Users
table. i tried messing around with inner joins but, well,
here i am...
thanks in advace.
Hi guys,
anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.
so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".
so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?
regards
terence chua
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies View Related I have to add a step into a SQL job agent which needs to be a insert statement to a table from another table. The insert statement is not the issue as you can see below. The purpose of this insert statement is to update table dbo.INV_Items with any new item numbers added to dbo.PartInfo table. I guess my question is is it just a insert statement or do i require a special function.
insert into [dbo].[INV_Items](ITEMNMBR)
select [Item] as ITEMNMBR
FROM [dbo].[PartInfo]
For some reason, I'm getting this error, even without the DBCC Check:
INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.
The very first insert fails...it was working fine before:
DELETE Category;
-- Now, insert the initial 'All' Root Record
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'
Here's the schema:
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
Hello,
This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...
http://msdn2.microsoft.com/en-us/library/ms178043.aspx
At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).
http://msdn2.microsoft.com/en-us/library/ms189098.aspx
and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....
Here is how I set everything up...
PetitionSet table consists of:
PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls
the Petition table consists of:
PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls
the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int
And, there is a composite key made up of both the PetitionSetID and PetitionID fields.
I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.
The trigger is on the Petition table and it has the following code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID
END
I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...
The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.
Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...
Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.
David
All Rights Reserved in All Media
I understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT
[Code] ....
Sometimes I want to quickly to edit a record in a table instead of using an insert statement.
Sometimes there are auditing columns like DateCreated, and CreatedBy,
I know it can be made as default. for DateCreated to be sysdatetime, and createdby to be system user.
But I just curious to know if there is a way to manually enter today's date and the user in the cell?
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>
[Code] ....
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
Why this statement doesn't work correctly?
I am unable to load data from flat file to sql table using bulk insert sql statement
My code:-
DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'
[Code] .....
Is it possible for an AND statement to take mulitple parameters? See below code:
SELECT tblQuestion.Question, tblAnswer.Answer
FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID)
WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14)
Is something like this possible?:
AND (tblAnswer.QuestionID = 14, 26)
Thanks
I have a SQL2k5 table capturing transaction via an online vendor application. I need to join this data with cooresponding data that exist within a 2003 MS Access .MDB application. I've had recent training in SQL Serv 2005 T-SQL and programming. This is what I'm planning but not if this is the best/simple plan.
write After Insert trigger for SQL tbl to invoke a sp
write After Insert trigger for SQL tbl to invoke a sp (OK with this) write a sp to select last inserted record from SQL tbl (OK with this)
sp will use pk_col to pull data from linked MS Access 2003 using OPENQUERY or four part naming (is one way better than the other?)
insert Parent record into MS Access parent tbl (after insert, how can i get Parent Key from this insert to use as Child FK col? will @@identity work?)
insert Child record into MS Access child tbl (how can i get Child Key from this insert to use as GrandChild FK col? @@identity?)
insert GrandChild record into MS Access GrandChild table
any help greatly appreciated!
Thanks for being there....
Tom
I'm pretty new at T-SQL programming, though I'm pretty familiar with most SQL statements. I'm trying to write an insert trigger that handles a multi-row insert, but I'm avoiding using cursors (which I'll have to resort to if all else fails). I've looked everwhere I can think of for an answer, outside of buying another book, but I can't seem to find a solution. Anyone have a good way I can alter this trigger to accept multirow inserts without using a cursor?Note: The purpose of this trigger is to copy the features of a mobile phone account whenever a new mobile phone account is created (as in this is the information for an invoice, and the mobile account is to be copied and updated for the new invoice each month.)CREATE TRIGGER [copymobilefeatures] ON [dbo].[mobilesub] FOR INSERTAS
insert into #featuresselect i.invoicedate, i.subaccountnumber, i.planid, f.featureidfrom mobilesub m join (inserted i join mobilefeatures f on i.invoicedate = f.invoicedate ) on m.subaccountnumber = f.subaccountnumber
insert into mobilefeatures(invoicedate, subaccountnumber, planid, featureid)select * from #featuresI'd love code examples, but a detailed written explaination of what needs to be done might be even more helpful. I'm looking for understanding, not just something I can copy and paste. Thanks!
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!
if there are 2 insert statement
insert ... select * from table1 union all select * from table2
or
insert ... select * from table1
insert ... select * from table2
pls advise which 1 is faster ...
Hello,
I am wondering if there is a way to insert one parent record with multi child records in one transaction? I am using dataset to update my database. I want to use transaction so if one record insert fails all the transctions rollback.
Thanks
Your Input would be greatly appricated.
I would like to know how to, if at all possible, to reconstruct the following trigger as to be able to handle multiple row insert when a single insert command is used - because the trigger will only be called once...I'm not familiar and don't know anything about cursors and i've read that its not the best way to go.
TRIGGER ON childtable INSTEAD OF INSERT
AS
BEGIN
DECLARE @customkey char(16);
DECLARE @nextchild int;
DECLARE @parent int;
DECLARE @date datetime;
SET @date = getdate();
SELECT @parent = parenttable FROM inserted;
SELECT @nextchild=count(*)+1 FROM childtable WHERE parenttable = @parent;
IF (@nextchild >= 9998) return;
SET @customkey = €˜type€™+ convert(char(4),year(@date)) + convert(char(2),month(@date)) + convert(char(2),day(@date))+convert(char(4),@nextchild + 1);
INSERT INTO childtable (customkey,parent) VALUES (@customkey,@parent);
END
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedHello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.