Recently I
was working on a project, in which I need to display cumulative monthly
information for all the years in the period starting from the current month to
the month for end of the period (if less that 12 months) or all the months (is
more than 12 months).
I searched
Internet, but I was not able to get any substantial help. So, I finally decided
to write it down myself:
--------------------------Variable
Declaration Start-----------------------------
DECLARE @in_website_id
int, @in_surgeon_id int, @in_customer_type_id int, @in_selection_criteria_id
int
DECLARE @vc_marketing_channel
varchar(100), @vc_campaign_id_sf
varchar(40)
DECLARE @dt_start
varchar(20), @dt_end varchar(20), @dt_start_compare varchar(20), @dt_end_compare varchar(20)
--------------------------Variable
Declaration
End-------------------------------
SET @in_website_id
= 6
SET @in_surgeon_id
= 0
SET @in_customer_type_id
= 0
SET @in_selection_criteria_id
= 0
SET @vc_marketing_channel
= 'Website-Affiliate-PY'
SET @vc_campaign_id_sf
= ''
SET @dt_start
= '11/01/2011'
SET @dt_end
= '04/30/2012'
SET @dt_start_compare
= '11/01/2011'
SET @dt_end_compare
= '04/30/2012'
--------------------------Stored
Procedure
Start---------------------------------
SELECT in_month_id
AS in_month, ISNULL(SUM(dc_estimated_net_roi), 0) AS dc_estimated_net_roi
INTO #temp_current
FROM dbo.cd_process_data
WHERE in_website_id
= @in_website_id
AND in_surgeon_id
= @in_surgeon_id
AND (((@vc_marketing_channel <>
'Email Marketing')
AND (vc_marketing_channel
= @vc_marketing_channel))
OR ((@vc_marketing_channel =
'Email Marketing')
AND (vc_marketing_channel
= @vc_marketing_channel
AND vc_campaign_id_sf
= @vc_campaign_id_sf)))
AND in_customer_type_id
= @in_customer_type_id
AND in_selection_criteria_id
= @in_selection_criteria_id
AND ((in_year_id =
YEAR(@dt_start) AND in_month_id >= MONTH(@dt_start))
OR ((in_year_id >
YEAR(@dt_start) AND in_year_id < YEAR(@dt_end)) AND (in_month_id >= 1 AND in_month_id <= 12))
OR ((in_year_id >
YEAR(@dt_start)) AND (in_year_id = YEAR(@dt_end)) AND (in_month_id >= 1 AND in_month_id <= MONTH(@dt_end)))
OR ((in_year_id =
YEAR(@dt_start)) AND (in_year_id = YEAR(@dt_end)) AND (in_month_id >= MONTH(@dt_start) AND in_month_id <= MONTH(@dt_end))))
GROUP BY
in_month_id
SELECT in_month_id
AS in_month, ISNULL(SUM(dc_estimated_net_roi), 0) AS dc_estimated_net_roi
INTO #temp_previous
FROM dbo.cd_process_data
WHERE in_website_id
= @in_website_id
AND in_surgeon_id
= @in_surgeon_id
AND (((@vc_marketing_channel <>
'Email Marketing')
AND (vc_marketing_channel
= @vc_marketing_channel))
OR ((@vc_marketing_channel =
'Email Marketing')
AND (vc_marketing_channel
= @vc_marketing_channel
AND vc_campaign_id_sf
= @vc_campaign_id_sf)))
AND in_customer_type_id
= @in_customer_type_id
AND in_selection_criteria_id
= @in_selection_criteria_id
AND ((in_year_id =
YEAR(@dt_start_compare) AND in_month_id >= MONTH(@dt_start_compare))
OR ((in_year_id >
YEAR(@dt_start_compare) AND in_year_id < YEAR(@dt_end_compare)) AND (in_month_id >= 1 AND in_month_id <= 12))
OR ((in_year_id >
YEAR(@dt_start_compare)) AND (in_year_id = YEAR(@dt_end_compare)) AND (in_month_id >= 1 AND in_month_id <= MONTH(@dt_end_compare)))
OR ((in_year_id =
YEAR(@dt_start_compare)) AND (in_year_id = YEAR(@dt_end_compare)) AND (in_month_id >= MONTH(@dt_start_compare) AND in_month_id <= MONTH(@dt_end_compare))))
GROUP BY
in_month_id
CREATE TABLE
#temp_main (in_month int, dc_current_estimated_net_roi decimal(12, 2), dc_previous_estimated_net_roi
decimal(12, 2))
INSERT INTO
#temp_main (in_month)
SELECT in_month
FROM #temp_current
INSERT INTO
#temp_main (in_month)
SELECT in_month
FROM #temp_previous
WHERE in_month NOT IN (SELECT in_month FROM #temp_main)
UPDATE a
SET a.dc_current_estimated_net_roi
= ISNULL((SELECT dc_estimated_net_roi FROM
#temp_current WHERE
in_month = a.in_month), 0)
FROM #temp_main
a
UPDATE a
SET a.dc_previous_estimated_net_roi
= ISNULL((SELECT dc_estimated_net_roi FROM
#temp_previous WHERE
in_month = a.in_month), 0)
FROM #temp_main
a
SELECT DATENAME(MONTH, in_month * 28) AS vc_month, FLOOR(ROUND(dc_previous_estimated_net_roi, 0)) AS [Last Period],
FLOOR(ROUND(dc_current_estimated_net_roi, 0))
AS [Current Period]
FROM #temp_main
ORDER BY (MONTH(GETDATE()) - in_month) % 12 -- starting from Current
Month to the Last Month from Current
DROP TABLE
#temp_current
DROP TABLE
#temp_previous
DROP TABLE
#temp_main
--------------------------Stored
Procedure
End-----------------------------------
For report from Current Month to the Last Month from Current in the given period in
SQL Query, we use ORDER BY ((MONTH(GETDATE()) - in_month) + 12) % 12. This is the key which does this
trick.
Ex: Let us assume that the current
month is March.
Current
Date: 03/04/2013
@dt_start
= '08/01/2011'
@dt_end
= '04/30/2012'
Output Months Order:
March
February
January
December
November
October
September
August
April
Note: Notice that the month starts from
March and decreases gradually.
If we
require report from One Month before
Current Month to the Last Month from Current, we can use the following text
in the SQL Query:
ORDER BY
((MONTH(GETDATE()) - (in_month + 1)) + 12) % 12
Ex: Let us assume that the current
month is March.
Current
Date: 03/04/2013
@dt_start
= '08/01/2011'
@dt_end
= '04/30/2012'
Output Months Order:
February
January
December
November
October
September
August
April
March
Note: Notice that the month starts from February
and decreases gradually.
If we
require report from End Month of the
period to Last Month from End Month of the period, we can use the following
text in the SQL Query:
ORDER BY
((MONTH(@dt_end) - in_month) + 12) % 12
Ex: Let us assume that the current
month is March.
Current
Date: 03/04/2013
@dt_start
= '08/01/2011'
@dt_end
= '04/30/2012'
Output Months Order:
April
March
February
January
December
November
October
September
August
Note:
Notice that the month starts from April and decreases gradually.
