Search code examples
sql-serverdense-rank

Using RANK() or DENSE_RANK() to get the next value by Rank

I have data within a SQL Server table where there is a single date column. This date is a "Transfer Date" for each LocationID in my database. I need to write a query where the TransferDate for one CustomerID becomes the StopDate. Additionally, the same TransferDate also becomes the StartDate for the next CustomerID in order of date. I could perhaps use RANK() or DENSE_RANK and OVER to order the results by LocationID and TransfDate. My question is how do I use RANK() or any other method to fetch the "next" TransferDate in my table as the StartDate for the next CustomerID.

Here are screenshots of the data in the table (Table1) and my desired result. The last value in the Table1 screenshot is a derived value from a related table to Table1 for which I am using a join to fetch that one additional row.

LocationID  CustomerID  TransferDate
------------------------------------
500007      109160      12/29/2009
500007      109160      2/9/2018
500007      99999999    2/13/2018
500007      100001      3/30/2018
500007      129615      4/8/2019
500007      100001      5/16/2019
500007      99999999    5/17/2019
500007      131458      12/31/9999

Desired Result

Results after using LEAD() and LAG(): enter image description here

Working DDL is below. One row with Customer '111706' is in the base table but my final query includes several other joins which results in this row being eliminated. Will that cause on issue? I am not sure when LEAD() or LAG() actually execute. I feel they execute before my join, rendering the rest of the dataset problematic.

create table Table1(
  Location char(15),
  Customer varchar(15),
  TransferDate datetime
  )
  
  insert into Table1 
  values
  ('500007','111706','2009-12-29'),
  ('500007','109160','2009-12-29'),
  ('500007','109160','2018-02-09'),
  ('500007','VACANT','2018-02-13'),
  ('500007','100001','2018-03-30'),
  ('500007','129615','2019-04-08'),
  ('500007','100001','2019-05-16'),
  ('500007','VACANT','2019-05-17');
  
select Location, Customer, TransferDate,
LAG(TransferDate,1,TransferDate) over (order by Location,TransferDate) as StartDate,
LEAD(TransferDate,1) over (order by Location,TransferDate) as StopDate
from Table1
where Location='500007'`
Solution

  • I was able to use the suggestion by @GarethD to resolve my issue. In my case, LAG() was the answer as I already had the next date available. Thanks for his help with this. I found some good examples here.