Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Records not qualfying with CASE 1

Status
Not open for further replies.

gatetec

MIS
Joined
Mar 22, 2007
Messages
420
Location
US
// 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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top