Sql query for getting difference beteen different columns of different rows

Question

I have different records with columns Start Date and End Date. If I sort records in ascending order of start date. and want to find difference between start date of one row and end date of previous row consecutively.

e.g.
Table: Data
------------
Date1           Date2           
13-DEC-2011     15-DEC-2011      
18-DEC-2011     16-DEC-2011     
21-DEC-2011     24-DEC-2011

one more query if I have a third column say ID and I want difference grouped by these ID's e.g

              ID          Date1             Date2           
              1     13-DEC-2011     15-DEC-2011      
              1     18-DEC-2011     16-DEC-2011     
              2     21-DEC-2011     24-DEC-2011  
              2     25-JAN-2012     25-FEB-2012
              2     29-FEB-2012     25-MAR-2012


              and I need :  


             ID INTERVAL FREE
              1 15 DEC to 18 DEC
              2 24dec to 25 jan;25 feb to 29 feb

Take a look at this answer of mine. Might be helpful. http://stackoverflow.com/questions/11262260/finding-free-blocks-of-time-in-mysql-and-php — Muhammad Raheel, Dec 05 '12 at 13:18

valex · Accepted Answer · 2012-12-05T18:11:17.423

Here is SQLFiddle demo

with t1 as
(
select t.*,
        row_number() over (order by date1) rn 
        from t
)
select t1.date1 as d1,t1.Date2 as d2 ,
t2.Date2 as PreviousDate2,
t1.Date1-t2.Date2 as DIff
from t1
left join t1 t2 on t1.rn=t2.rn+1
order by t1.rn

Here is a query to answer your edited question:

SQLFiddle DEMO

If you need to gather rows for each ID in one comma separated line you should do it on the client side not in SQL.

with t1 as
(
select t.*,
        row_number() over (partition by id order by date1) rn 
        from t
)
select t1.id,
t2.Date2 as PreviousDate2,
t1.date1 as d1
from t1
left join t1 t2 on (t1.rn=t2.rn+1) and (t1.id=t2.id)
where t2.Date2 is not null
order by t1.id,t1.rn

I've added a query for the second part of your question – valex Dec 06 '12 at 07:07 — valex, Dec 06 '12 at 07:07

score 0 · Answer 2 · answered Dec 05 '12 at 13:31

Try this ::

SELECT 
DATEDIFF(temp1.from, temp2.to) as diff
from
(
SELECT t.DATE1 as from, 
       @rownum := @rownum + 1 AS rank1
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r
order by Date1
) as temp1
inner join 

(
SELECT t.DATE2 as to, 
       @rownum := @rownum + 1 AS rank2
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 1) r
order by Date1
) as temp2 on (temp1.rank1=temp2.rank2)

Sql query for getting difference beteen different columns of different rows

2 Answers2