SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Count result of case expression
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

siweltj
Starting Member

3 Posts

Posted - 01/31/2013 :  15:46:50  Show Profile  Reply with Quote
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

Edited by - siweltj on 01/31/2013 17:28:09

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 01/31/2013 :  16:36:09  Show Profile  Reply with Quote
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 - 01/31/2013 :  16:50:40  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/01/2013 :  02:41:03  Show Profile  Reply with Quote
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 - 02/01/2013 :  11:18:25  Show Profile  Reply with Quote
Thanks to all I got what I needed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000