Records not qualfying with CASE 1

[gatetec]

// SQL Server 2000

In the 'DailyLog' table, there is a field called 'VisitReason'.

If 'VisitReason' contains both of 'Flu Accept' and 'Flu Vaccine' in the date range given, then I need the count AS AcceptVac.

If 'VisitReason' contains 'Flu Accept' but no 'Flu Vaccine' in the date range given, then I need the count AS AcceptNoVac.

If 'VisitReason' contains both of 'Flu Decline' and 'Flu Vaccine' in the date range given, then I need the count AS DeclineVac.

If 'VisitReason' contains 'Flu Decline' but no 'Flu Vaccine' in the date range given, then I need the count AS DeclineNoVac.

I don't have outputs from 'AcceptVac' and 'DeclineVac'. I know there are qualifying records though.

Please advise.

thx so much

select
a.Dept,
COUNT(CASE WHEN d.VisitReason ='Flu Accept'
and d.VisitReason ='Flu Vaccine'
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS AcceptVac,
COUNT(CASE WHEN d.VisitReason ='Flu Accept'
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS AcceptNoVac,
COUNT(CASE WHEN d.VisitReason ='Flu Decline'
and d.VisitReason ='Flu Vaccine'
and visitdate between '01-01-2008' and '12-31-2008' THEN 1 END)
AS DeclineVac,
COUNT(CASE WHEN d.VisitReason ='Flu Decline'
and visitdate between '01-01-2008' and '12-31-2008'
THEN 1 END)
AS DeclineNoVac,
c.Assonum
from DailyLog d, AssociatePersonal a, CountAssociate c
where d.AssociateIDLog = a.AssociateID
and Dept !=''
and c.DeptName = a.dept
and c.DeptDate = convert(varchar, GETDATE(), 101)
and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))
group by a.Dept, c.Assonum
order by a.Dept

 

[gmmastros]

I'm glad I was able to help.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom