Building A Multi Statement Table UDF

Jul 20, 2005

Hi All,


While in the process of building my table (40 or so Insert statments)
can I then query ("select * from @Table_variable") and use the results
up to theat point for another insert into @Table_varible? If you look
for stepID -15 I have commented that section out due to it not
retuning the correct values.

Thank you in advance
Stephen Patten

Table Code:

ALTER FUNCTION dbo.BidContract
(
@MixHeaderID int,
@MaterialEffectiveDate nvarchar(10),
@LaborEffectiveDate nvarchar(10),
@AreaTypeID int,
@NailingParam int,
@TapingParam int
)

/*
@MixHeaderID int = 2,
@MaterialEffectiveDate nvarchar(10) = '2003-01-01',
@LaborEffectiveDate nvarchar(10) = '2003-01-01',
@AreaTypeID int = 1,
@NailingParam int = -1,
@TapingParam int = -1
*/
RETURNS @table_variable TABLE (
IDintIDENTITY(1,1) PRIMARY KEY CLUSTERED,
StepIDdecimal (18,1)NOT NULL ,
JobMasterIDintNOT NULL ,
MixHeaderIDintNOT NULL ,
BidSubtypeIDintNOT NULL ,
WorkTypeIDintNOT NULL ,
UnitNamenvarchar (64)NOT NULL ,
UnitQuantityintNOT NULL ,
ItemDescriptionnvarchar (256)NOT NULL ,
ItemQuantitydecimal(18, 4)NOT NULL ,
ScaleValuedecimal(18, 4)NOT NULL ,
ExtendedPricedecimal (18,4)NOT NULL ,
IsVisiblebitNULL ,
WSQtyAdjdecimal(18,4)NULL)
AS
BEGIN
/*
/////////////////////////////////////////////////////////////////////////////////////////////

MATERIAL
SUBTYPE 1

/////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -1
WALLBOARD
ALL MATERIAL THAT HAS A MATERIAL CATEGORY OF 1 (WALLBOARD)
NOTE: THIS WILL ALSO GIVE YOU THE TOTAL SQUARE FEET TO BE USED IN
LATER CALCULATIONS
*/
INSERT INTO @table_variable
SELECT - 1 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 1 AS BidSubTypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity *
(FloorPlanLineItem.Quantity * Dimension.Area)) / 1000 AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity *
(FloorPlanLineItem.Quantity * Dimension.Area)) / 1000 *
MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,
MaterialScale.Price
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 1 AS StepID, MixHeader.JobMasterID,
MixHeader.MixHeaderID, 1 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area /
1000) AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Dimension.Area / 1000 *
MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectivedate)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +
'~' + ISNULL(FloorPlanHeader.Attribute3, ''), MixHeader.MixHeaderID,
MixHeader.JobMasterID,
MixLineItem.FloorPlanQuantity,
MaterialScale.Price
HAVING (MixHeader.MixHeaderID = @MixHeaderID)

/*
STEP -2
STOCKING
Scale * Total Wallboard sq ft
*/
INSERT INTO @table_variable
SELECT -2, JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID,
UnitName, UnitQuantity, N'STOCKING', SUM(ItemQuantity),
dbo.BidContract_GetMaterialScaleValue(830, @AreaTypeID,
@MaterialEffectiveDate), SUM(ItemQuantity) *
dbo.BidContract_GetMaterialScaleValue(830, @AreaTypeID,
@MaterialEffectiveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
/*
/////////////////////////////////////////////////////////////////////////////////////////////

MISC MATERIAL
SUBTYPE 2

/////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
STEP -3
NAILS AND SCREWS
Scale * Total Wallboard sq ft
1 box covers 4000 sq ft of wallboard
This makes sure we are dealing with whole boxes of nails:
ROUND(((TotalSquareFoot)/4000 + .4999), 0)

SELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(ItemQuantity)/4) + .4999,
0), dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),
ROUND((SUM(ItemQuantity)/4) + .4999, 0) *
dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),
IsVisible, WSQtyAdj
FROM bidunit
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
*/
INSERT INTO @table_variable
SELECT - 3, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'NAILS-SCREWS', ROUND((SUM(ItemQuantity)/4), 0),
dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),
ROUND((SUM(ItemQuantity)/4), 0) *
dbo.BidContract_GetMaterialScaleValue(829, 1, '2003-01-01'),
IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 1
GROUP BY JobMasterID, MixHeaderID, BidSubtypeID, WorkTypeID, UnitName,
UnitQuantity, IsVisible, WSQtyAdj
/*
MUD
Step -4
Select just a subset of the already inserted
data to give us a distinct list of UNITS to pass to the MUD function
NOTE: this type of select will be used a couple of more times, always
use StepID = -2 (STOCKING)
@THE_VALUE = isnull(@WALLBOARD/250,0) + isnull(@FIRETAPING/500,0) +
isnull(@METAL/125,0) + isnull(@CEIL_SQ_FT/900,0)
*/
INSERT INTO @table_variable
SELECT - 4, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'MUD', dbo.BidContract_GetMudValue(@MixHeaderID,
UnitName), dbo.BidContract_GetMaterialScaleValue(828, @AreaTypeID,
@MaterialEffectiveDate), dbo.BidContract_GetMudValue(@MixHeaderID,
UnitName) * dbo.BidContract_GetMaterialScaleValue(828, @AreaTypeID,
@MaterialEffectiveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 2
/*
TAPE
Step -5
ROUND(SUM(ItemQuantity)/1100, 0)
*/
INSERT INTO @table_variable
SELECT - 5, JobMasterID, MixHeaderID, 2, WorkTypeID, UnitName,
UnitQuantity, N'TAPE', dbo.BidContract_GetTapeValue(@MixHeaderID,
UnitName), dbo.BidContract_GetMaterialScaleValue(832, @AreaTypeID,
@MaterialEffectiveDate), dbo.BidContract_GetTapeValue(@MixHeaderID,
UnitName) * dbo.BidContract_GetMaterialScaleValue(832, @AreaTypeID,
@MaterialEffectiveDate), IsVisible, WSQtyAdj
FROM @table_variable
WHERE StepID = - 2
/*
METAL
Step -6
SUM(Z395*1.1)/1000
*/
INSERT INTO @table_variable
SELECT - 6 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtype, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0) * 1.1) / 1000
AS ItemQuantity, MaterialScale.Price AS
ScaleValue, (ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0) * 1.1) / 1000
* MaterialScale.Price AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 2) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 2) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate)
GROUP BY MixBuilding.MixBuildingDescription,
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,

MaterialItemMaster.MaterialItemMasterDescription, MaterialScale.Price
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 6 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, (FloorPlanLineItem.Quantity * 1.1) / 1000 AS
ItemQuantity, MaterialScale.Price AS ScaleValue,
(FloorPlanLineItem.Quantity * 1.1) / 1000 *
MaterialScale.Price AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 2) AND
(MaterialScale.AreaTypeID = @AreaTypeID) AND
(MaterialScale.EffectiveDate = @MaterialEffectiveDate)
GROUP BY FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +
'~' + ISNULL(FloorPlanHeader.Attribute3, ''),
MaterialItemMaster.MaterialItemMasterDescription,
MaterialScale.Price, FloorPlanLineItem.Quantity,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
/*
MISC MATERIAL
Step -7
*/
INSERT INTO @table_variable
SELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtype,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0)
AS ItemQuantity, MaterialScale.Price AS
ScaleValue, ISNULL(SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity), 0)
* MaterialScale.Price AS ExtendedPrice, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NOT NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2))) OR
(MixLineItem.MixHeaderID = @MixHeaderID) AND
(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2)))
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity,
MaterialScale.Price, FloorPlanLineItem.WorkTypeID
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidMiscMaterialExemptionListDescription
FROM
clsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID
<> 3)
UNION ALL
SELECT - 7 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'') + '~' + ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, FloorPlanLineItem.Quantity AS ItemQuantity,
MaterialScale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity *
MaterialScale.Price) AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
MaterialScale ON
MaterialItemMaster.MaterialItemMasterID =
MaterialScale.MaterialItemMasterID LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND
(MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelID IS
NULL) AND
(NOT (MaterialItemMaster.MaterialCategoryID IN
(1, 2)))
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'')
+ '~' + ISNULL(FloorPlanHeader.Attribute2, '') +
'~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MaterialScale.Price,
FloorPlanLineItem.WorkTypeID, MixLineItem.FloorPlanQuantity,
FloorPlanLineItem.Quantity
HAVING (NOT (MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidMiscMaterialExemptionListDescription
FROM
clsBidMiscMaterialExemptionList))) AND (FloorPlanLineItem.WorkTypeID
<> 3)
/*
COUNT OF LIVING UNITS
Step -8

****HOUSE***
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
N'SUNDRIES' AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity) AS
ItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate)
AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity
* dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FloorPlanHeader.IsLivingUnit =
'1')
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
****HOUSE***
*/
INSERT INTO @table_variable
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'SUNDRIES' AS
ItemDescription,
SUM(MixLineItem.FloorPlanQuantity) AS
ItemQuantity, dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate)
AS ScaleValue, SUM(MixLineItem.FloorPlanQuantity
* dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FloorPlanHeader.IsLivingUnit
= '1') OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL)
GROUP BY FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingDescription, MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 8 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
N'SUNDRIES' AS ItemDescription,
1 AS ItemQuantity,
dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate)
AS ScaleValue, SUM(1 *
dbo.BidContract_GetMaterialScaleValue(843, @AreaTypeID,
@MaterialEffectiveDate))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND (FloorPlanHeader.IsLivingUnit =
'1')
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
/*
FIRE HAULT - CHECK FOR FIREPROOFING
Step -9
*/
IF EXISTS (
SELECT 'true' AS Expr1
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID
WHERE (MixLineItem.MixHeaderID = @MixHeaderID) AND
(MaterialItemMaster.MaterialItemMasterDescription = N'FIRETAPING') AND
(JobMaster.ProjectTypeID <> 1))
BEGIN
INSERT INTO @table_variable
SELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidsubtypeID, 0 AS WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity, N'FIRE HAULT' AS
ItemDescription,
ISNULL(SUM(MixBuilding.MixBuildingQuantity *
MixLineItem.FloorPlanQuantity) / 2, 0) AS ItemQuantity,
dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID, @MaterialEffectiveDate) AS ScaleValue,
ISNULL(SUM(MixBuilding.MixBuildingQuantity *
MixLineItem.FloorPlanQuantity) / 2, 0) *
dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID, @MaterialEffectiveDate) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanHeader INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND (FloorPlanHeader.IsLivingUnit
= '1') OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL)
GROUP BY MixBuilding.MixBuildingDescription,
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 9 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 2 AS BidSubtypeID, 0 AS WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity, N'FIRE
HAULT' AS ItemDescription,
ISNULL(SUM(MixLineItem.FloorPlanQuantity) / 2,
0) AS ItemQuantity, dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID,
@MaterialEffectiveDate) AS ScaleValue,
ISNULL(SUM(MixLineItem.FloorPlanQuantity) / 2, 0) *
dbo.BidContract_GetMaterialScaleValue(579,
@AreaTypeID, @MaterialEffectiveDate) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID =
MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID
WHERE (MixLineItem.MixBuildingID IS NULL) AND (MixLineItem.MixLevelID
IS NULL) AND (FloorPlanHeader.IsLivingUnit = '1')
GROUP BY FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
FloorPlanHeader.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
END
/*
//////////////////////////////////////////////////////////////////////////////////////////////////

NAILING LABOR
SUBTYPE 3

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

INSERT INTO @table_variable
SELECT - 10.1 AS StepID, NailingLabor.JobMasterID,
NailingLabor.MixHeaderID, 3 AS BidSubtypeID, NailingLabor.WorkTypeID,
NailingLabor.UnitName,
NailingLabor.UnitQuantity, RTRIM(NailingLabor.ItemDescription) + N' '
+ dbo.Height.HeightDescription AS ItemDescription,
NailingLabor.ItemQuantity, Scale.Price +
dbo.Height.NailingRateGarage +
NailingLabor.ProductionNailingLaborIncrease AS ScaleValue,
NailingLabor.ItemQuantity * (Scale.Price +
dbo.Height.NailingRateGarage +
NailingLabor.ProductionNailingLaborIncrease) AS ExtendedPrice, 0 AS
IsVisible,
0 AS WSQtyAdj
FROM dbo.BidContract_NailingLabor_Wallboard_Production_ Garage(@MixHeaderID)
NailingLabor INNER JOIN
dbo.Height ON NailingLabor.HeightID =
dbo.Height.HeightID INNER JOIN
dbo.BidContract_NailingLabor_Scale() Scale ON
NailingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.AreaTypeID = @AreaTypeID) AND (Scale.EffectiveDate =
@LaborEffectiveDate)
ORDER BY NailingLabor.WorkTypeID DESC,
RTRIM(NailingLabor.ItemDescription) + N' ' +
dbo.Height.HeightDescription

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

--TODO: add garage

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

--TODO: add garage

END
/*
'ALL OTHER MATERIAL
Step -11
*/
INSERT INTO @table_variable
SELECT - 11 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)
AS ItemQuantity,
BidContract_NailingLabor_Scale.Price AS ScaleValue,
SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity * BidContract_NailingLabor_Scale.Price) AS
ExtendedPrice, 0 AS IsVisible,
0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
BidContract_NailingLabor_Scale()
BidContract_NailingLabor_Scale ON
MaterialItemMaster.MaterialItemMasterDescription
= BidContract_NailingLabor_Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.MaterialItemMasterID <> 606)
AND (MaterialItemMaster.Attribute2 = N'1') AND
(BidContract_NailingLabor_Scale.EffectiveDate =
@LaborEffectiveDate) AND (BidContract_NailingLabor_Scale.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.MaterialItemMasterID <> 606)
AND (MaterialItemMaster.Attribute2 = N'1') AND
(BidContract_NailingLabor_Scale.EffectiveDate =
@LaborEffectiveDate) AND (BidContract_NailingLabor_Scale.AreaTypeID =
@AreaTypeID)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanLineItem.WorkTypeID, MixBuilding.MixBuildingDescription,
MixHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, BidContract_NailingLabor_Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 2) AND
(MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 11 AS StepID, MixHeader.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, SUM(MixLineItem.FloorPlanQuantity) AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID <> 1) AND
(MaterialItemMaster.MaterialItemMasterID <> 606)
AND (MaterialItemMaster.Attribute2 = N'1') AND (Scale.EffectiveDate =
@LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
GROUP BY MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
MixHeader.JobMasterID, MixLineItem.MixHeaderID, Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 2) AND
(MixLineItem.MixHeaderID = @MixHeaderID)
/*
'PRELIM WALLBOARD LESS SPECIAL
Step -12
*/
INSERT INTO @table_variable
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.UnitQuantity * Scale.Price AS ExtendedPrice,
VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 12 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,

MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,

MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription AS ItemDescription,

SUM(MixLineItem.FloorPlanQuantity * (FloorPlanLineItem.Quantity *
Dimension.Area)) AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOIN
Dimension ON
FloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOIN
JobMaster ON
FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID =
JobMaster.JobMasterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1)
AND
(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))) OR

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

(MaterialItemMaster.Attribute2 = N'1') AND (NOT
(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList)))
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription, FloorPlanLineItem.WorkTypeID,

MixBuilding.MixBuildingDescription, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, MixBuilding.MixBuildingQuantity
HAVING (FloorPlanLineItem.WorkTypeID = 1)
AND (MixLineItem.MixHeaderID = @MixHeaderID)) VT INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
VT.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
UNION ALL
SELECT VT.StepID, VT.JobMasterID, VT.MixHeaderID, VT.BidSubtypeID,
VT.WorkTypeID, VT.UnitName, VT.UnitQuantity, VT.ItemDescription,
VT.ItemQuantity,
Scale.Price, VT.UnitQuantity * Scale.Price AS
ExtendedPrice, VT.IsVisible, VT.WSQtyAdj
FROM (SELECT - 12 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName
+ '~' + ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' +
ISNULL(FloorPlanHeader.Attribute3, '') AS UnitName,
MixLineItem.FloorPlanQuantity AS UnitQuantity,

MaterialItemMaster.MaterialItemMasterDescription + N' ' +
WorkLocation.WorkLocationDescription AS ItemDescription,

SUM(FloorPlanLineItem.Quantity * Dimension.Area) AS ItemQuantity, 0 AS
IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON
FloorPlanHeader.FloorPlanHeaderID = MixLineItem.FloorPlanHeaderID
INNER JOIN
MixHeader ON
MixLineItem.MixHeaderID = MixHeader.MixHeaderID INNER JOIN
Dimension ON
FloorPlanLineItem.DimensionID = Dimension.DimensionID INNER JOIN
JobMaster ON
FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID =
JobMaster.JobMasterID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON
FloorPlanLineItem.WorkLocationID = WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NULL)
AND (MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND

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

(MaterialItemMaster.MaterialItemMasterDescription IN
(SELECT
clsBidNailingLaborExemptionListDescription
FROM
clsBidNailingLaborExemptionList))
UNION ALL
SELECT - 13 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 3 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity * Dimension.Area)
AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * Dimension.Area * Scale.Price) AS
ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
Dimension ON FloorPlanLineItem.DimensionID =
Dimension.DimensionID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID = JobMaster.JobMasterID AND
MixHeader.JobMasterID = JobMaster.JobMasterID INNER JOIN
BidContract_NailingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID LEFT OUTER JOIN
WorkLocation ON FloorPlanLineItem.WorkLocationID =
WorkLocation.WorkLocationID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialCategoryID = 1) AND
(MaterialItemMaster.Attribute2 = N'1') AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY FloorPlanLineItem.WorkTypeID,
MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
JobMaster.JobMasterID, MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity, Scale.Price
HAVING (FloorPlanLineItem.WorkTypeID = 1) AND
(MixLineItem.MixHeaderID = @MixHeaderID) AND

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

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

TAPING LABOR
SUBTYPE 4

/////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
/*
WALLLBOARD W/ HEIGHT
Step - 16.x
*/
IF (@TapingParam = -1) --Progressive
BEGIN
INSERT INTO @table_variable
SELECT - 16.1 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, Scale.Price +
TapingLabor.TapingLaborIncrease + Height.TapingRate AS ScaleValue,
TapingLabor.ItemQuantity * (Scale.Price +
TapingLabor.TapingLaborIncrease + Height.TapingRate) AS ExtendedPrice,
TapingLabor.IsVisible,
TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@MixHeaderID)
TapingLabor INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
TapingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
END
IF (@TapingParam = -2) --Nonprogressive
BEGIN
INSERT INTO @table_variable
SELECT - 16.2 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, Scale.Price +
TapingLabor.TapingLaborIncrease AS ScaleValue,
TapingLabor.ItemQuantity * (Scale.Price +
TapingLabor.TapingLaborIncrease) AS ExtendedPrice,
TapingLabor.IsVisible, TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@MixHeaderID)
TapingLabor INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
TapingLabor.ItemDescription = Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
END
IF (@TapingParam = -3) --Straight
BEGIN
INSERT INTO @table_variable
SELECT - 16.3 AS StepID, TapingLabor.JobMasterID,
TapingLabor.MixHeaderID, TapingLabor.BidSubtypeID,
TapingLabor.WorkTypeID, TapingLabor.UnitName,
TapingLabor.UnitQuantity,
CASE TapingLabor.ItemDescription WHEN
'WALLBOARD' THEN Height.HeightDescription ELSE
TapingLabor.ItemDescription + ' ' + Height.HeightDescription
END AS ItemDescription,
TapingLabor.ItemQuantity, TapingLabor.TapingLaborStraight AS
ScaleValue,
TapingLabor.ItemQuantity *
TapingLabor.TapingLaborStraight AS ExtendedPrice,
TapingLabor.IsVisible, TapingLabor.WSQtyAdj
FROM BidContract_TapingLabor_Wallboard(@MixHeaderID) TapingLabor
INNER JOIN
Height ON TapingLabor.HeightID = Height.HeightID
INNER JOIN
BidContract_TapingLabor_Scale() Scale ON TapingLabor.ItemDescription
= Scale.ItemDescription
WHERE (Scale.EffectiveDate = @LaborEffectiveDate) AND
(Scale.AreaTypeID = @AreaTypeID)
END
/*
METAL AND MISC ITEMS
StepID -17
*/
INSERT INTO @table_variable
SELECT - 17 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(MixLineItem.FloorPlanQuantity *
FloorPlanLineItem.Quantity)
AS ItemQuantity, Scale.Price AS ScaleValue,
SUM((MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)
* (Scale.Price + JobMaster.TapingLaborIncrease))
AS ExtendedPrice, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID =
MixHeader.MixHeaderID INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID =
MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NOT NULL) AND
(MaterialItemMaster.MaterialItemMasterID IN (605, 795,
589, 584, 586, 583, 585, 587, 582, 588)) AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID) OR
(MixLineItem.MixBuildingID IS NOT NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialItemMasterID IN (605, 795, 589,
584, 586, 583, 585, 587, 582, 588)) AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY JobMaster.JobMasterID,
MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity, Scale.Price,
FloorPlanLineItem.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
UNION ALL
SELECT - 17 AS StepID, JobMaster.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID,
FloorPlanLineItem.WorkTypeID,
FloorPlanHeader.PlanName + '~' +
ISNULL(FloorPlanHeader.Attribute1, '') + '~' +
ISNULL(FloorPlanHeader.Attribute2, '')
+ '~' + ISNULL(FloorPlanHeader.Attribute3, '')
AS UnitName, MixLineItem.FloorPlanQuantity AS UnitQuantity,
MaterialItemMaster.MaterialItemMasterDescription
AS ItemDescription, SUM(FloorPlanLineItem.Quantity) AS ItemQuantity,
Scale.Price AS ScaleValue,
SUM(FloorPlanLineItem.Quantity * (Scale.Price +
JobMaster.TapingLaborIncrease)) AS ExtendedPrice, 0 AS IsVisible, 0 AS
WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID
= MixLineItem.FloorPlanHeaderID INNER JOIN
MixHeader ON MixLineItem.MixHeaderID = MixHeader.MixHeaderID
INNER JOIN
JobMaster ON FloorPlanHeader.JobMasterID =
JobMaster.JobMasterID INNER JOIN
BidContract_TapingLabor_Scale() Scale ON
MaterialItemMaster.MaterialItemMasterDescription =
Scale.ItemDescription LEFT OUTER JOIN
MixBuilding ON MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NULL) AND
(MixLineItem.MixLevelID IS NULL) AND
(MaterialItemMaster.MaterialItemMasterID IN (605, 795, 589, 584,
586, 583, 585, 587, 582, 588)) AND
(Scale.EffectiveDate = @LaborEffectiveDate) AND (Scale.AreaTypeID =
@AreaTypeID)
GROUP BY JobMaster.JobMasterID,
MaterialItemMaster.MaterialItemMasterDescription,
FloorPlanHeader.PlanName + '~' + ISNULL(FloorPlanHeader.Attribute1,
'') + '~' + ISNULL(FloorPlanHeader.Attribute2,
'') + '~' + ISNULL(FloorPlanHeader.Attribute3, ''),
MixLineItem.MixHeaderID,
MixLineItem.FloorPlanQuantity, Scale.Price,
FloorPlanLineItem.WorkTypeID
HAVING (MixLineItem.MixHeaderID = @MixHeaderID)
/*
BRACKETS
StepID -18
*/
INSERT INTO @table_variable
SELECT Brackets.StepID, Brackets.JobMasterID,
Brackets.MixHeaderID, Brackets.BidSubtypeID, Brackets.WorkTypeID,
Brackets.UnitName,
Brackets.UnitQuantity, Brackets.ItemDescription,
Brackets.ItemQuantity, Scale.Price AS Price, Brackets.ItemQuantity *
Scale.Price AS ExtendedPrice,
Brackets.IsVisible, Brackets.WSQtyAdj
FROM (SELECT - 18 AS StepID, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID, 4 AS BidSubtypeID, 0 AS WorkTypeID,

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

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

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

PICKUP
SUBTYPE 5

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

MixBuilding.MixBuildingDescription AS UnitName,
MixBuilding.MixBuildingQuantity AS UnitQuantity,

MaterialItemMaster.MaterialItemMasterDescription AS ItemDescription,
SUM(MixLineItem.FloorPlanQuantity * FloorPlanLineItem.Quantity)
AS ItemQuantity, 0 AS IsVisible, 0 AS WSQtyAdj
FROM FloorPlanLineItem INNER JOIN
MaterialItemMaster ON
FloorPlanLineItem.MaterialItemMasterID =
MaterialItemMaster.MaterialItemMasterID INNER JOIN
FloorPlanHeader ON
FloorPlanLineItem.FloorPlanHeaderID =
FloorPlanHeader.FloorPlanHeaderID INNER JOIN
MixLineItem ON FloorPlanHeader.FloorPlanHeaderID =
MixLineItem.FloorPlanHeaderID LEFT OUTER JOIN
MixBuilding ON
MixLineItem.MixBuildingID = MixBuilding.MixBuildingID
WHERE (MixLineItem.MixBuildingID IS NOT
NULL) AND (MixLineItem.MixLevelID IS NOT NULL) AND

(MaterialItemMaster.MaterialItemMasterID = 802) OR

(MixLineItem.MixBuildingID IS NOT NULL) AND (MixLineItem.MixLevelID IS
NULL) AND (MaterialItemMaster.MaterialItemMasterID = 802)
GROUP BY
MaterialItemMaster.MaterialItemMasterDescription,
MixBuilding.MixBuildingDescription, FloorPlanHeader.JobMasterID,
MixLineItem.MixHeaderID,
MixBuilding.MixBuildingQuantity
HAVING (MixLineItem.MixHeaderID =
@MixHeaderID)) VT INNE

View 1 Replies


ADVERTISEMENT

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

Mar 31, 2004

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

View 2 Replies View Related

Multi-statement Table-valued Functions

May 25, 2004

Hello

I am trying to do the following:

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

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

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

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

Thanks for any help.

View 3 Replies View Related

Multi-statement Table-Valued Function

Oct 18, 2007

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.

View 7 Replies View Related

Multi Statement Table-valued UDF Without Declaring Columns?

May 6, 2007

Hey
I have created a multi-statement
table valued function

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



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

View 2 Replies View Related

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With Sys?

Nov 2, 2006

Hi all,

I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:

Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.

I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?

Thanks, Jos

Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/

PRINT @@VERSION
go

PRINT 'Scalar function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go

DROP FUNCTION sys_test
go

PRINT ''
go


PRINT 'In-line table-valued function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go

DROP FUNCTION sys_test
go

PRINT ''
go


PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go

CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN

INSERT INTO @t VALUES (1)

RETURN

END
go

DROP FUNCTION sys_test
go

PRINT ''
go

/*
----------------------------------------------------------------------------------------------------
*/

And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Scalar function with name "sys_" creates ok...

In-line table-valued function with name "sys_" creates ok...

Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.

View 3 Replies View Related

Problems Building Sql Statement

Nov 15, 2007

Hello,
I'm having problems building an sql stament that joins a few tables. I can seem to get my head around the structure!
I have to try and link up four different tables to try and get my result.
Here are the 4 table structures...
Web_Users----------------User_IDName
Tags_Table-----------------Tags_IDUser_IDGroup_IDTitle
Created_Groups-----------------------Group_IDGroup_Name
Tags_To_Groups------------------------Group_Link_IDGroup_IDTag_ID 
Basically, this database, has four tables; One table (Web_Users) that contains a users name, and assigns a unique ID (User_ID), another table that stores a users tags they have created, and also links it to a group_ID. The created_groups table, contains group names and assigns a unique id also. And the last table, Tags_To_Groups, links tags to groups.
So this is what I'm trying to do...
I'm trying to get the Group_name field from Created_Groups table, of a tag , that belongs to a certain user. If sounds easy when I say it like that, but I've been inner joining tables all night and failing every time.
 Does this make sense? Can anyone help?
Thank you

View 3 Replies View Related

Building A Sql Statement In A Stored Proc

May 12, 2008

Hi All,
What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign.
I've tried various ways to create this query and it always truncated around the point of the % sign.
Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings!
Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc.
Thanks for any help on this!
regrads
davej
@var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE'
@var3 = ''%london%' OR item_Text LIKE '%solicitor%''
@var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'
@varfull = @var1+@var2+@var3+@var4
 

View 11 Replies View Related

Building A Dynamic Sql Statement Into Stored Procedure

Apr 19, 2008

Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]
( 
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID  = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId  = @regionID '
exec sp_executesql @StaticStr
)

View 10 Replies View Related

Error In Building SQL Query Within LIKE Statement For TableAdapter In Design Section

May 31, 2006

ASP.net 2.0 (VB), SQL Server 2005:While creating a new TableAdapter in design section, I'm using the query builder and trying to write a query within "LIKE" statement as below -Example1: SELECT * FROM table WHERE field LIKE @'%TextBoxData%'Example2: SELECT * FROM table WHERE field LIKE '%@TextBoxData%'
but these query doesn't work...error in building query...any clue to make it work? If I remove "@" sure the query will work with normal but '%TextBoxData%' will become a hardcoding value...this is not I want...I want make the TextBoxData become a flexible value depend on the data what I enter in my text box like 'abc,123' not like 'TextBoxData'...
I know normally it supposed to be like:
 "SELECT * FROM table WHERE field LIKE '%" & TextBoxData.Text & "%'"
It can work when in Code Section, but not at this time...because now i'm trying to made it with "Query Builder" for TableAdapter " in Design Section...hmm did you get what I mean? Sorry for my bad english
Thanks in advance

View 3 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

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

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

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

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

View 2 Replies View Related

Building A Table From SQL Query

Apr 21, 2006

I am hoping someone can point me in the right direction with this.I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data.  Some of the columns may not have values in them, and so I dont want to build a table row for it.  I also need to use the column name as the table header.  As an example:==============================Column Name    || Column Value-----------------||-----------------Column Name    || Column Value
-----------------||-----------------
I hope I have explained myself properly.  Any help would be greatly appreciated.

View 4 Replies View Related

Building A Parts Table With Multiple Catagories

Mar 14, 2008

I am trying to figure out the best way to build a table that will hold information about our inventory of parts. We have several issues that I have been struggeling with trying to make this work. First capturing the basic part information is a no brainer, where I am having some questions is with how to assign categories, subcategories for each of these parts. The challenge comes because each part can belong to multiple categories and subcategories. For example we have 5 main items that would have categories, subcategories, parts. For example:

Item: Trailer
Model: model1, model2, model3
Category: Chassis
Subcategory: Upper frame, lower frame, electrical
Part: could be any thing that falls under one of the subcategories

Item: Trailer
Model: model1, model2, model3
Category: Gen set
Subcategory: engine, power, electrical
Part: could be any thing that falls under one of the subcategories

repeat...

My main items are trailer, shelter, power, heat, lights. Under the trailer and shelter items parts can belong all models or just a couple or just one model. My thought was to create a table for each item, ie: TrailerModels, ShelterModels which would have 100 plus columns, 1 column for each model, using a bit datatype to show which parts could belong to which model. This however seems to be quite a bit of work, because in order to use the models to thin down lists when searching for an item it would have query this table as well as the category and subcategory tables.

Anyone have some feedback about how I should approach this? I don't know if I was able to communicate the specifics of this very well but I can clear up details if anyone wants to throw some feedback my way.

Thanks!





yAy

View 3 Replies View Related

Multi Parameter AND Statement

May 27, 2008

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

View 2 Replies View Related

SQL Server 2012 :: Building Self-referencing Hierarchical Table

May 21, 2014

An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.

I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.

It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.

View 9 Replies View Related

SQL Server 2012 :: In Trigger - Building Dynamic Table With Inserted Data

Nov 4, 2015

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)

Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

View 2 Replies View Related

Select Statement Using Multi-list Box Values For WHERE IN SQL Clause

Jan 11, 2007

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!

View 4 Replies View Related

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

May 21, 2015

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

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

View 13 Replies View Related

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related

Multi-Table Update??

May 26, 2004

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

What is the alternatives??

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

View 5 Replies View Related

Multi-Table Join Help

Feb 7, 2002

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

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

from ca

join c1 on ca.accountno = c1.accountno

join ch on ca.accountno = ch.accountno

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

View 1 Replies View Related

Multi Table Lookups

Sep 12, 2014

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

field1=name field2=age field3=country

Joe,50,1

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

field1=id field2=description

1,USA
2,France
3,Germany

I want to do a lookup where it returns:

Joe,50,USA

View 1 Replies View Related

Multi Table Source

Jul 9, 2007

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



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



SELECT * FROM Table1
WHERE (Key = ?)



SELECT * FROM Table2
WHERE (Key = ?)



SELECT * FROM Table3
WHERE (Key = ?)



SELECT * FROM Table4
WHERE (Key = ?)



SELECT * FROM Table5
WHERE (Key = ?)



TIA



Ian

View 4 Replies View Related

Multi-table Queries For M:N Situations

May 29, 2006

Hi friends,

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

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

Cm

View 3 Replies View Related

MULTI-Table Update Queries

Jul 23, 2005

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

View 2 Replies View Related

Multi-table UDF Not Returning All Rows

Sep 7, 2005

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

View 2 Replies View Related

RDA - Multi Table Error For Tracking

Apr 18, 2007



Hello,



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

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

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



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



Thanks in advance!

View 6 Replies View Related

Adding Multi Records To An SQL Table

May 7, 2007

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

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

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

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

Thanks.

View 2 Replies View Related

Multi-table Join Problem

Jan 9, 2008

Hello All,

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

Any help is much appreciated.

Thanks,

View 7 Replies View Related

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

Sep 23, 2014

Disaster Recovery Options based on the following criteria.

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

What I have looked into is:

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

View 1 Replies View Related

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

Aug 17, 2015

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

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

View 9 Replies View Related

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

Feb 4, 2008

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



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




TNX

View 2 Replies View Related

Multi Relational Table Design Question

Dec 10, 2006

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



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



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

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

View 3 Replies View Related







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