# Ranking and finding the difference in dates in a table in SQL

## Ranking and finding the difference in dates in a table in SQL

Contents

Problem Description:

I want to find the number of days between two different dates in a column which I have ranked based on the most recent date. I was able to write the code that gave me the correct difference in dates between most of the dates, but I get a discrepancy when it calculates the difference between two dates in different months, for ex. taking the difference between 2022-12-01 and 2022-11-30 returns "71" as the number of days between the two.

I created the "Prev_Date_Updated" column by using the rank function partitioned by DDA_Account ordered by Date_Last_Updated.

Could someone please help me with the syntax, or point out my mistake, maybe there’s a better way of doing this that I am not seeing. Any help would be greatly appreciated.

Here is the logic I used to create the table:

``````SELECT DDA_Account, Date_Last_Updated, Total_Daily_Deposits,
RANK() OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated DESC) Date_Rank, LAG(Date_Last_Updated,1) OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated DESC) as Prev_Date_Updated,
CASE
WHEN RANK() OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated DESC) = 1 THEN null
ELSE LAG(Date_Last_Updated, 1) OVER (partition by DDA_Account ORDER BY Date_Last_Updated DESC) - Date_Last_Updated
END AS Days_Between_Deposits
FROM trb_acct_3
HAVING Total_Daily_Deposits > 30;
``````

## Solution – 1

You should use `DATEDIFF` function. MySql has no date overload for the `-` operator and a number of conversions apply producing the wrong result

``````SELECT DDA_Account, Date_Last_Updated, Total_Daily_Deposits,
RANK() OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated DESC) Date_Rank, LAG(Date_Last_Updated,1) OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated DESC) as Prev_Date_Updated,
CASE
WHEN RANK() OVER (PARTITION BY DDA_Account ORDER BY Date_Last_Updated DESC) = 1 THEN null
ELSE datediff (LAG(Date_Last_Updated, 1) OVER (partition by DDA_Account ORDER BY Date_Last_Updated DESC),  Date_Last_Updated)
END AS Days_Between_Deposits
FROM trb_acct_3
HAVING Total_Daily_Deposits > 30;
``````
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.