I have a question based on the below table -

I would like to subtract dates from 2 different columns - minimum of Inception Date i.e. 12/06/2016 and Timestamp where the cancel flag is Yes i.e. 03/03/2021 to get the number of days.

So, 12/06/2016 - 03/03/3021.

How can I achieve this?

Thank you!

Edit: The above table is just a snippet and I have a lot more rows in the original table - more than 3000.

CodePudding user response：

If I understand the question correctly, the following statement is an option:

SELECT
   ProductID,
   MIN(InceptionDate) AS InceptionDate,
   MAX(CASE WHEN Cancel = 'Yes' THEN [Timestamp] END) AS [Timestamp],
   DATEDIFF(
     day, 
     MIN(InceptionDate), 
     MAX(CASE WHEN Cancel = 'Yes' THEN [Timestamp] END)
   ) AS Difference
FROM (VALUES
   ('No',  120, '2020-08-12', '2018-02-02'),
   ('No',  120, '2016-06-12', '2016-02-10'),
   ('No',  120, '2017-02-12', '2021-01-15'),
   ('Yes', 120, '2021-02-12', '2021-03-03')
) v (Cancel, ProductID, InceptionDate, [Timestamp])
GROUP BY ProductID

Result:

CodePudding user response：

Try this one.

   with cte AS (
select *,           
       MIN(InceptionDate) OVER(PARTITION BY ProductID) AS MinInceptionDate
from product
)
SELECT *,
       DATEDIFF(dd, MinInceptionDate, timestamp) AS Diff
FROM cte 
WHERE cancel = 'Yes'

Demo