get all months between two dates in sql with or without data

_{Friday, October 12, 2012 12:25 AM}

Hi,

Thanks for looking into this thread.

Please suggest a method where in we could retrieve name of all months in between two dates.

The months may or may not contain data, but as the need is to display monthly trend, we are required to fetch all months in between two date ranges with or without data.

Jan Feb Mar Apr May

0    10   20   0    20

Any help is appreciated.

Thanks...

_{Friday, October 12, 2012 12:31 PM ✅Answered}

You need to LEFT JOIN Table2 rs as you were LEFT JOINing the Table1.

Declare @fromDate datetime2;
Declare @toDate datetime2;

Set @fromDate = '2011-03-01';
Set @toDate = '2012-10-12';

With dt As
 (
 Select @fromDate As [TheDate]
 Union All
 Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < @toDate
 ) 
 select 
 CASE WHEN rs.[Title (Title)] ='' OR  rs.[Title (Title)] IS NULL THEN 'Not Assigned' ELSE rs.[Title (Title)] END AS [Org Name]
 , dt.TheDate
 , Count(ss.Req_ID)
 from dt left join Table1 ss
 on dt.TheDate = ss.Created 
LEFT join Table2 rs
 on ss.[Org Id] = rs.OrgId
  where dt.TheDate between @fromDate and @toDate
 group by 
 COALESCE(rs.[Title (Title)],'')
 , dt.TheDate

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Friday, October 12, 2012 4:20 PM ✅Answered}

Well, this is the JOIN you're currently using

from dt left join Table1 ss
 on dt.TheDate = ss.Created 

So, it assumes that Created is the first day in each month (since we created only dates for first of month) and doesn't have time portion.

If this is not the case, you only will get NULLs, so no wonder you got 0 as counts.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Friday, October 12, 2012 12:38 AM | 1 vote}

Declare @fromDate datetime2;
Declare @toDate datetime2;

Set @fromDate = '2012-01-01';
Set @toDate = '2012-12-01';

With dt As
(
Select @fromDate As [TheDate]
Union All
Select DateAdd(m, 1, TheDate) From dt Where Period < @toDate
)
Select
    dt.TheDate
    , <whatever else needs selecting>
From
    dt
    Left Join
    <the other table>
    On
    --The join condition:  Use DatePart.  I hope your month column is an integer!
    DatePart(m, dt.TheDate) = <other table>.<month field>
Where
    <whatever conditions needed>
;

Should work OK.  Just today I used this approach.  Requires SQL Server 2005 or above.

Jose R. MCP
Code Samples

_{Friday, October 12, 2012 12:42 AM}

The following link may be helpful

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/bf387555-70cc-4048-b612-22f653ba71ab

http://www.sqlservercentral.com/Forums/Topic1177709-392-1.aspx

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

_{Friday, October 12, 2012 12:49 AM}

Hey Jose,

Thanks for your reply and solution. :)

Getting a Max Recursion error.

Tried to include option (maxrecursion 365); but it does not solve the problem.

Thanks !!

_{Friday, October 12, 2012 12:51 AM}

Option (MaxRecursion 0) deactivates the recursion limit.  Use it wisely.  Note, however, that if you only need months, 12 iterations is the maximum number you'll need.  Just make sure the To and From dates are in the same year.

Jose R. MCP
Code Samples

_{Friday, October 12, 2012 12:53 AM}

Can anyone come up with an elegant solution that works when the two dates are not in the same year? The original question did not exclude this case.

Cheers!

Josh Ash

_{Friday, October 12, 2012 12:56 AM | 1 vote}

Declare @fromDate datetime2;
Declare @toDate datetime2;

Set @fromDate = '2012-01-01';
Set @toDate = '2012-12-01';

With dt As
(
Select @fromDate As [TheDate]
Union All
Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < @toDate
)
Select
    dt.TheDate
    
From
    dt

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Friday, October 12, 2012 12:59 AM}

Declare @fromDate datetime2;
Declare @toDate datetime2;

Set @fromDate = '2012-01-01';
Set @toDate = '2012-12-01';

With dt As
(
Select @fromDate As [TheDate]
Union All
Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < @toDate
)
Select
    dt.TheDate
    
From
    dt

Thanks, that's neat!

Josh Ash

_{Friday, October 12, 2012 1:07 AM | 1 vote}

Josh, that's the exact same CTE that I posted.  It is just showing without being joined to any other table.

Jose R. MCP
Code Samples

_{Friday, October 12, 2012 1:09 AM}

Thank you all..

Yes. I got the result but in my database, I have data from Sept 2011 onwards. End user could select any date sa Mar 2011, in this case, the result being returned in again from Sept 2011 only and not Mar 2011.. We need to showcase trend from the start date till end date..

Below is my query.. Please have a look and suggest if any changes are required to get desired data -

Declare @fromDate datetime2;
 Declare @toDate datetime2;

Set @fromDate = '2011-03-01';
 Set @toDate = '2011-09-29';

With dt As
 (
 Select @fromDate As [TheDate]
 Union All
 Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < @toDate
 )
 Select
     dt.TheDate
    
 From
     dt
  left join
  sample ss
   On
    DatePart(m, dt.TheDate) = DatePart(m, ss.Created)
 Where
 ss.Created BETWEEN
   '2011-03-01' AND DATEADD(minute,1439,'2011-09-29')

 the result set comes as below (but we need to fetch month even they do not have data i.e. from March 2011)

2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000
2011-09-01 00:00:00.0000000

Thanks for your help..

_{Friday, October 12, 2012 1:18 AM}

You need this change:

Declare @fromDate datetime2;
 Declare @toDate datetime2;

Set @fromDate = '20110301';
 Set @toDate = '20110930';

With dt As
 (
 Select @fromDate As [TheDate]
 Union All
 Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < @toDate
 )
 Select
     dt.TheDate
     
 From
     dt
  left join
  sample ss
   On ss.Created >= dt.TheDate and
    ss.Created < dateadd(month,1,dt.TheDate)
    and ss.Created < DATEADD(minute,1439,'20110929')
 Where
 dt.TheDate BETWEEN
   @FromDate AND @toDate

Or, alternatively, you can create a calendar table with all dates (not just first of month).

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Friday, October 12, 2012 1:31 AM}

Thanks a lot Naomi !!

Really appreciate your help with this. However, as soon as I join a third table from where I need to pull org name and on grouping those, need to calculate count from sample table, it again starts creating the result set from Sept 2011 and display only those months for which we have data.

Does this joining with third table making a difference here (I think, this should not be a problem though) ?

_{Friday, October 12, 2012 1:34 AM}

If you are using SQL Server 2008, you can declare multiple CTE's.  Declare a second CTE with the join of the other tables, and finally simply left join the two CTE's.  It will work.  It is exactly what I had to do today, not 8 hours ago.  I wanted totals by periods and wanted zeroes where there was no data and this is how I did it.

Jose R. MCP
Code Samples

_{Friday, October 12, 2012 1:52 AM}

Hi Jose,

Can you please send me an example about the same? As soon as i join it with a third table, it changes the result set and starts giving data from Sept month.

Trying again....

Thank you...

_{Friday, October 12, 2012 2:23 AM}

Well, I don't know your table structure.  It would be good if you showed your tables and explain how they are joined.

In my case I needed:  GENERATED_DATES Left Join TABLEA Inner Join TABLEB.  The Inner Join was killing the no-data records.  So I did With dt ( ... ), source As (Select what I need From TABLEA Inner Join TABLEB) Select what I need From dt Left Join source.

Jose R. MCP
Code Samples

_{Friday, October 12, 2012 3:33 AM}

Josh, that's the exact same CTE that I posted.  It is just showing without being joined to any other table.

Jose R. MCP
Code Samples

Indeed it is - thanks mate!

Josh Ash

_{Friday, October 12, 2012 3:35 AM}

Start from creating the original result of LEFT JOIN between Calendar and your other table. Then use that result to JOIN with your third table.

Post your current query if you need more assistance.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Friday, October 12, 2012 8:34 AM}

Hello all,

@Jose - Yes. My requirement is exactly the same as yours.

@Naomi - I tried to follow yours as well as Jose's suggestions but seems like still I am doing some silly thing here...

Below are the details of my tables -

Table dt: DT table (with processed date info)

Table 2: Sample (Request_Id, Org_Id, CreatedDate)

Table 3: Organization (Org_Id, Org_Name)

Need to fetch Count(Request_Id) group by Organization names, to showcase Count(Request_Id) against each Organization name...

Query is as below -

Declare @fromDate datetime2;
Declare @toDate datetime2;

Set @fromDate = '2011-03-01';
Set @toDate = '2012-10-12';

With dt As
 (
 Select @fromDate As [TheDate]
 Union All
 Select DateAdd(month, 1, TheDate) From dt Where [TheDate] < @toDate
 )
 select
 CASE WHEN rs.[Title (Title)] IN ('') THEN ('Not Assigned') ELSE rs.[Title (Title)] END AS [Org Name]
 , dt.TheDate
 , Count(ss.Req_ID)
 from dt left join Table1 ss
 on dt.TheDate = ss.Created
 inner join Table2 rs
 on ss.[Org Id] = rs.OrgId
  where dt.TheDate between @fromDate and @toDate
 group by
 rs.[Title (Title)]
 , dt.TheDate

It returns no rows. :(

Definitely, missing something here.. Sorry about this...

Thanks for all your help and advices..

_{Friday, October 12, 2012 4:02 PM}

Thanks Naomi...

Changed the query slightly...as it was asking to group on rs.[Title (Title)] field..thats the only change..

Now got the result but no count.. trying to get through.. :)

Org Name                    TheDate                         Cnt

Not Assigned   2012-03-01 00:00:00.0000000     0
Not Assigned   2012-04-01 00:00:00.0000000     0
Not Assigned   2012-05-01 00:00:00.0000000     0
Not Assigned   2012-06-01 00:00:00.0000000     0
Not Assigned   2012-07-01 00:00:00.0000000     0
Not Assigned   2012-08-01 00:00:00.0000000     0
Not Assigned   2012-09-01 00:00:00.0000000     0
Not Assigned   2012-10-01 00:00:00.0000000     0

Thanks !!

_{Friday, October 12, 2012 6:50 PM}

Hi Naomi / Jose,

Thanks a lot for your time, help and guidance...

Made little changes as per Naomi point,

- Modified CTE to pull ALL dates from each month

- Removed time stamp from the Table1 dates to map correctly with the CTE table

- Applied join to see the correct monthly trend as per the start and end dates

- Group by Organization, as required.

Yeahhh.. its solved !!! :)