SQL Date Query include month even if no data

Question

I have a booking database with various dates for each booking. I want to get a count of all bookings in each month e.g.

JAN | 12
FEB | 15
MAR | 53
APR | 25

If I have zero bookings in a month, how can I still get the month listed? e.g.:

JAN | 12
FEB | 15
MAR | 53
APR | 0
MAY | 52

Schema:

ID | BookingDate | REF

I don't have or want to have a months table to join against.

Below is my query so far:

SELECT TOP 1000 [Id]
  ,[MemberId]
  ,[LocationId]
  ,[Date]
  ,[BookingStateId]
  ,[RPEFeeling]
  ,[RPEPush]
  ,[Notes]
  ,[TimeSlotId]
  ,[MembershipId]
  ,[TrialSession]
  ,[CreatedDate]
  ,[ModifiedDate]
  ,[CreatedBy]
  ,[ModifiedBy]
  FROM [DB].[dbo].[SessionBooking]

DECLARE @months TABLE (MonthNumber INT, YearNumber INT, Name VARCHAR(50))
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (1, 2014, 'Jan')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (2, 2014, 'Feb')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (3, 2014, 'Mar')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (4, 2014, 'Apr')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (5, 2014, 'May')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (6, 2014, 'Jun')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (7, 2014, 'Jul')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (8, 2014, 'Aug')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (9, 2014, 'Sept')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (10, 2014, 'Oct')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (11, 2014, 'Nov')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (12, 2014, 'Dec')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (1, 2015, 'Jan')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (2, 2015, 'Feb')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (3, 2015, 'Mar')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (4, 2015, 'Apr')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (5, 2015, 'May')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (6, 2015, 'Jun')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (7, 2015, 'Jul')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (8, 2015, 'Aug')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (9, 2015, 'Sept')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (10, 2015, 'Oct')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (11, 2015, 'Nov')
INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (12, 2015, 'Dec')

SELECT MonthNumber, YearNumber, SUM(Id) 
FROM SessionBooking b
RIGHT JOIN @months mo ON DATEPART(m, b.Date) = mo.MonthNumber 
       AND DATEPART(yy, b.Date) = mo.YearNumber
GROUP BY mo.MonthNumber, mo.YearNumber

This is only returning the following results (where there are multiple bookings over last two years on each month.

MonthNumber YearNumber  (No column name)
1   2014    NULL
2   2014    NULL
3   2014    NULL
4   2014    NULL
5   2014    NULL
6   2014    NULL
7   2014    NULL
8   2014    NULL
9   2014    NULL
10  2014    NULL
11  2014    NULL
12  2014    NULL
1   2015    NULL
2   2015    215
3   2015    134
4   2015    NULL
5   2015    NULL
6   2015    NULL
7   2015    NULL
8   2015    NULL
9   2015    NULL
10  2015    NULL
11  2015    NULL
12  2015    NULL

Is this within a specific calendar year, or could it span multiple years? — Cᴏʀʏ, Sep 01 '15 at 22:19
This could technically span multiple years..if i was to do a historical booking query...(I am trying to get this data out so that i can put it into a graph) — Christopher Pettigrew, Sep 01 '15 at 22:22
You *could* use a Numbers table (which you can google about), but basically you can create a CTE that, with a seed date, uses `DATEADD(dd, SeedDate, n)`, where `n` is the row value from the Numbers table. That CTE gives you a crap load of dates, to which you `LEFT JOIN` your table to fill in the gaps. — Cᴏʀʏ, Sep 01 '15 at 22:29
I added a ISNULL in the SELECT statement of my answer to display 0 when there are no records instead of NULL. — Francisco Goldenstein, Sep 01 '15 at 22:47

Francisco Goldenstein · Answer 1 · 2015-09-01T22:46:59.193

2

You need to have a table variable with all the months and do a JOIN.

DECLARE @months TABLE (MonthNumber INT, YearNumber INT, Name VARCHAR(50))

INSERT INTO @months (MonthNumber, YearNumber, Name) VALUES (1, 2015, 'January')
...


SELECT MonthNumber, YearNumber, ISNULL(SUM(...), 0)
FROM Booking b
RIGHT JOIN @months mo ON DATEPART(m, b.BookingDate)=mo.MonthNumber AND DATEPART(yy, b.BookingDate)=mo.YearNumber
GROUP BY mo.MonthNumber, mo.YearNumber

EDIT: If you want to consider multiple years, add year as a column in @month and change the CROSS JOIN condition to consider month and year.

edited Sep 01 '15 at 22:46

answered Sep 01 '15 at 22:19

Francisco Goldenstein

13,299
7
58
74

Is this a virtual table or an actual SQL table? – Christopher Pettigrew Sep 01 '15 at 22:22
I think you mean `RIGHT JOIN` so you get 0 when no booking – Juan Carlos Oropeza Sep 01 '15 at 22:24
I am guessing this will only work for a single year? not to span multiple? – Christopher Pettigrew Sep 01 '15 at 22:25
I added a comment saying that if you need it for multiple years you need to add another column to @months table variable. – Francisco Goldenstein Sep 01 '15 at 22:26
I included YearNumber in my code now so now it works for multiple years. – Francisco Goldenstein Sep 01 '15 at 22:27

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

The easy way is using a months table because you can have empty months.

create table months (
   month_id integer,
   date_ini datetime,
   date_end datetime
)

And this are some examples of how create that table automatic

Using select Generate days from date range

This use store procedure in MSQL

edited May 23 '17 at 12:07

Community

1
1

answered Sep 01 '15 at 22:29

Juan Carlos Oropeza

47,252
12
78
118

SQL Date Query include month even if no data

2 Answers2

Linked

Related