0

I've been searching for a solution to this but not managed to find it. We have a table listing person ID, application ID and application date. One person may have submitted multiple applications. Basically I am trying to generate a list that displays the previous application date (if any) for the person submitting the application - i.e. the date of any application that preceded the current one for that person. So the date is always relative to the current application date.

The new table would show person ID, application ID, application date, previous application date as set out in the following example. Note dates expressed DD/MM/YY

Original data:

Person Applic ID Applic Date
Fred 100 1/1/22
Steve 101 2/2/22
Dave 104 17/2/22
Fred 105 24/2/22
Steve 107 24/2/22
Fred 108 25/2/22

Procedure Returns:

Person Applic ID Applic Date Prev Applic Date
Fred 100 1/1/22 -
Steve 101 2/2/22 -
Dave 104 17/2/22 -
Fred 105 24/2/22 1/1/22
Steve 107 24/2/22 2/2/22
Fred 108 25/2/22 24/2/22

I have tried several solutions but most were simplified ie get the 2nd highest date for a chosen field (say person ID); in this case there is another step - we need the highest date that is lower than the existing date for the person ID involved in an application; I couldn't find anything that solved this

Thom A
  • 88,727
  • 11
  • 45
  • 75
gechsy
  • 1
  • 1
  • *"Note dates expressed DD/MM/YY"* nitpick, but they are actually in `d/m/yy`. Is this also how they are actually stored in your database as well, or are you actually properly using a `date`? – Thom A Feb 27 '23 at 12:22
  • 1
    Are you not just after `LAG` here? You state you tried several solutions; what *were* those attempts? Why didn't they work? – Thom A Feb 27 '23 at 12:26
  • yes correct d/m/yy. in my DB they are stored in format "2022-08-10 00:00:00.000" – gechsy Feb 27 '23 at 12:30
  • here is an example: https://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records – gechsy Feb 27 '23 at 12:32
  • As example of what, exactly? Why doesn't [`LAG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16) work here? – Thom A Feb 27 '23 at 12:33
  • please be patient as I am not an expert in sql by any stretch. the solutions I found were finding the latest record for a particular ID, but not a previous record based on a derivative field. As the data may not be in date order It is important it looks at the date rather than the order of the record – gechsy Feb 27 '23 at 12:34
  • Yes, and `LAG` does that. Tables don't have an order, you define the "last row" in `LAG` with an `ORDER BY` in the `OVER` clause. – Thom A Feb 27 '23 at 12:35
  • thanks for pointing me in the right direction. I will give that a go – gechsy Feb 27 '23 at 12:41
  • What SQL Server/Database version you are using? based on you can you may allow to User LAG function for SQL Server version 2012 and later can use the LAG function – Bhavesh Harsora Feb 28 '23 at 10:17
  • please mentioned what solutions you have used so far and what's the result which are not upto your expectations – Bhavesh Harsora Feb 28 '23 at 10:21

0 Answers0