Odd issue with using CASE

[gatetec]

I have this query, and it works fine other than the result for 'CntElseV'

select
sum (CASE WHEN StatusColumn ='AcceptVac' THEN 1 ELSE 0 END ) AS CntAccV,
sum (CASE WHEN StatusColumn ='AcceptNoVac' THEN 1 ELSE 0 END ) AS CntAccNV,
sum (CASE WHEN StatusColumn ='DeclineVac' THEN 1 ELSE 0 END ) AS CntDecV,
sum (CASE WHEN StatusColumn ='DeclineNoVac' THEN 1 ELSE 0 END ) AS CntDecNV,
sum (CASE WHEN StatusColumn ='JustVac' THEN 1 ELSE 0 END ) AS CntJustV, 
sum (CASE WHEN StatusColumn ='ElseVac' THEN 1 ELSE 0 END ) AS CntElseV
from 
  (Select AssociateIdLog,
       Case 
            When FluAccept  > 0 And FluVaccine > 0 Then 'AcceptVac'
            When FluAccept  > 0 And FluVaccine = 0 Then 'AcceptNoVac'
            When FluDecline > 0 And FluVaccine > 0 Then 'DeclineVac'
            When FluDecline > 0 And FluVaccine = 0 Then 'DeclineNoVac'
            when FluDecline = 0 And FluAccept = 0 And FluVaccine > 0 Then 'JustVac'
            when FluElse > 0 Then 'ElseVac'
            End StatusColumn
   from   (
       Select AssociateIdLog,
              Count(Case When VisitReason = 'Flu Accept' Then 1 End) As FluAccept,
              Count(Case When VisitReason = 'Flu Vaccine' or VisitReason = 'Flu Vaccine Urgicare' or VisitReason = 'Flu Vaccine Elsewhere' Then 1 End) As FluVaccine,
              Count(Case When VisitReason = 'Flu Decline' Then 1 End) As FluDecline,
	      Count(Case When VisitReason = 'Flu Vaccine Elsewhere' Then 1 End) As FluElse
       from   DailyLog 
       Where  VisitDate between '01/01/2008' and '12/31/2008'
       Group By AssociateIdLog
          ) As AliasForCount
  ) as d
     Inner Join AssociatePersonal a
        On d.AssociateIDLog = a.AssociateID
      Inner Join CountAssociate c
        On c.DeptName = a.dept
where --a.Dept !=''  and 
       c.DeptDate = convert(varchar, GETDATE(), 101)
      and CONVERT(VARBINARY, c.Deptname) = CONVERT(VARBINARY, UPPER(c.Deptname))

The issue is that this segment is not pulling out the count correctly, which should have pulled out 43 records.

sum (CASE WHEN StatusColumn ='ElseVac' THEN 1 ELSE 0 END ) AS CntElseV
when FluElse > 0 Then 'ElseVac'
Count(Case When VisitReason = 'Flu Vaccine Elsewhere' Then 1 End) As FluElse

This query pulls out 43 records.

select count(1)
from dailylog
where VisitReason = 'Flu Vaccine Elsewhere'
      and VisitDate between '01/01/2008' and '12/31/2008'

I suspect that this odd issue has something to do with Count(Case When VisitReason = 'Flu Vaccine' or VisitReason = 'Flu Vaccine Urgicare' or VisitReason = 'Flu Vaccine Elsewhere' Then 1 End) As FluVaccine
in one way or the other.

The reason is that if I modify it to
Count(Case When VisitReason = 'Flu Vaccine' or VisitReason = 'Flu Vaccine Urgicare' Then 1 End) As FluVaccine

'CntElseV' gives me 10 records, but 10 records are the only ones I can get...

Regardless, I have to keep

Count(Case When VisitReason = 'Flu Vaccine' or VisitReason = 'Flu Vaccine Urgicare' or VisitReason = 'Flu Vaccine Elsewhere' Then 1 End) As FluVaccine

for the correct count for 'FluVaccine'

What would be causing this issue and how do you correct this?

Please advise.

Thank you

 

[markros]

In SQL Server 2005 you can use CTE. If you try to write each of your inner select statements as CTE and view results, would it be correct?

 

[Alan0568]

Are there any null values involved? Have you considered the effect of null values for compare

A