Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Count result of case expression

Author  Topic 

siweltj
Starting Member

3 Posts

Posted - 2013-01-31 : 15:46:50
I am trying to count the results of the if statement in the query below. Just a bit out of my reach...?


SELECT b.ldate, c.lastname, c.firstname, ((e.schtime/60)/60) as "Hour", e.schtime, e.actualarrivetime, ((e.actualarrivetime-e.schtime)/60) as "Min Past",     

case when ((e.actualarrivetime-e.schtime)/60) > 30 then 'Late'
when ((e.actualarrivetime-e.schtime)/60) < 0 then 'Early'
else 'On-Time' end 'OTP',

b.SubTypeAbbr, e.schedstatus, es.evstrname FROM clients c, booking b, bookinglegs bl, events e, eventstrings es WHERE e.schedstatus in (1, 2, 3, 4, 20, 21) AND c.clientid = b.clientid AND b.bookingid = bl.bookingid AND bl.legid = e.legid AND e.evstrid=es.evstrid AND e.schid=es.schid AND e.schid>=? AND e.schid<=?AND e.schtime > 0 AND e.esttime > 0 order by b.ldate, e.schtime

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 16:36:09
I don't see an if statement in the query - did you mean the case expression perhaps? What should the result look like? Here is one way of counting, if you are looking for counts grouped by lastname and firstname. Take a look at this, and if this is not what you are looking for, can you post a sample of the expected output data?
SELECT
lastname,
firstname,
SUM(CASE WHEN 'OTP' = 'Late' THEN 1 ELSE 0 END) AS LateCount,
SUM(CASE WHEN 'OTP' = 'Early' THEN 1 ELSE 0 END) AS EarlyCount,
SUM(CASE WHEN 'OTP' = 'On-Time' THEN 1 ELSE 0 END) AS OnTimeCount
FROM(
SELECT b.ldate, c.lastname, c.firstname, ((e.schtime/60)/60) as "Hour", e.schtime, e.actualarrivetime, ((e.actualarrivetime-e.schtime)/60) as "Min Past",

case when ((e.actualarrivetime-e.schtime)/60) > 30 then 'Late'
when ((e.actualarrivetime-e.schtime)/60) < 0 then 'Early'
else 'On-Time' end 'OTP',

b.SubTypeAbbr, e.schedstatus, es.evstrname FROM clients c, booking b, bookinglegs bl, events e, eventstrings es WHERE e.schedstatus in (1, 2, 3, 4, 20, 21) AND c.clientid = b.clientid AND b.bookingid = bl.bookingid AND bl.legid = e.legid AND e.evstrid=es.evstrid AND e.schid=es.schid AND e.schid>=? AND e.schid<=?AND e.schtime > 0 AND e.esttime > 0
) s
GROUP BY
lastname,
firstname
Go to Top of Page

siweltj
Starting Member

3 Posts

Posted - 2013-01-31 : 16:50:40
James,

Yes I am referring to the case expression. I trying to get the result as follows:

EarlyCount ##
OnTimeCount ##
LateCount ##
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-01 : 02:41:03
what are datatypes of e.actualarrivetime,e.schtime etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

siweltj
Starting Member

3 Posts

Posted - 2013-02-01 : 11:18:25
Thanks to all I got what I needed.
Go to Top of Page
   

- Advertisement -