CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Hardik Parmar
Hardik Parmar

Reputation: 1051

How to get dynamic month's data depending on the Conditon in sql

I am working on financial domain. So I want data from April To May.

Suppose Current Month IS June then I only want April to June of this year and it should group by year and month.

And If Suppose Current Month is February then I want details from last April to Jan i.e. April-2013 to Jan-2014

I have written CTE expression for this I have Got partial result I don't know I am doing wrong or right

declare @Month int 
declare @Year int ;
set @Month = 4;
set @Year = YEAR(GETDATE());

if (@Month <= MONTH(GETDATE()))
    with cte as 
    (
    SELECT N = 4, YYY =  YEAR(GETDATE())
    UNION ALL
    SELECT N + 1, YYY
    FROM cte
    WHERE N+1 <= MONTH(GETDATE())
    )
    select N, YYY from cte

else  

    with cte as 
    (

    SELECT N = 4 , YYYY = YEAR(GETDATE() - 1)
    UNION ALL
    SELECT N + 1,YYYY
    FROM cte
    WHERE N+1 <= 12     
    )
    select N, YYYY from cte

I want O/P something like this

If Current month is greater then April then or more specifically suppose current month is June then 

April 2014
May   2014
June  2014

If current month is February-2014 then I want 

April 2013
May   2013
June  2013
July  2013   
Aug   2013 
Sep   2013
Oct   2013
Nov   2013
Dec   2013
Jan   2014
Feb   2014

Thank You.

Upvotes: 0

Views: 1471

Answers (1)

adrianm
adrianm

Reputation: 14736

DECLARE @CurrentDate AS datetime = '20150212';
DECLARE @FirstDayInCurrentMonth AS datetime = DATEADD(day, 1 - DATEPART(day, @CurrentDate), @CurrentDate);
DECLARE @LastApril AS datetime = DATEADD(month, -(DATEPART(month, @FirstDayInCurrentMonth) + 8) % 12, @FirstDayInCurrentMonth);

WITH Periods AS (
    SELECT @LastApril AS [Period]
  UNION ALL
    SELECT DATEADD(month, 1, [Period])
    FROM Periods
    WHERE [Period] < @FirstDayInCurrentMonth
)
SELECT DATENAME(month, [Period])
      ,DATEPART(year, [Period])
FROM Periods

Note that this returns only april for days in april. It is not clear from your question if you want 1 or 13 months for days in april.

Upvotes: 3

Related Questions