Missing months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Missing months

Author

Topic

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-05-18 : 14:27:01

I have field called ContactDate that has contains date ranging from 1/1/2010 to the present date. There are instances where there no values for certain months. When I run my query may have a skipped month. Is it possible to account for the missing months with a zero value, even the month does not appear in my data set.

SELECT YEAR(ContactDate) AS YYear, MONTH(ContactDate) AS MMonth, COUNT(pkPatientID) AS Num_Count, County
FROM tblPersons

Output


1    2010    45
2    2010    60
4    2010    75

How could I account for March 2010 in my output:


3    2010    0

Regards

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-18 : 15:17:58

May be this could help you to get onto right direction

declare @tblPersons table (pkPatientID int,ContactDate datetime)
Insert into @tblPersons values (1,'2010-01-01'),(2,'2010-01-01'),(3,'2010-02-01'),(4,'2010-01-01')
SELECT YEAR(ContactDate) AS YYear, MONTH(ContactDate) AS MMonth, COUNT(pkPatientID) AS Num_Count
FROM @tblPersons
group by YEAR(ContactDate), MONTH(ContactDate)

union all

Select distinct 2010,a.number,0
FROM master..spt_values a
Left join @tblPersons b on MONTH(ContactDate)=a.Number
Where MONTH(ContactDate) is null and a.number between 1 and 12

Cheers
MIK

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 15:23:34

[code]
SELECT
YYear,
MMonth,
isnull(COUNT(pkPatientID),0) AS Num_Count--,
--County --what is this?
FROM
(
Select MMonth = number From master..spt_values Where type='P' and number between 1 and 12)
) Months
Cross Join
(
Select YYear = 2000+number From master..spt_values Where type='P' and number between 10 and 20)
) Years
Left Join tblPersons
On Months.MMonth = MONTH(ContactDate)
and Years.YYear = YEAR(ContactDate)
Group By MMonth, YYear
Order By MMonth, YYear
[/code]

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources