Case Always?

• Ben Sullins-437405

  SSCertifiable

  Points: 5397

  More actions

  March 13, 2008 at 2:59 pm

  #71461

  Hey everyone,

  I have a challenge here where I have a query that returns aggregated results and categorizes them using a case statement. I found out from the business today that the results of this query (which are presented through a SSRS Matrix Object) need to return results for all categories in my case statement. Let me explain better w/ an example...

  SELECT

  CASE

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 1 AND 30 THEN '1-30'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 31 AND 45 THEN '31-45'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) >45 THEN 'Over 45'

  END [DaysAged],

  COUNT(*)

  FROM

  Table1

  GROUP BY

  CASE

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 1 AND 30 THEN '1-30'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 31 AND 45 THEN '31-45'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) >45 THEN 'Over 45'

  END

  So with this query I get counts for by [DaysAged] but if there are no matching records for one of the [DaysAged] categories the business user wants to see 0 for that category.

  Any ideas?

  Thanks,

  Ben

  ---

  Cheers,

  Ben Sullins
  bensullins.com
  Beer is my primary key...

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  March 13, 2008 at 10:17 pm

  #789438

  If you don't mind the "pivot"...

  SELECT

  SUM(CASE WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 1 AND 30 THEN 1 ELSE 0 END) AS [1-30],

  SUM(CASE WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 31 AND 45 THEN 1 ELSE 0 END) AS [31-45],

  SUM(CASE WHEN DATEDIFF(dd, AsOfDate, GETDATE()) > 45 THEN 1 ELSE 0 END) AS [Over 45]

  FROM

  Table1

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mrpolecat

  SSCertifiable

  Points: 7014

  More actions

  March 14, 2008 at 3:00 pm

  #789913

  why not just add else '0'

  SELECT

  CASE

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 1 AND 30 THEN '1-30'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 31 AND 45 THEN '31-45'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) >45 THEN 'Over 45'

  else '0'

  END [DaysAged],

  COUNT(*)

  FROM

  Table1

  GROUP BY

  CASE

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 1 AND 30 THEN '1-30'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) BETWEEN 31 AND 45 THEN '31-45'

  WHEN DATEDIFF(dd, AsOfDate, GETDATE()) >45 THEN 'Over 45'

  else '0'

  END

  ---