Dealing with NULL values

Question

question

AlphaKilo asked May 19, '20

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'

views case-statement

10 |1200

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

Answer 1 · 2020-05-20T08:23:47Z

Kev Riley answered May 20, '20

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

10 |1200

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

AlphaKilo commented · May 21, 2020 at 04:46 PM

@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 ·

Kev Riley ♦♦ AlphaKilo commented · May 22, 2020 at 08:40 AM

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 ·

Answer 2 · 2020-05-22T19:41:26Z

AlphaKilo answered May 22, '20

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

10 |1200

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

Kev Riley ♦♦ commented · May 26, 2020 at 09:56 AM

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 ·

question