I have a table with following structure
ID Account Number Date 1 1001 10/9/2011 (dd/mm/yyyy) 2 2001 1/9/2011 (dd/mm/yyyy) 3 2001 3/9/2011 (dd/mm/yyyy) 4 1001 12/9/2011 (dd/mm/yyyy) 5 3001 18/9/2011 (dd/mm/yyyy) 6 1001 20/9/2011 (dd/mm/yyyy)
Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number The expected result would be !!
1001 10/9/2011 - 12/9/2011 2 days 1001 12/9/2011 - 20/9/2011 8 days 1001 20/9/2011 NA
Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number , in the above example would be 1001. (the dates don't have to be shown in the result)
I use access 2003.
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
diff() function. This function calculates the difference between two consecutive DataFrame elements. Parameters: periods: Represents periods to shift for computing difference, Integer type value.
The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
SELECT T1.ID, T1.AccountNumber, T1.Date, MIN(T2.Date) AS Date2, DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff FROM YourTable T1 LEFT JOIN YourTable T2 ON T1.AccountNumber = T2.Accountnumber AND T2.Date > T1.Date GROUP BY T1.ID, T1.AccountNumber, T1.Date;
or
SELECT ID, AccountNumber, Date, NextDate, DATEDIFF("D", Date, NextDate) FROM ( SELECT ID, AccountNumber, Date, ( SELECT MIN(Date) FROM YourTable T2 WHERE T2.Accountnumber = T1.AccountNumber AND T2.Date > T1.Date ) AS NextDate FROM YourTable T1 ) AS T
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With