October 20, 2014 at 1:31 am
I have this code:
/****** Object: StoredProcedure [dbo].[RPT_Human_Resources_Head_Count_Summary] Script Date: 10/20/2014 08:15:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RPT_Human_Resources_Head_Count_Summary]
(
@Report_Last_Month_DateDATETIME = NULL,
@Month_CountINTEGER = NULL
)
AS
CREATE TABLE #Summary_Table
(
Row_IDINTEGERIDENTITY(1,1),
Measure_Date_StartDATETIME,
Measure_Date_EndDATETIME,
Measure_GroupVARCHAR(200),
DivisionVARCHAR(100),
Functional_AreaVARCHAR(100),
Position_TypeVARCHAR(20),
Head_CountINTEGER
)
DECLARE
@Report_First_Month_DateDATETIME,
@Current_Query_DateDATETIME,
@Month_Start_DateDATETIME,
@Month_End_DateDATETIME,
@Check_DateDATETIME,
@Current_Month_Start_DateDATETIME
BEGIN TRY
/* IF NO @Report_Last_Month_Date WAS SPECIFIED, CHOOSE CURRENT MONTH'S END */
IF @Report_Last_Month_Date IS NULL
SELECT
@Report_Last_Month_Date = Month_End_Date
FROM
DWH_Warehouse.dbo.Calendar_Dimension AS cd
WHERE
cd.Calendar_Date = CONVERT(CHAR(10),GETDATE(),120)
SELECT
@Current_Month_Start_Date = Month_Start_Date
FROM
DWH_Warehouse.dbo.Calendar_Dimension AS cd
WHERE
cd.Calendar_Date = CONVERT(CHAR(10),GETDATE(),120)
/* IF NOT MONTH COUNT HAS BEEN SPECIFIED, DEFAULT TO 13 */
IF @Month_Count IS NULL
SET @Month_Count = 13
IF @Month_Count > 0
SET @Month_Count = @Month_Count * -1
/* CALCULATE THE FIRST MONTH'S START DATE */
SET @Report_First_Month_Date = DATEADD(MONTH,@Month_Count,@Report_Last_Month_Date)
SELECT
@Current_Query_Date = Month_Start_Date
FROM
DWH_Warehouse.dbo.Calendar_Dimension AS cd
WHERE
cd.Calendar_Date = @Report_First_Month_Date
/* LOOP THROUGH SPECIFIED AMOUNT OF MONTHS TO GET EVERY END OF MONTH HEAD COUNT */
WHILE @Current_Query_Date <= @Report_Last_Month_Date
BEGIN
/* HEADCOUNT AT END OF MONTH */
SET @Month_Start_Date = @Current_Query_Date
SET @Month_End_Date = DATEADD(DAY,-1, DATEADD(MONTH,1,@Month_Start_Date))
/* CHECK IF THE MONTH IS THE CURRENT MONTH, IF SO, SPECIFY THE END OF MONTH DATE AS TODAY */
IF @Month_Start_Date = @Current_Month_Start_Date
SET @Check_Date = GETDATE()
ELSE
SET @Check_Date = @Month_End_Date
INSERT INTO #Summary_Table
(
Measure_Date_Start,
Measure_Date_End,
Measure_Group,
Division,
Functional_Area,
Position_Type,
Head_Count
)
SELECT
@Month_Start_Date,
@Check_Date,
'Head Count End of Month',
Employee_Position_Functional_Area,
Employee_Position_Business_Unit,
Employee_Position_Type,
COUNT(1) AS Head_Count
FROM
DWH_Datamarts.dbo.Human_Resources_Position_Datamart AS src
WHERE
Employee_Position_Start_Date <= @Check_Date
AND COALESCE(Employee_Position_End_Date,GETDATE()) >= @Check_Date
AND @Check_Date <> GETDATE()
GROUP BY
Employee_Position_Functional_Area,
Employee_Position_Business_Unit,
Employee_Position_Type
ORDER BY
CASE WHEN Employee_Position_Functional_Area = 'Operations' THEN 1 ELSE 0 END DESC,
Employee_Position_Business_Unit
IF NOT EXISTS ( SELECT 1 FROM #Summary_Table WHERE Measure_Date_Start = @Month_Start_Date AND Division = 'Operations' AND Position_Type = 'Temp' AND @Check_Date <> GETDATE())
BEGIN
INSERT INTO #Summary_Table
(
Measure_Date_Start,
Measure_Date_End,
Measure_Group,
Division,
Functional_Area,
Position_Type,
Head_Count
)
VALUES
(
@Month_Start_Date,
@Check_Date,
'Head Count End of Month',
'Operations',
'Call Centre Operations',
'Temp',
0
)
END
/* INCREMENT MONTH FOR NEW LOOP */
SET @Current_Query_Date = DATEADD(MONTH, 1, @Current_Query_Date )
END /* LOOP END */
SELECT
Measure_Date_Start,
Measure_Date_End,
Measure_Group,
Division,
Functional_Area,
Position_Type,
Head_Count
FROM
#Summary_Table
WHERE Measure_Date_End <> GETDATE()
END TRY
BEGIN CATCH
EXECUTE dbo.Raise_Error @Error_Proc_ID = @@PROCID
END CATCH
I have Month Count parameter, if I choose 2 months, I should only get two months results, but so far I'm getting 3 months results, please help
October 20, 2014 at 2:13 am
We need table definitions as CREATE TABLE statements, some sample data in the form of INSERT INTO statements and expected results.
See the first article linked in my signature line to see how to post this question effectively.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply