Display all months even if values are NULL

Question

Post reply

Display all months even if values are NULL

pwalter83

SSChampion

Points: 14575
More actions
May 13, 2015 at 7:45 am

#300629

Hi,
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.
Can someone please advice how to display all the months even if the values are NULL ? Please find my query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
GROUP BY LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2), CAST('01 ' + LEFT(DATENAME(MONTH,
m.CREATE_DT), 3) + ' ' + CAST(RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2) AS VARCHAR) AS DATE)
ORDER BY CAST('01 ' + LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + CAST(RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2) AS VARCHAR) AS DATE)
Thanks for your time.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

imex Ten Centuries Points: 1234 More actions · Answer 1

Hi,

Try:

with

CTE_Months as

(

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as MonthYear

union all

select DATEADD(MONTH, -1, MonthYear)

from CTE_Months

where MonthYear > DATEADD(MONTH, -13, GETDATE())

),

CTE_Count as

(

select

COUNT(BOOKING_ID) AS count_booking,

DATEADD(MONTH, DATEDIFF(MONTH, 0, CREATE_DT), 0) as MonthYear

from MG_BOOKING

where

CREATE_DT >= DATEADD(MONTH, -13, GETDATE())

group by

DATEADD(MONTH, DATEDIFF(MONTH, 0, CREATE_DT), 0)

)

select count_booking, MonthYear

from CTE_Count

union all

select 0, MonthYear

from CTE_Months as m

where

not exists (select 1 from CTE_Count as c where c.MonthYear = m.MonthYear)

order by MonthYear

Hope this helps.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lot of work being done. This may not display what you want in the result set, but it should meet your requirements.

with Months14 as (

select dateadd(month,datediff(month,0,getdate()) - n, 0) MonthDate

from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13))dt(n))

select

m14.MonthDate,

count(mb.BOOKING_ID) CountBooking

from

Months14 m14

left outer join dbo.MG_BOOKING mb

on (m14.MonthDate = dateadd(month,datediff(month,0,mb.CREATE_DT),0))

where

mb.CREATE_DT >= dateadd(month,-13,getdate())

group by

m14.MonthDate

order by

m14.MonthDate;

pwalter83 SSChampion Points: 14575 More actions · Answer 3

imex (5/13/2015)
Hi,
Try:
with
CTE_Months as
(
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as MonthYear
union all
select DATEADD(MONTH, -1, MonthYear)
from CTE_Months
where MonthYear > DATEADD(MONTH, -13, GETDATE())
),
CTE_Count as
(
select
COUNT(BOOKING_ID) AS count_booking,
DATEADD(MONTH, DATEDIFF(MONTH, 0, CREATE_DT), 0) as MonthYear
from MG_BOOKING
where
CREATE_DT >= DATEADD(MONTH, -13, GETDATE())
group by
DATEADD(MONTH, DATEDIFF(MONTH, 0, CREATE_DT), 0)
)
select count_booking, MonthYear
from CTE_Count
union all
select 0, MonthYear
from CTE_Months as m
where
not exists (select 1 from CTE_Count as c where c.MonthYear = m.MonthYear)
order by MonthYear
Hope this helps.

Thanks very much, that worked perfectly !

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

pwalter83 (5/13/2015)
imex (5/13/2015)
Hi,
Try:
with
CTE_Months as
(
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as MonthYear
union all
select DATEADD(MONTH, -1, MonthYear)
from CTE_Months
where MonthYear > DATEADD(MONTH, -13, GETDATE())
),
CTE_Count as
(
select
COUNT(BOOKING_ID) AS count_booking,
DATEADD(MONTH, DATEDIFF(MONTH, 0, CREATE_DT), 0) as MonthYear
from MG_BOOKING
where
CREATE_DT >= DATEADD(MONTH, -13, GETDATE())
group by
DATEADD(MONTH, DATEDIFF(MONTH, 0, CREATE_DT), 0)
)
select count_booking, MonthYear
from CTE_Count
union all
select 0, MonthYear
from CTE_Months as m
where
not exists (select 1 from CTE_Count as c where c.MonthYear = m.MonthYear)
order by MonthYear
Hope this helps.
Thanks very much, that worked perfectly !

No need for the recursive CTE. Did you look at the code I posted?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

This is very similar to what Lynn already posted, with a little improvement to make the JOIN clause SARGable. I agree that recursion is not needed, neither the UNION ALL to include missing months.

Note that there isn't a WHERE clause because the JOIN will filter the rows needed.

WITH E AS

(

SELECT n FROM (VALUES(0),(0),(0),(0)) E(n)

)

,

CTE_Tally AS

(

SELECT TOP 13 (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1) * -1 n

FROM E a, E b

)

,

CTE_Months as

(

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + n, 0) AS MonthYear,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + n + 1, 0) AS MonthEnd, n

FROM CTE_Tally

)

SELECT STUFF( CONVERT( char(11), m.MonthYear, 0), 5, 5, '') Month_Name,

COUNT(b.BOOKING_ID) AS count_booking

FROM CTE_Months m

LEFT JOIN MG_BOOKING b ON b.CREATE_DT >= m.MonthYear

AND b.CREATE_DT < m.MonthEnd

GROUP BY m.MonthYear

ORDER BY m.MonthYear

EDIT: Corrected month formula.

EDIT 2: Corrected error

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Luis Cazares (5/13/2015)
This is very similar to what Lynn already posted, with a little improvement to make the JOIN clause SARGable. I agree that recursion is not needed, neither the UNION ALL to include missing months.
Note that there isn't a WHERE clause because the JOIN will filter the rows needed.
WITH E AS
(
SELECT n FROM (VALUES(0),(0),(0),(0)) E(n)
)
,
CTE_Tally AS
(
SELECT TOP 13 (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1) * -1 n
FROM E a, E b
)
,
CTE_Months as
(
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + n, 0) AS MonthYear,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + n + 1, 0) AS MonthEnd, n
FROM CTE_Tally
)
SELECT STUFF( CONVERT( char(11), m.MonthYear, 0), 5, 5, '') Month_Name,
COUNT(b.BOOKING_ID) AS count_booking
FROM CTE_Months m
LEFT JOIN MG_BOOKING b(BOOKING_ID, CREATE_DT) ON b.CREATE_DT >= m.MonthYear
AND b.CREATE_DT < m.MonthEnd
GROUP BY m.MonthYear
ORDER BY m.MonthYear
EDIT: Corrected month formula.

Similar but not quite the same. There is also a error in your code above.

Give the following a spin. Also, comment the SET STATISTICS lines and generate an execution plan for them both as well.

-- CREATE_DT datetime

-- );

--create clustered index idx_1 on dbo.MG_BOOKING_Test (CREATE_DT);

--with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

-- e2(n) as (select 1 from e1 a cross join e1 b),

-- e4(n) as (select 1 from e2 a cross join e2 b),

-- e6(n) as (select row_number() over (order by (select null)) from e4 a cross join e2 b)

--insert into dbo.MG_BOOKING_Test(BOOKING_ID,CREATE_DT)

--select

-- ABS(CHECKSUM(NEWID()))%100+1,

-- CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0+2000.0 AS DATETIME) randomDate

--from e6;

set statistics io,time on;

with Months14 as (

select dateadd(month,datediff(month,0,getdate()) - n, 0) MonthDate

from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13))dt(n))

select

m14.MonthDate,

count(mb.BOOKING_ID) CountBooking

from

Months14 m14

left outer join dbo.MG_BOOKING_Test mb

on (m14.MonthDate = dateadd(month,datediff(month,0,mb.CREATE_DT),0))

where

mb.CREATE_DT >= dateadd(month,-13,getdate()) and

mb.CREATE_DT < getdate()

group by

m14.MonthDate

order by

m14.MonthDate;

set statistics io,time off;

set statistics io,time on;

WITH E AS

(

SELECT n FROM (VALUES(0),(0),(0),(0)) E(n)

)

,

CTE_Tally AS

(

SELECT TOP 14 (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1) * -1 n

FROM E a, E b

)

,

CTE_Months as

(

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + n, 0) AS MonthYear,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + n + 1, 0) AS MonthEnd, n

FROM CTE_Tally

)

SELECT STUFF( CONVERT( char(11), m.MonthYear, 0), 5, 5, '') Month_Name,

COUNT(b.BOOKING_ID) AS count_booking

FROM CTE_Months m

LEFT JOIN MG_BOOKING_Test b ON b.CREATE_DT >= m.MonthYear

AND b.CREATE_DT < m.MonthEnd

GROUP BY m.MonthYear

ORDER BY m.MonthYear;

set statistics io,time off;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Oh yeah, I saw the error that came from using a table value constructor for testing purposes.

The queries will give different results, but that's a difference on requirement understanding. I'm querying for full natural months and you're querying a specific range for exactly 13 months. By the way, using that range is what enables a clustered index seek, instead of an index scan which seems to slow everything down incredibly.

The WHERE clause is also changing the OUTER JOIN into an INNER JOIN. Obviously this was a distraction from you and it can be solved by including the filters in the JOIN clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Credits to Navy beans says the op probably doesn't care about the performance or technique improvements because he has a solution that works even though the rCTE isn't what any of us would use for the reasons already stated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)