CODEWITHSUNDEEP
mysqlsql
user2041357
user2041357

Reputation: 15

How to get the number of days between two dates

I have a situation where I need to find the number of days that have passed between two rows with date fields. The rows that the calculation needs to be made are not sorted.

Here is the structure of the table

Folio   DATE    

1       6/1/2015
2       4/1/2015
1       3/1/2015
4       2/1/2015
1       1/1/2015

Basically, I would need to sort by date and keep only the last two transactions grouped by folio. so in this example, the transaction by folio 1 on 1/1/2015 would be ignored.

Suppose that I need to do the following: 1. Group by folio number 2. only count the days between the last two transactions by folio. For example, folio #1 would only include the transactions from 6/1/2015 and 3/1/2015.

The result I'm looking for:

Folio   FirstDATE   LastDate   #ofDays

1       3/1/2015     6/1/2015    90

Any MySQL pros out there? My skills are still in newbie territory. Thank you!

UPDATE:

I've managed to come up with the following:

SELECT 
  SubQuery.`Folio Number`,
  SubQuery.LatestClosing,
  SubQuery.FirstClosing,
  DATEDIFF(SubQuery.LatestClosing, SubQuery.FirstClosing) AS numofdays
FROM (SELECT
    Subquery.`Folio Number`,
    SubQuery.LatestClosing,
    SubQuery.FirstClosing
  FROM (SELECT t.`Folio Number`,
      MAX(t.`Closing Date`) AS LatestClosing,
      (SELECT
          s.`Closing Date`
        FROM MLSFinalimport s
        WHERE t.`Folio Number` = s.`Folio Number`
        ORDER BY s.`Closing Date` DESC
        LIMIT 1, 1) AS FirstClosing,
    FROM MLSFinalimport t
    GROUP BY t.`Folio Number`) SubQuery) SubQuery

This is generating a result that looks like this:

LatestClosing     First Closing   numofdays
7/20/2016         5/9/2006         3725

This is what I need. However, I'm stuck trying to add the original column for each row called "Folio Number". How do I proceed?

Thank you very much.

Upvotes: 1

Views: 1217

Answers (1)

sagi
sagi

Reputation: 40481

Pros for MySQL at this? Probably the opposite.. MySQL doesn't support window functions so you can try using a correlated query with LIMIT/OFFSET:

SELECT p.folio,p.max_d,p.second_d,DATEDIFF(p.max_d,p.second_d) as NumOfDays
FROM (
    SELECT t.folio,MAX(t.date) as max_d,
           (SELECT s.date FROM YourTable s
            WHERE t.folio = s.folio
            ORDER BY s.date DESC
            LIMIT 1,1) as second_d
    FROM YourTable t
    GROUP BY t.folio) p

Upvotes: 2

Related Questions