I want to have column where the value of a subtraction will get stored. Subtraction should happen between value of a column and previous value of another column.

Below is the example:

Here we have customer ID, for each customer ID the gap should be calculated like shipdate-previous supplydate.. just like 2nd ship date 12/6/2018 minus previous supply date that is 12/10/2018, so gap should be -4.

You can refer to the above link to get the sample data..

CodePudding user response：

You can use lead() windows function. Assuming that you are using SQL Server.

Create table and insert statements:

 create table customer_shipping(Customer_ID int, Ship_date date, Supply_date date);
 insert into customer_shipping values(76868773  ,'10/15/2018 0:00','12/10/2018 0:00');
 insert into customer_shipping values(76868773  ,'12/06/2018 0:00','01/31/2019 0:00');
 insert into customer_shipping values(76868773  ,'02/21/2019 0:00','04/18/2019 0:00');
 insert into customer_shipping values(76868773  ,'04/25/2019 0:00','06/20/2019 0:00');
 insert into customer_shipping values(76868773  ,'06/27/2019 0:00','08/22/2019 0:00');
 insert into customer_shipping values(76868773  ,'08/29/2019 0:00','10/24/2019 0:00');
 insert into customer_shipping values(76868773  ,'11/26/2019 0:00','01/21/2020 0:00');
 insert into customer_shipping values(76868773  ,'01/30/2020 0:00','03/26/2020 0:00');
 insert into customer_shipping values(76868773  ,'04/06/2020 0:00','06/01/2020 0:00');
 insert into customer_shipping values(76868773  ,'06/01/2020 0:00','07/27/2020 0:00');
 insert into customer_shipping values(76868773  ,'07/29/2020 0:00','09/23/2020 0:00');

Query:

 select *, datediff(day,supply_date,lead(ship_date)over(partition by customer_id order by ship_date))gap 
 from customer_shipping

Output:

db<>fiddle here

CodePudding user response：

If your database doesn't support LEAD and LAG (earlier versions of MS SQL don't) then you can always use the RANK() OVER() function to give each a unique RANK number and then join the table to itself using an offset on the RANK of 1