Padding a query with null values 1

[briangriffin]

We went live with a new system on 3/1, so I don't have a full year's worth of data to work with. However, I don't want to have to retool a lot of queries once we hit 3/1/2014 so I want to return 12 records for every distinct value in the measure column.

Calendar table query dynamically returning either the 12 months starting 3/1/2013, or the previous 12 month values once we hit 3/1/2014:

3/1/2013
4/1/2013
5/1/2013
6/1/2013
7/1/2013
8/1/2013
9/1/2013
10/1/2013
11/1/2013
12/1/2013
1/1/2014
2/1/2014


Measure table is only returning data since 3/1:

3/1/2013 Advance Directives 99%
4/1/2013 Advance Directives 98%
5/1/2013 Advance Directives 99%
6/1/2013 Advance Directives 100%
3/1/2013 Smoking Status 100%
4/1/2013 Smoking Status 100%
etc. etc.

What I want to return would be:

3/1/2013 Advance Directives 99%
4/1/2013 Advance Directives 98%
5/1/2013 Advance Directives 99%
6/1/2013 Advance Directives 100%
7/1/2013 Advance Directives null
8/1/2013 Advance Directives null
9/1/2013 Advance Directives null
10/1/2013 Advance Directives null
11/1/2013 Advance Directives null
12/1/2013 Advance Directives null
1/1/2014 Advance Directives null
2/1/2014 Active Directives null
3/1/2013 Smoking Status 100%
4/1/2013 Smoking Status 100%
5/1/2013 Smoking Status 100%
6/1/2013 Smoking Status 100%
7/1/2013 Smoking Status null
8/1/2013 Smoking Status null
etc. etc.

I could probably bludgeon something together, but surely there's an easier way to do this.

Thanks in advance.

 

[lameid]

Without knowing your table structure... The only thought I have is it sounds like you want a cartesian product to get all possible values as a subquery and a join to it to get the data (values).

 

[briangriffin]

Yes, of course - I had tried using a cross join but not in a subquery. Thanks!