Date Calculation with a Case Statement in a Query

• sqlguru1day

  Old Hand

  Points: 356

  More actions

  July 1, 2020 at 6:01 am

  Go to Answer

  #3766610

  Hi all,

  Can someone please help with a easy one here. Thanks.

  The Below Query Returns : 123456 | 01/04/2020 for example.

  __________________________________________

  SELECT

  ID, CONVERT(VARCHAR,E.[Start Date],103) AS [Start Date]

  FROM

  EVENTS AS E

  ____________________________________

  • I want to add the below columns with a Case Statement built in it.

  [Days From Start Date], [Weeks From Start Date],

  CASE [Week From Start Date]

  WHEN [Week From Start Date] >=0 THEN 'Yes - Started'

  WHEN [Week From Start Date] >=-1 THEN 'Within 1 Week'

  WHEN [Week From Start Date] > = -2 THEN 'Within 2 Weeks'

  WHEN [Week From Start Date] >= -4 THEN 'Within 4 Weeks'

  ELSE 'Outside 4 weeks '

  END

  FROM Events AS E

• DinoRS

  SSCrazy

  Points: 2683

  More actions

  July 1, 2020 at 7:29 am

  Answer

  #3766616

  Have a look at datediff 

  your case statement will probably look something like this:

   

  CASE 
  WHEN DATEDIFF(week,E.[Start Date], GETDATE()) >=0 THEN 'Yes - Started'