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

  • Hi All,

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

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

    FROM
    [Medications]

    Capture

     

    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):

    Capture1

     

    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 3 years, 11 months ago by  Hell-1934.
    • This topic was modified 3 years, 11 months ago by  Hell-1934.
    • This topic was modified 3 years, 11 months ago by  Hell-1934.
  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply