ALTER PROCEDURE [dbo].[sproc_tblWkHrsVsBudgeted]
AS
-------------------------------------------------------------------------------
-- Procedure : sproc_tblWkHrsVsBudgeted
-- SubSystem : Labor Model
-- Description: Refer to HRISLaborModel.doc
--
-- * creates a table (tblWkHrsVsBudgeted)
of exactly 15 Weekly Hours vs Budgeted values
-- by week
start for each plant. tblWkHrsVsBudgeted
construction is divided into steps:
-- 1) An
empty table is created with 15 weeks per Plant.
-- 2)
PT/FT mixes from Retail Hours Rollup.xls are added. The PT/FT values are given
-- in PPD time base
and each incoming value writes to two adjacent weekstart
dates.
-- 3)
Actual PPD Labor Hours from the Activity Archive dbo.vStoreDlyActivityYTD
are added
-- by weekstart for each Plant.
-- 4)
Budgeted Labor Hours from LOCKED down Labor Hours forecasts are added.
-- 5) Now,
Actual vs Budgeted Variance calculations are possible
and they are added.
-- 6) The
Plant, Market, Division, Channel STARS are added.
-- 7) Call
Exec sproc_PivotWkHrsAct to convey the new values to
the Crystal Reports view.
--
-- History:
-- ---------
--
-- When
Who What
-- ----
--- ----
--
-- 15AUG07 J.
Martin v1.7 * Join Actuals from PPD archive using weekstarts
and not weekends.
-- 03AUG07 J.
Martin v1.6 * Use new vStoreDlyLaborForeYTD format.
-- 05JUL07 J.
Martin v1.5 * Call fnFifthWeek code inline.
-- 03JUL07 J.
Martin v1.4 * Get Actual
Labor Hours from the PPD Activity Archive
-- 26JUN07 J.
Martin v1.3 * Map NULLs to 0
for Cyrstal Reports DIV/MKT sums.
-- 12JUN07 J.
Martin v1.2 * Added CHSTAR
(Channel STAR) columns to tblWkHrsVsBudgeted.
-- 05JUN07 J.
Martin v1.1 * Added MKTSTAR
and DIVSTAR columns to tblWkHrsVsBudgeted.
-- 26MAY07 P.
Sutera v1.0
--
J. Martin
-------------------------------------------------------------------------------
BEGIN
SET NOCOUNT ON
SET DATEFIRST 7
-- STAR date ranges
DECLARE @Startdate datetime
DECLARE @Enddate datetime
-- sp_executesql
DECLARE @Command nvarchar(4000)
-- sp_showmessage
DECLARE @RowCnt
bigint
DECLARE @ProcID
int
DECLARE @Msg
nvarchar(400)
-------------------------------------------------------------------------------------
SET NOCOUNT ON
SET @ProcID
= @@PROCID
-------------------------------------------------------------------------------------
-- forecasted activity by week for each store.
-------------------------------------------------------------------------------------
SET @Msg
= 'Creating
tblWkHrsVsBudgeted...'
EXEC sp_showmessage
@objid = @ProcID, @msg = @Msg
-----------------------------------------------------------------------------
-- create a new tblWkHrsVsBudgeted
if object_id('tblWkHrsVsBudgeted') is
null BEGIN
CREATE TABLE
dbo.tblWkHrsVsBudgeted (
Plant nvarchar(10) NULL
,dtWeek datetime NULL
,intWeek int NULL
,intFore int NULL
,intAct int NULL
,intVar int NULL
,intVarPercent int NULL
,intFT int NULL
,intP int NULL
,intFTPercent int NULL
,intPTPercent int NULL
,STAR decimal(6,1) NULL
,MKTSTAR decimal(6,1) NULL
,DIVSTAR decimal(6,1) NULL
,CHSTAR decimal(6,1) NULL)
END
TRUNCATE TABLE dbo.tblWkHrsVsBudgeted
-----------------------------------------------------------------------------
-- load weekly plants (10 weeks) with no data
SET @Msg
= 'Loading
fifth week data...'
EXEC sp_showmessage
@objid = @ProcID, @msg = @Msg
-- loop counters
DECLARE @WEEKSTART DATETIME
DECLARE @dtStart DATETIME
DECLARE @WeekEnd DATETIME
DECLARE @SaveMonth INTEGER
DECLARE @IntWeeks INTEGER
DECLARE @j INTEGER
DECLARE @k INTEGER
DECLARE @m INTEGER
DECLARE @Weeks TABLE (WeekStart datetime,
intWeek integer)
------------------------------------------------------------------------------
-- init counters
SET @dtStart = DATEADD(m,-1,GetDate())
SET @intWeeks
= 10
-- First day of the month (minus time component)
SET @WeekEnd = convert(datetime,convert(int,convert(float,
(DATEADD(dd, -DATEPART(dd,
@dtStart ),
@dtStart ) + 1)
)))
-- Last day of the week (sets the month a week falls in)
SET @SaveMonth
= Month(DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 7),@WeekEnd))
-- First day of the first week
SET @WeekStart
= DATEADD(dd,-(DATEPART(dw,
@WeekEnd) - 1),@WeekEnd)
-- find the # of weeks in @intWeeks
started at the current month/year.
SET @j = 1 -- count weeks in @intWeeks.
SET @k = 0 -- count weeks in 1
month.
SET @m = 1 -- let client sorts
return the expected pivot order (use ORDER BY Plant, intWeek).
-----------------------------------------------------------------------------
-- If a month doesn’t contain 5 weeks naturally, add a
NULL week in its place.
WHILE (@j <= @intWeeks ) BEGIN
-- month changed
If
@SaveMonth <> Month(DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 7),@WeekEnd)) Begin
-- write
NULL
IF @k < 5 AND
@m < (@intWeeks + 1) BEGIN
INSERT @Weeks SELECT NULL, @m
SET @m = @m + 1
END
-- save the month
and reset the week counter
SET @SaveMonth = Month(DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 7),@WeekEnd))
SET @k = 0
End
-- write a naturally occurring
fith week.
IF
@m < (@intWeeks + 1) BEGIN
INSERT @Weeks SELECT @WeekStart, @m
END
-- advance counters
SET
@m = @m + 1
SET
@j = @j + 1
SET
@k = @k + 1
SET
@WeekEnd
= DATEADD(dd,7,@WeekEnd)
SET
@WeekStart = DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 1),@WeekEnd)
END
INSERT tblWkHrsVsBudgeted
SELECT PLANTS.Plant
,WEEKS.WeekStart
,WEEKS.intWeek
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM vLaborStoreDetail PLANTS,
@Weeks
WEEKS
-----------------------------------------------------------------------------
-- Add Labor Hours Acutals from
the Actitity Archive (PPD)
-- Add FT/PT mixes from Retail Hours Rollup.xls (PPE)
--SET DATEFIRST 1
SET @Msg
= 'Loading
Actuals and Headcounts...'
EXEC sp_showmessage
@objid = @ProcID, @msg = @Msg
-- second PPE week
UPDATE VARIANCE
SET
Plant = ACTUALS.Plant
,intFore
=
0
,intAct =
ISNULL(ACTARC.LaborHours,0)
,intVar =
0
,intVarPercent = 0
,intFT =
ISNULL(ACTUALS.Total_FT,0)
,intPT =
ISNULL(ACTUALS.Total_PT,0)
,intFTPercent =
CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN
0
ELSE
(ISNULL(ACTUALS.Total_FT,0) + .00) / ((ISNULL(ACTUALS.Total_FT,0) +
ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
,intPTPercent = CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN
0
ELSE (ISNULL(ACTUALS.Total_PT,0) + .00) /
((ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
FROM tblLookupHrsRollup
ACTUALS
JOIN tblWkHrsVsBudgeted
VARIANCE
ON VARIANCE.Plant =
ACTUALS.Plant
AND VARIANCE.dtWeek = DATEADD(dd, -6, ACTUALS.PPE)
-- get PPD Actuals from the
Activity Archive
JOIN (SELECT Plant
,Activitydate =
(DATEADD(dd,-(DATEPART(dw,
Activitydate) - 1),Activitydate))
,LaborHours =
SUM(ISNULL(LaborHours,0))
FROM vStoreDlyActivityYTD
GROUP BY Plant, (DATEADD(dd,-(DATEPART(dw, Activitydate) - 1),Activitydate))
HAVING SUM(ISNULL(LaborHours,0)) > 0) ACTARC
ON VARIANCE.Plant =
ACTARC.Plant
AND VARIANCE.dtWeek = ACTARC.ActivityDate
--first PPE week
UPDATE VARIANCE
SET
Plant = ACTUALS.Plant
,intFore
=
0
,intAct =
ISNULL(ACTARC.LaborHours,0)
,intVar =
0
,intVarPercent = 0
,intFT =
ISNULL(ACTUALS.Total_FT,0)
,intPT =
ISNULL(ACTUALS.Total_PT,0)
,intFTPercent = CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN
0
ELSE
(ISNULL(ACTUALS.Total_FT,0) + .00) / ((ISNULL(ACTUALS.Total_FT,0) +
ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
,intPTPercent = CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN
0
ELSE
(ISNULL(ACTUALS.Total_PT,0) + .00) /
((ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
FROM tblLookupHrsRollup
ACTUALS
JOIN tblWkHrsVsBudgeted
VARIANCE
ON VARIANCE.Plant =
ACTUALS.Plant
AND VARIANCE.dtWeek = DATEADD(dd, -13, ACTUALS.PPE)
-- get PPD Actuals from the
Activity Archive
JOIN (SELECT Plant
,Activitydate =
(DATEADD(dd,-(DATEPART(dw,
Activitydate) - 1),Activitydate))
,LaborHours =
SUM(ISNULL(LaborHours,0))
FROM vStoreDlyActivityYTD
GROUP BY Plant, (DATEADD(dd,-(DATEPART(dw, Activitydate) - 1),Activitydate))
HAVING SUM(ISNULL(LaborHours,0)) > 0) ACTARC
ON VARIANCE.Plant =
ACTARC.Plant
AND VARIANCE.dtWeek = ACTARC.ActivityDate
-----------------------------------------------------------------------------
-- load Budgeted Hours and Variance Percent from the
forecasted hours archive
SET @Msg
= 'Loading
Budgeted Hours...'
EXEC sp_showmessage
@objid = @ProcID, @msg = @Msg
UPDATE VARIANCE
SET intFore
=
ISNULL(FORE.Budgeted_Hours,0)
,intVar
=
ISNULL(FORE.Budgeted_Hours,0) -
ISNULL(VARIANCE.intAct,0)
,intVarPercent
= CASE ISNULL(FORE.Budgeted_Hours,0)
WHEN 0 THEN
0
ELSE ((ISNULL(FORE.Budgeted_Hours,0) - ISNULL(VARIANCE.intAct,0)) +.00) /
(ISNULL(FORE.Budgeted_Hours,