CODEWITHSUNDEEP
sqlteradata
smileyface
smileyface

Reputation: 51

Create new column based on some conditions in SQL

I have the following table. Let's call it orders. I would like to add a new column to this existing table which calculates the days apart from the first order date by the customerid. If there are 0 days apart from the minimum sold date, then it should be 0.

From this 

customerid orderdate
1          1/21/2018
1          1/21/2018
1          2/21/2018
1          5/22/2018
2          3/22/2018
3          4/5/2018
3          4/5/2018

to this

customerid orderdate daysapart
1          1/21/2018         0
1          1/21/2018         0
1          2/21/2018        30
1          2/21/2018       123
2          3/22/2018         0
3          4/5/2018          0
3          4/5/2018          0

Upvotes: 1

Views: 71

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Here is one approach, using a correlated subquery:

SELECT
    t1.customerid,
    t1.orderdate,
    t1.orderdate - (SELECT MIN(t2.orderdate)
                    FROM your_table t2
                    WHERE t1.customerid = t2.customerid) daysapart
FROM your_table t1;

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

Using a Windowed Aggregate:

select customerid, orderdate,
   orderdate - min(orderdate) over (partition by customerid) as daysapart
from mytab

Upvotes: 1

Related Questions