sql server (alternate):
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 (
ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StepID decimal (18,1) NOT NULL ,
JobMasterID int NOT NULL ,
MixHeaderID int NOT NULL ,
BidSubtypeID int NOT NULL ,
WorkTypeID int NOT NULL ,
UnitName nvarchar (64) NOT NULL ,
UnitQuantity int NOT NULL ,
ItemDescription nvarchar (256) NOT NULL ,
ItemQuantity decimal(18, 4) NOT NULL ,
ScaleValue decimal(18, 4) NOT NULL ,
ExtendedPrice decimal (18,4) NOT NULL ,
IsVisible bit NULL ,
WSQtyAdj decimal (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
