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'