SQL - Lead function to get dates from next row

I have a table with a date and value for example:

    AccID    Date          Value
    1      01/01/2007      10
    1      01/02/2008      20
    1      01/03/2009      40

I want to create a new table that starts from the date in row 1 and ends with the date in row 2 and so on.....for example

    AccID    Date           DateEnd           Value
      1      01/01/2007     01/02/2008          10
      1      01/02/2008     01/03/2009          20
      1      01/03/2009     01/04/2050          40


  Select
       date,
       isnull(lead(date) over (partition by accID order by date), '01/04/2050') as DateEnd,
       value
  from column A

     

I have tried this code but I can't seem to get the correct output. This is the output I am currently getting

     AccID    Date           DateEnd           Value
      1      01/02/2008     01/02/2009          20
      1      01/02/2009     01/03/2007          40
      1      01/01/2007     01/04/2050          10

1 answer

  • answered 2022-05-04 10:51 CHill60

    You are not getting any output, you are getting an error

    Incorrect syntax near 'partition'.

    You get the correct results when you correct the error

     Select
           date,
           isnull(lead(date) over (partition by accID order by date), '01/04/2050') as DateEnd,
           value
      from column A
    

    Edit: Do yourself some favours and apply the following:

    • Avoid the use of reserved words or if you "must" use them, surround the column or table name with [ ] e.g. [date], [value],[column]
    • Research the function you're going to use to make the most of what it has to offer, to simplify your queries e.g. See the documentaion for Lead
    • Use date formats that are unambiguous e.g. '2050-04-01' in preference to '01/04/2050'. The latter could be either 1st April or 4th January depending on where in the world you happen to be

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum