Add missing Months in between dates

[tirf]

I have a recordset like this

SiteID ProtocolID Month Year PatientCount
1 2 Aug 2011 3
1 2 Nov 2011 2
2 3 Jul 2010 1
2 3 Aug 2010 3
3 4 Jan 2009 1
3 4 Mar 2009 2
3 4 Jun 2001 3

I want to fill out the missing months in between for Site protocol and patient Count should be 0, so my recordset should look like

SiteID ProtocolID Month Year PatientCount
1 2 Aug 2011 3
1 2 Sep 2011 0
1 2 Oct 2011 0
1 2 Nov 2011 2
2 3 Jul 2010 1
2 3 Aug 2010 3
3 4 Jan 2009 1
3 4 Feb 2009 0
3 4 Mar 2009 2
3 4 Apr 2009 0
3 4 May 2009 0
3 4 Jun 2001 3

I appreciate your help

Thanks

 

[r937]

what range of dates?

for example, i notice that you have a row for june of 2001, so how come you don't want to see rows for 2002 through 2008?

and what about 2012?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tirf]

that was by mistake should have been 2009

--first recordset
SiteID ProtocolID Month Year PatientCount
1 2 Aug 2011 3
1 2 Nov 2011 2
2 3 Jul 2010 1
2 3 Aug 2010 3
3 4 Jan 2009 1
3 4 Mar 2009 2
3 4 Jun 2009 3

--finalrecordset

SiteID ProtocolID Month Year PatientCount
1 2 Aug 2011 3
1 2 Sep 2011 0
1 2 Oct 2011 0
1 2 Nov 2011 2
2 3 Jul 2010 1
2 3 Aug 2010 3
3 4 Jan 2009 1
3 4 Feb 2009 0
3 4 Mar 2009 2
3 4 Apr 2009 0
3 4 May 2009 0
3 4 Jun 2009 3

 

[r937]

why no 2009 rows for site id 1 ?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tirf]

site 1 has minimum date Aug 2011 and maximum date of Nov 2011, so we fill out the missing dates between the two

Sep 2011 and Oct 2011

Thanks

 

[r937]

aha, min and max for each site as taken from existing data

y u no say so earlier ;-)

can you use CTEs?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tirf]

I tryed cte and recursive cte but not able to resolve it, I am forwarding it to the gurus of tek-tips

 

[markros]

Try:

;with Periods as (select distinct SiteID, ProtocolID, [Year] from DataTable), Months as (select 'Jan' union all select 'Feb' etc.),
AllInfo as (select * FROM Periods CROSS JOIN Months)

select AI.*, T.COALESCE(PatientCount,0) as PatienCount
from AllInfo AI LEFT JOIN DataTable T ON AI.SiteID = T.SiteID and
AI.ProtocolID = T.ProtocolID and AI.[Year] = T.[Year]

So, the idea is to get all unique possible combinations and then LEFT JOIN to your actual data.


PluralSight Learning Library