// 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
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