0

I'm looking for a way to make each SUM exclusive, i.e., it doesn't count one record more than once. You might think that with the way the date ranges are set up, it wouldn't count more than once, but in this particular database application the dates are very peculiar. TotalCount is correct, but I'm getting 2 additional records for Count1 and Count2. I have tried wrapping the SUMs in an outer CASE, but have not been able to get it to work. Any ideas?

select 
 Sum(case   --'Before Admission'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 1 else 0 
 end) as Count0,

 Sum(case   --'Within 24 Hours'
 when ((EvalCheckboxDate >= UnitArrivalTime and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0 
 end) as Count1,

 Sum(case  --'Between 24 and 48 Hours'
 when ((EvalCheckboxDate > dateadd(hh,24,UnitArrivalTime) and EvalCheckboxDate <= dateadd(hh,48,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count2,

 Sum(case   --'Greater than 48 Hours'
 when (EvalCheckboxDate > dateadd(hh,48,UnitArrivalTime) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count3,

 Sum(case   --'Recorded as Not Done'
 when m_value='No' then 1 else 0
 end) as Count4,

 Sum(case   --'Not Recorded'
 when m_value is null then 1 else 0
 end) as Count5,  
--
count(*) as TotalCount
from TFDataset
1
  • I'm not sure about your extra rows but I am curious about this: (m_value<>'No' or m_value is not null). What are you expecting from that? The or m_value is not null part is going to give you a true for any non-null value which kind of makes the m_value <> 'No' part rather pointless. Unless I'm missing something? Commented May 4, 2017 at 13:10

2 Answers 2

2

Here's one way to make them mutually exclusive:

select count(case X when 'A' then 1 end)
     , count(case X when 'B' then 1 end)
     , ...
from (    
  select 
    case   --'Before Admission'
    when ((EvalCheckboxDate < UnitArrivalTime) 
     and (m_value<>'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate >= UnitArrivalTime 
     and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) 
     and (m_value<>'No' or m_value is not null)) then 'B'  
    when ...
    [...]
    end as X
  from TFDataset
) as T

I.e. the idea is to use one case stmt to classify each row and then count the amount of each one. Thye code will be a bit simpler if you can live with something like:

select X, count(1)
from (    
  select 
    case   --'Before Admission'
    when ((EvalCheckboxDate < UnitArrivalTime) 
     and (m_value<>'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate >= UnitArrivalTime 
     and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) 
     and (m_value<>'No' or m_value is not null)) then 'B'  
    when ...
    [...]
    end as X
  from TFDataset
) as T
group by X

and then transpose the result in the application

0
2

I actually like Lennart's answer, personally. However, if you want a minimal change to existing code, you could do this:

select 
 Sum(case   --'Before Admission'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 1 else 0 
 end) as Count0,

 Sum(case   --'Within 24 Hours'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 0 
 when ((EvalCheckboxDate >= UnitArrivalTime and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0 
 end) as Count1,

 Sum(case  --'Between 24 and 48 Hours'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 0 
 when ((EvalCheckboxDate >= UnitArrivalTime and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 0 
 when ((EvalCheckboxDate > dateadd(hh,24,UnitArrivalTime) and EvalCheckboxDate <= dateadd(hh,48,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count2,

 Sum(case   --'Greater than 48 Hours'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 0 
 when ((EvalCheckboxDate >= UnitArrivalTime and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 0 
 when ((EvalCheckboxDate > dateadd(hh,24,UnitArrivalTime) and EvalCheckboxDate <= dateadd(hh,48,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 0
 when (EvalCheckboxDate > dateadd(hh,48,UnitArrivalTime) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count3,

 Sum(case   --'Recorded as Not Done'
 when m_value='No' then 1 else 0
 end) as Count4,

 Sum(case   --'Not Recorded'
 when m_value is null then 1 else 0
 end) as Count5,  
--
count(*) as TotalCount
from TFDataset

Since some things are being caught by multiple possible branches of the case, simply make sure that all "previous" branches are included, and the value is set to zero on those. I'd probably actually include all 4 date lines in each date-related CASE statement.

1
  • Interesting perspective!
    – rdbdba
    Commented May 4, 2017 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.