How to create a case statement on max expiry date

• daryl.mclean

  SSC Enthusiast

  Points: 153

  More actions

  March 5, 2009 at 8:07 pm

  #201033

  How can i create a CASe statement which will search for a maximum expiry date and update the year fiield

  e.g

  Induction No Acccess Code Expiry Date

  a2345 561 01-01-2001

  a2345 561 01-01-2003

  b2345 561 01-01-2001

  b2345 561 01-01-2006

  b2345 563 01-01-2001

  b2345 563 01-01-2003

  The rules are

  select the latest expiry date for each different type of access code, then update the year by 3 for 2006 expiry dates and 6 years update for 2003 expiry dates

  I would hope to see results as

  a2345 561 01-01-2009

  b2345 561 01-01-2009

  b2345 563 01-01-2009

  Complicated i know but any help would be appreciated

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  March 6, 2009 at 9:21 am

  #955005

  This will work without a CASE and will change any year to 2009 if the MAX(expiry_date) is not 2003 or 2006.

  USE tempdb;

  GO

  DECLARE @test TABLE(induction_no CHAR(5), access_code INT, expiry_date SMALLDATETIME)

  INSERT INTO @test (

  induction_no,

  access_code,

  expiry_date

  )

  SELECT

  'a2345', 561 , '01-01-2001'

  UNION ALL

  SELECT

  'a2345', 561 , '01-01-2003'

  UNION ALL

  SELECT

  'b2345' , 561 , '01-01-2001'

  UNION ALL

  SELECT

  'b2345' , 561, '01-01-2006'

  UNION ALL

  SELECT

  'b2345' , 563, '01-01-2001'

  UNION ALL

  SELECT

  'b2345' , 563 , '01-01-2003'

  SELECT

  induction_no,

  access_code,

  DATEADD(YEAR, DATEDIFF(YEAR, MAX(expiry_date), '1/1/2009'), MAX(expiry_date))

  FROM

  @test

  GROUP BY

  induction_no,

  access_code

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question