Create a new column which shows the last enquiry date based on ID using SQL

I have the EnquiryDate and the ID column. I am trying to create the ‘previous_EnquiryDate column.

Previous enquiryDate is based on ID. For each ID, when is the most recent enquiry that came in before the current enquiry.

Example in row 1, this is the first enquiry by this ID, so previous enquiryDate is the same as the EnquiryDate. In row 9, this ID has enquired before on 2/11/2019 so this is the previous enquiryDate.

Row EnquiryDate ID  Previous enquiryDate
1   7/20/2019   14148   7/20/2019
2   3/17/2019   14169   3/17/2019
3   6/9/2019    14169   3/17/2019
4   3/23/2019   14217   3/23/2019
5   4/25/2019   14229   4/25/2019
6   1/16/2019   14286   1/16/2019
7   5/9/2019    14295   5/9/2019
8   2/11/2019   14333   2/11/2019
9   4/3/2019    14333   2/11/2019
10  5/20/2019   14333   4/3/2019
11  6/24/2019   14333   5/20/2019
12  4/8/2019    14343   6/24/2019

I just started using SQL recently and have no clue how this can be done or if this can even be done on SQL.

Answer

You don’t say which database you are using so I’ll assume yours supports windows functions; most of them do nowadays.

The following query shows the date you want:

select
  row,
  enquirydate,
  id,
  case when prev is null then enquirydate else prev end as previous_EnquiryDate
from (
  select *,
    lag(enquirydate) over(partition by id order by enquirydate) as prev
  from t
) x

Advertisement

Answer