CODEWITHSUNDEEP
sqlsql-servert-sql
white fang
white fang

Reputation: 135

How to determine difference in month between rows in a way like crossing

I'm creating a report where I need to calculate the difference between two dates in different rows in a way like cross to cross. How can I achieve this result with the following data:

CREATE TABLE #Customers (
  customerid INT,
  issuedate DATE,
  statusdate date
)

INSERT INTO #Customers
SELECT 928, '2017-07-24',   '2018-01-22'
union
SELECT 928, '2018-04-05',   '2018-10-05'
union
SELECT 928, '2019-02-21',   '2019-01-21'

--The Result should be like this "Displaying the difference between '2018-01-22' and '2018-04-05'
--And difference between '2018-20-05' and '2019-02-21'

DROP TABLE #Customers

I expect the result from the query to display the difference in months between columns 'statusdate' and 'issuedate' giving an output of 3 and 4

SELECT DATEDIFF(MONTH,'2018-01-22','2018-04-05')
UNION
SELECT DATEDIFF(MONTH,'2018-10-05','2019-02-21')

Upvotes: 2

Views: 79

Answers (2)

Qirel
Qirel

Reputation: 26450

Use the LAG() function to get the value from the previous row, and check where that value is not null. We use a sub-select to fetch the values, as the LAG() functions cannot be used in a WHERE clause.

SELECT DATEDIFF(MONTH, T.PreviousStatusDate, T.issuedate) as difference
FROM (
  SELECT LAG(c.statusdate) OVER(ORDER BY c.statusdate) as PreviousStatusDate,
         c.issuedate
  FROM Customers c
) AS T
WHERE T.PreviousStatusDate IS NOT NULL

Result,

difference
----------------
3
4

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

If I understand correctly and you want to get the difference between values from current and subsequent rows, next approach using LEAD() may help:

SELECT DATEDIFF(MONTH, statusdate, nextissuedate) AS [Difference]
FROM (
   SELECT 
      customerid,
      issuedate,
      statusdate,
      LEAD(issuedate) OVER (PARTITION BY customerid ORDER BY issuedate) AS nextissuedate
   FROM #Customers     
) t
WHERE nextissuedate IS NOT NULL

Output:

----------------
Difference
----------------
3
4

Upvotes: 1

Related Questions