Case statement won't work in grouping

• HakunaMatata

  SSCrazy

  Points: 2610

  More actions

  November 1, 2010 at 2:42 pm

  #228468

  Hi,

  I have a table like below -

  create table #Temp(Code varchar(2),Description varchar(50),Time datetime)

  insert into #Temp(Code,Description,Time)

  (

  select 'XX','XXDescription','2010-10-01 15:14:00.000' UNION

  select 'XX','XXDescription','2010-10-02 14:12:00.000' UNION

  select 'XX','XXDescription','2010-10-03 14:14:00.000' UNION

  select 'XY','XYDescription','2010-10-01 15:14:00.000' UNION

  select 'XY','XYDescription','2010-10-02 14:11:00.000' UNION

  select 'XY','XYDescription','2010-10-03 14:13:00.000' UNION

  select 'XZ','XZDescription','2010-10-01 12:30:00.000' UNION

  select 'XZ','XZDescription','2010-10-02 12:31:00.000' UNION

  select 'XZ','XZDescription','2010-10-03 12:29:00.000' UNION

  select 'XZ','XZDescription','2010-10-04 12:20:00.000'

  )

  Now what I want to do is I want to select max time for each Code and also based on selected max time, if its before or after certain period, I want to show 'Success','Delay' kinda messages....

  I have written query like -

  select Code,Description,MAX(Time), Status = case when (Select MAX(Time) from #Temp where Code = 'XX') < '2010-10-02' then 'Success'

  when (Select MAX(Time) from #Temp where Code = 'XY') < '2010-10-05' then 'Success'

  else 'Delay' end

  from #Temp

  group by Code,Description

  This query says if max time for XX/XY code is less than specified Date/Time then show Status as Success else show as Delay...

  But the above query gives me all results as Success eventhough I should see some results as Delay.. Is there anything wrong with the query?

  :rolleyes:

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  November 1, 2010 at 2:53 pm

  #1245093

  Something like this, perhaps?

  SELECT

  Code,

  MaxTime,

  CASE WHEN CODE = 'XX' AND MaxTime >= '2010-10-02' THEN 'DELAY'

  WHEN CODE = 'XY' AND MaxTime >= '2010-10-05' THEN 'DELAY'

  ELSE 'SUCCESS' END AS SuccessDelay

  FROM

  (SELECT

  Code,

  MAX( [Time]) AS MaxTime

  FROM

  #Temp AS t

  GROUP BY

  Code

  ) AS drv

  Note the subquery. Get your grouping out of the way, then deal with your case/calculations.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• HakunaMatata

  SSCrazy

  Points: 2610

  More actions

  November 2, 2010 at 6:32 am

  #1245306

  Yep.. it worked fine.. 😀

  seems like grouping won't work with case statements...! :unsure:

  :rolleyes:

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 2, 2010 at 6:41 am

  #1245312

  Sacheen (11/2/2010)

  ---

  Yep.. it worked fine.. 😀

  seems like grouping won't work with case statements...! :unsure:

  No, they work fine together. This[/url] article shows how to use CASE with aggregation to perform high-speed crosstabs.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden