Need to calculate [ExpirationDate] based on next record's [EffectiveDate]-1

• Hell-1934

  Old Hand

  Points: 333

  More actions

  April 10, 2020 at 9:57 pm

  #3742120

  Hi All,

  I have the following simple query, and the resulting table:

  SELECT 
        [Client_ID],
        [ClientName],
        [EffectiveDate],
        [MedConditionID],
        [MedCondition]
  
  FROM
        [Medications]

  

   

  I need to populate  [ExpirationDate] field - which is based on next record's [EffectiveDate] field - 1.

  For example - if effective dates are  '1/16/2019', '10/16/2019' then the [ExpirationDate] for '1/16/2019'= '10/15/2019', etc.

  For the biggest effective date (in this table it's '3/18/2020'), expiration date should always be = '2050/12/31'

  Please see updated table below (how I need to populate [ExpirationDate], I highlighted the 1st 2 examples):

  

   

  Plz, help with any suggestions

  I was thinking about some Lag() / Lead() functions that will compare records between each other, but not sure

  Thank you!

   

   

  • This topic was modified 4 years, 7 months ago by  Hell-1934.
  • This topic was modified 4 years, 7 months ago by  Hell-1934.
  • This topic was modified 4 years, 7 months ago by  Hell-1934.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  April 10, 2020 at 10:58 pm

  #3742133

  Be brave. try it. LAG() looks at the previous record in a window (defined by the PARTITION clause) and LEAD() looks at the "next" record.