Need help grouping by year

3 Feb 2006 7:03 PM

johngilmer

I have a table of patients. There's a date column called AdmitDate. So
if I want to know the number of patients that were admitted in each
year, I can do:

SELECT     DATENAME([year], AdmitDate) AS 'Year', COUNT(*) AS
Admissions
FROM         Patients
WHERE     (0 = 0)
GROUP BY DATENAME([year], AdmitDate)

The output might be:

Year   Admissions
2003   455
2004   489
2005   501

But then if I put a WHERE clause on the query such that no patient
records meet the criteria (e.g. WHERE PatientAge > 150), then the query
returns a blank DataSet with no rows at all. Instead, I want it to
return

Year   Admissions
2003   0
2004   0
2005   0

Any ideas how I could do this? Thanks in advance.

3 Feb 2006 7:11 PM

MJKulangara

not tested..but something like this...

declare @table table
(
year int primary key,
Admissions int default 0
)

declare @max_yr int,
    @min_yr int

set @max_yr = max DATENAME([year], AdmitDate)
from Admissions
set @min_yr = max DATENAME([year], AdmitDate)
from Admissions

while @min_yr <= @max_yr
begin
    insert @table(year)
    select @min_yr

    set @min_yr = @min_yr + 1
end

update @table
set Admissions = x.Admissions
from
@table t
join
(
SELECT     DATENAME([year], AdmitDate) AS 'Year', COUNT(*) AS
Admissions
FROM         Patients
WHERE     (0 = 0)
GROUP BY DATENAME([year], AdmitDate)
)x
on x.year = t.year

select * from @table

MJKulangara
http://sqladventures.blogspot.com

3 Feb 2006 7:11 PM

Raymond D'Anjou

<johngil***@yahoo.com> wrote in message
Show quote

news:1138993429.310257.64700@g14g2000cwa.googlegroups.com...
>I have a table of patients. There's a date column called AdmitDate. So
> if I want to know the number of patients that were admitted in each
> year, I can do:
>
> SELECT     DATENAME([year], AdmitDate) AS 'Year', COUNT(*) AS
> Admissions
> FROM         Patients
> WHERE     (0 = 0)
> GROUP BY DATENAME([year], AdmitDate)
>
> The output might be:
>
> Year   Admissions
> 2003   455
> 2004   489
> 2005   501
>
> But then if I put a WHERE clause on the query such that no patient
> records meet the criteria (e.g. WHERE PatientAge > 150), then the query
> returns a blank DataSet with no rows at all. Instead, I want it to
> return
>
> Year   Admissions
> 2003   0
> 2004   0
> 2005   0
>
> Any ideas how I could do this? Thanks in advance.

SELECT     DATENAME([year], AdmitDate) AS 'Year',
    SUM(CASE WHEN PatientAge > 150 THEN 1 ELSE 0 END) AS
Admissions
FROM         Patients
GROUP BY DATENAME([year], AdmitDate)

Although I don't see the utility of such a query.

3 Feb 2006 7:37 PM

Green

See if this helps:

select b.Year, isnull(a.Admissions,0) from
(
SELECT DATENAME([year], AdmitDate) AS 'Year', COUNT(*) AS Admissions
FROM Patients
WHERE (0 = 0)
GROUP BY DATENAME([year], AdmitDate)
) a
right join
(
select distinct DATENAME([year], AdmitDate) AS 'Year' from Patients
) b
on a.Year=b.Year