SQL - How can you display monthly information starting from current month to last month in the period?

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.