CODEWITHSUNDEEP
mysqlsqlstored-procedures
mimaku
mimaku

Reputation: 37

How to calculate the difference between two dates in stored procedure for multiple sets in rows

I have a data table like this 

id  typeid      date
12  exited      01-06-2017    
1   approved    05-06-2017
7   attended    08-06-2017
9   admitted    10-06-2017
45  approved    12-06-2017
67  admitted    16-06-2017

The answer I want would be something like this:

difference(days)
5 
4

I want to calculate the date difference between approved and admitted (wherever they are, so I think we have to use looping statement). I want to write a stored procedure in MySql (version: 5.6) which returns the result in any form (maybe a table having these results).

Upvotes: 0

Views: 1127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

If you are concerned about performance, you can assign a value to each row which is the cumulative number of "admitted". Then use this for aggregation:

select max(case when typeid = 'approved' then date end) as approved_date,
       max(case when typeid = 'admitted' then date end) as admitted_date,
       datediff(max(case when typeid = 'admitted' then date end),
                max(case when typeid = 'approved' then date end)
               ) as diff
from (select t.*,
             (@cnt := @cnt + (typeid = 'approved')) as grp
      from (select t.* from t order by date) t cross join
           (select @cnt := 0) params
     ) t
group by grp;

This can take advantage of an index on (date) for assigning grp. Then it just needs to do a group by.

Using a correlated subquery can become quite expensive as the size of the data grows. So for larger data, this should be much more efficient.

In either case, using window functions (available in MySQL 8+) is much, much the preferred solution.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521579

This is actually the sort of problem for which window functions are very well suited, but since you are using version 5.6, this isn't a possibility. Here is one way to do this:

SELECT
    DATEDIFF(
        (SELECT t2.date FROM yourTable t2
         WHERE t2.typeid = 'admitted' AND t2.date > t1.date
         ORDER BY t2.date LIMIT 1),
        t1.date) AS difference
FROM yourTable t1
WHERE
    typeid = 'approved'
ORDER BY
    date;

The logic in the above query is that we restrict only records which are approved type. For each such records, using a correlated subquery, we then seek ahead and time and find the nearest record which is admitted type. Then, we take the difference between those two dates.

Check the working demo link below.

Demo

Upvotes: 1

Related Questions