Return data for all days even when null vlaues

Question

Return data for all days even when null vlaues

viksch

SSC Rookie

Points: 37
More actions
August 23, 2012 at 4:18 pm

#280661

Hi,
I have a transactional dataset that doesn't have data for all days b/c transactions didn't exist for that day. Even though there aren't any transactions for that day, I still want the all the days to be displayed with a null value. For example, I want the output to look like:
20120801 - 1
20120802 - Null
20120803 - 6
20120804 - Null
Currently my query output looks like this:
20120801 - 1
20120803 - 6
I also have a date table with all the days in it. I've tried a number of different joins to join my date table to the transaction table and I still get the missing days and it only shows days where there is values.
I really don't want to use a temp table, so I was wondering if there's another way to write the code so all the days are displayed even if the value is NULL?
Any assistance would be greatly appreciated!
Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442470 More actions · Answer 1

Using a calendar table would work, you would use a left join from the date to your data.

I have to leave now, but if no one provides you a more solid example in the next few hours, I'll post one later tonight.

viksch SSC Rookie Points: 37 More actions · Answer 2

Thanks Lynn! I do have a date table which serves as a calendar table and I've tried may join combinations and I still only get the two results opposed to four that I really want. It seems to work when I just use my transaction and date table; however, I have many more conditions in my where clause and when I add those that's when I only get the two results.

Any other thoughts would be great!

Thanks again!

Lynn Pettis SSC Guru Points: 442470 More actions · Answer 3

viksch (8/23/2012)
Thanks Lynn! I do have a date table which serves as a calendar table and I've tried may join combinations and I still only get the two results opposed to four that I really want. It seems to work when I just use my transaction and date table; however, I have many more conditions in my where clause and when I add those that's when I only get the two results.
Any other thoughts would be great!
Thanks again!

Nope, I can't see what you see so there really isn't much more I can do from here.

Now, if you would post the DDL for the tables, sample data for the tables, the expected results, and the code you are having trouble with then we can get somewhere.