question

AlphaKilo avatar image
AlphaKilo asked

Dealing with NULL values

I have a dashboard that lists the booking and releases for the last 30 days. i am using a simple query to get the data in different parts that I put together to get the average for the month.The problem is that if of the days there are no releases, then the whole day gets dropped from the dashboard because of the NULL count for that day. How can I get past the issue?

I have tried using case statement to replace the NULL to 0, but nothing seems to be working.

View for getting release counts:

select count(distinct b.BookingNumber) as rcount, convert(Date,br.ReleaseDate) as rdays, v3.Description as Gender from NWSAEGISMSPDB..BookingRelease br join NWSAEGISMSPDB..Booking b on b.BookingID = br.BookingID join NWSAEGISMSPDB..GlobalJacket g on g.JacketID = b.JacketID join NWSAEGISMSPDB..ValidationSetEntry v3 on v3.EntryID = g.vsSex where br.ReleaseDate > dateadd(day,datediff(day,'19000101 00:00:00', getdate())-30,'19000101 00:00:00') and br.ReleaseDate <= GETDATE() and b.ORI = 'CA0380000' group by v3.Description, convert(Date,br.ReleaseDate)

I use this out the count together:

select b.bdays 'Day', case when d.Ct is NULL then '0' else d.Ct end 'Female Daily Population', case when d1.ct is NULL then '0' else d1.Ct end 'Male Daily Population',d.Ct+d1.Ct as 'Total Daily Population',case when b.bcount is NULL then '0' else b.bcount end 'Females Booked', case when b1.bcount is NULL then '0' else b1.bcount end 'Males Booked',case when r.rcount is NULL then '0' else r.rcount end 'Females Released',case when r1.rcount is NULL then '0' else r1.rcount end 'Males Released', b.bcount+b1.bcount as 'Total Booked', r.rcount+r1.rcount as 'Total Released' from vwBooking_Counts b left join vwBooking_Counts b1 on b.bdays = b1.bdays left join vwRelease_Counts r on r.rdays = b.bdays left join vwRelease_Counts r1 on r.rdays = r1.rdays left join daily_count d on d.dy = b.bdays left join daily_count d1 on d1.dy = b.bdays where b.Gender = 'Female' and b1.Gender = 'Male' and r.Gender = 'Female' and r1.Gender = 'Male' and d.gender = 'Female' and d1.gender = 'Male'

viewscase-statement
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

You would need to join your data to another set of data that lists all the dates in the last 30 days. A Calendar table is often used, but you can generate them on the fly.
For example a data set of the last 30 days could be generated like

;with cte_tally as 
(
select top 10000
    ROW_NUMBER() over(order by sc1.column_id) N
from
    sys.columns sc1,sys.columns sc2
)
select
cast(dateadd(day,-N+1,getdate()) as date)
from cte_tally
where N <= 30

If you then outer join to this, the days that have no releases will have a NULL value.

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Kev Riley Can you please help me out even more by showing me how to join it to my query.I have never used CTE before and don't know how to join it. Thank you for all your help. This is the view I use to get the data:

select count(distinct b.BookingNumber) as rcount,
convert(Date,br.ReleaseDate) as rdays,
v3.Description as Gender
from BookingRelease br join Booking b on b.BookingID = br.BookingID
join GlobalJacket g on g.JacketID = b.JacketID
join ValidationSetEntry v3 on v3.EntryID = g.vsSex
where br.ReleaseDate > dateadd(day,datediff(day,'19000101 00:00:00', getdate())-30,'19000101 00:00:00') and br.ReleaseDate <= GETDATE()
group by v3.Description, convert(Date,br.ReleaseDate)

0 Likes 0 ·

It would be something like:

with cte_tally as 
(
select top 10000
    ROW_NUMBER() over(order by sc1.column_id) N
from
    sys.columns sc1,sys.columns sc2
), last30days as (
select
cast(dateadd(day,-N+1,getdate()) as date) [date]
from cte_tally
where N <= 30)

select count(distinct b.BookingNumber) as rcount,
last30days.date as rdays,
v3.Description as Gender
from BookingRelease br join Booking b on b.BookingID = br.BookingID
join GlobalJacket g on g.JacketID = b.JacketID
join ValidationSetEntry v3 on v3.EntryID = g.vsSex
right join last30days on last30days.date = convert(Date,br.ReleaseDate)
where br.ReleaseDate > dateadd(day,datediff(day,'19000101 00:00:00', getdate())-30,'19000101 00:00:00') and br.ReleaseDate <= GETDATE()
group by v3.Description, last30days.date;

0 Likes 0 ·
AlphaKilo avatar image
AlphaKilo answered

Thank you @Kev Rileyv

I was making it way more complicated.

But it’s still not giving me what I need.

For 5-15-20, there are no female releases. I want it to show 0 or NULL if there aren’t any

7 5/13/2020 Female 32 5/13/2020 Male 7 5/14/2020 Female 22 5/14/2020 Male 29 5/15/2020 Male 3 5/16/2020 Female 18 5/16/2020 Male
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So like the calendar table has all the dates, you'll need a 'gender' table too, that lists both genders that you can then outer join to.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.