Enterprise Software Solutions 

  Jump to: DW University, Employment

SQL Server Notes

SELECT (
CASE
    When Manufacture_Date < '04/16/2003' 
        Then 'Y' 
    Else 'N'
End) As Goodwill
FROM TableName

--'***********************************************************************
--' Comments : Loads a Data Mart from the warehouse (forecasting)
--' Parameters : NONE
--' Returns : nothing
--' Created : Enterprise Software Solutions [10/11/2002]
--' Modified :
--'***********************************************************************
CREATE Procedure LoadDataMart
AS

SET NOCOUNT OFF

DECLARE @ReportMonth SMALLDATETIME,
@Month_For SMALLDATETIME,
@Prod_ID INT,
@ProjectedInventory FLOAT(8),
@Forecast FLOAT(8),
@ProductionPlans FLOAT(8),
@ProjectedInventoryLastMonth FLOAT(8),
@NewProjectedInventory FLOAT(8),
@AverageForecastNextThreeMonths FLOAT(8)


--For the marts, only the current month "as of" data will be loaded
SELECT @ReportMonth = MAX(Report_Month) FROM Fact_Monthly_Facts
SET @ReportMonth = CAST(STR(MONTH(@ReportMonth)) + '/01/' + STR(YEAR(@ReportMonth)) AS 
SMALLDATETIME)

--clear mart table
TRUNCATE TABLE Mart_WebReport

--Build initial set of data (assume that every product has a forecast)
INSERT INTO Mart_WebReport (Prod_ID, Month_For, Forecast, ProductionPlans)
SELECT f.Prod_ID, f.Month_For, f.Forecast, ISNULL(p.ProductionPlans,0) as 
ProductionPlans
FROM
--Load Forecast
(
SELECT Prod_ID,
Month_For, 
SUM(Units) as Forecast
FROM Fact_Monthly_Facts
WHERE Type_ID IN(3)
AND Report_Month = @ReportMonth
GROUP BY Prod_ID,
Month_For
) f
LEFT OUTER JOIN 
--Load Production Plans
(
SELECT Prod_ID,
Month_For, 
SUM(Units) as ProductionPlans
FROM Fact_Monthly_Facts
WHERE Type_ID IN(6,7,8,9,10,11,20) 
AND Report_Month = @ReportMonth
GROUP BY Prod_ID,
Month_For
) p
ON (f.Prod_ID = p.Prod_ID AND f.Month_For = p.Month_For)
--where f.prod_id between 166 and 266

--Load ProjectedInvertoy for the current month
UPDATE Mart_WebReport
SET ProjectedInventory = ISNULL(mi.ProjectedInventory,0) - mo.Forecast + mo.ProductionPlans
FROM 
(
SELECT f.Prod_ID,
f.Month_For, 
SUM(
CASE 
WHEN f.Type_ID=14 THEN +f.Units
WHEN f.Type_ID=19 THEN +f.Units
WHEN f.Type_ID=17 THEN -f.Units
END) as ProjectedInventory
FROM Mart_WebReport m
INNER JOIN Fact_Monthly_Facts f ON (m.Prod_ID = f.Prod_ID AND m.Month_For = 
f.Month_For)
WHERE f.Type_ID IN(14,19,17)
AND f.Report_Month = @ReportMonth
AND f.Month_For = @ReportMonth
GROUP BY f.Prod_ID,
f.Month_For
) mi
INNER JOIN Mart_WebReport mo
ON (mo.Prod_ID = mi.Prod_ID AND mo.Month_For = mi.Month_For)


--***********************************
--Load ProjectedInvertoy for each month after the current month
--***********************************

--Begin cursor for the table
DECLARE OuterCursor CURSOR FOR 
SELECT DISTINCT Prod_ID 
FROM Mart_WebReport
ORDER BY Prod_ID
OPEN OuterCursor
FETCH OuterCursor INTO @Prod_ID
--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE MyCursor CURSOR FOR 
SELECT Month_For, Forecast, ProductionPlans, ProjectedInventory
FROM Mart_WebReport 
WHERE Prod_ID = @Prod_ID
AND Month_For <= DATEADD(Month,-3,(SELECT MAX(Month_For) FROM Mart_WebReport))
ORDER BY Month_For
OPEN MyCursor
--fetch twice because we do not update the current month
FETCH MyCursor INTO @Month_For, @Forecast, @ProductionPlans, @ProjectedInventory
--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
--Calculate average forcast for next 3 months
SELECT @AverageForecastNextThreeMonths = AVG(Forecast) 
FROM Mart_WebReport 
WHERE Prod_ID = @Prod_ID 
AND Month_For BETWEEN DATEADD(Month,+1,@Month_For) AND 
DATEADD(Month,+3,@Month_For)
--Calculate ProjectedInventory (unless its the first month)
IF @Month_For = (SELECT MIN(Month_For) FROM Mart_WebReport) BEGIN
--if first month, it's already calculated
SET @NewProjectedInventory = ISNULL(@ProjectedInventory,0)
END--IF 
ELSE BEGIN
SET @NewProjectedInventory = ISNULL(@ProjectedInventoryLastMonth,0) - 
@Forecast + @ProductionPlans
END--IF
--update record
UPDATE Mart_WebReport 
SET ProjectedInventory = @NewProjectedInventory,
AverageForecastNextThreeMonths = @AverageForecastNextThreeMonths,
WeeksOfSupply = @NewProjectedInventory / 
(@AverageForecastNextThreeMonths/4)
WHERE Prod_ID = @Prod_ID
AND Month_For = @Month_For
--Get next input record
SELECT @ProjectedInventoryLastMonth = @NewProjectedInventory
FETCH MyCursor INTO @Month_For, @Forecast, @ProductionPlans, 
@ProjectedInventory
END
--Cleanup inner Cursor
CLOSE MyCursor
DEALLOCATE MyCursor 
--get next product
FETCH OuterCursor INTO @Prod_ID
END
--Cleanup outer Cursor
CLOSE OuterCursor
DEALLOCATE OuterCursor 


--***********************************
--Load WeeksOfInventoryPerLot for current month only
--***********************************

UPDATE Mart_WebReport
SET WeeksOfInventoryPerLot=mi.WeeksOfInventoryPerLot
FROM 
(
SELECT f.Prod_ID, F.Month_For, (f.Units/fc.AverageForecast12Months) as 
WeeksOfInventoryPerLot
FROM Fact_Monthly_Facts f INNER JOIN
(
SELECT Prod_ID, 
(SELECT Min(Month_For) FROM Mart_WebReport) as Month_For, 
AVG(Forecast) as AverageForecast12Months
FROM Mart_WebReport
GROUP BY Prod_ID
) as fc
ON (f.Prod_ID = fc.Prod_ID AND f.Month_For = fc.Month_For)
WHERE f.Type_ID = 13
AND f.Report_Month = @ReportMonth
) mi
INNER JOIN Mart_WebReport mo
ON (mo.Prod_ID = mi.Prod_ID AND mo.Month_For = mi.Month_For)


GO

[ Home ] [ Up ] [ SQL Server FTP ]

Send mail to Webmaster@ESScorporation.com with questions or comments about this web site.
Copyright © 2004 Enterprise Software Solutions, Inc.
Last modified: July 29, 2004