Ranking and finding the difference in dates in a table in SQL-CodePudding

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;

CodePudding user response：

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;