asked
How To Get The DateDiff In Days Between Subsequent Dates?
How can I return the date difference in days between a customers (grouped by custno) sale dates? For example in the table below the first customer sale will have 0 date_diff in days as it is the first sale. However how do I return the date diff in subsequent sales dates? I'm looking to generate something like: custno saleno sale_date date_diff 89 124 2005-09-22 0 408 187 2001-04-04 0 408 222 2005-02-14 1412 408 378 2007-04-07 782 442 764 2010-01-29 0 442 888 2013-05-24 1211 TIA
OK, for SQL 2000... And shamelessly nicking @Kev Riley's code... You'll need to change the "sale_date" field from `date` to `datetime`, as `date` didn't exist as a data type back then in SQL Server. The `LAG` function can be replaced with a rather unpleasant inner correlated query: SELECT yt.custno, yt.saleno, yt.sale_date, (SELECT max(yti.sale_date) FROM @YourTable yti WHERE yti.custno = yt.custno AND yti.saleno < yt.saleno) FROM @YourTable yt which gives your final query (with date_diff): SELECT yt.custno, yt.saleno, yt.sale_date, DATEDIFF(DAY, ISNULL((SELECT max(yti.sale_date) FROM @YourTable yti WHERE yti.custno = yt.custno AND yti.saleno < yt.saleno),yt.sale_date),yt.sale_date) AS date_diff FROM @YourTable yt Hope this helps.
If you are on SQL 2012 or later, then you could use the [LAG function][1] to get the previous sale_date. This query shows what values you get declare @YourTable table (custno int, saleno int, sale_date date) insert into @YourTable select 89, 124,'2005-09-22' insert into @YourTable select 408,187,'2001-04-04' insert into @YourTable select 408,222,'2005-02-14' insert into @YourTable select 408,378,'2007-04-07' insert into @YourTable select 442,764,'2010-01-29' insert into @YourTable select 442,888,'2013-05-24' select custno, saleno, sale_date, lag(sale_date,1,null)over(partition by custno order by saleno) from @YourTable order by custno, saleno, sale_date and then you can use this to do the DATEDIFF select custno, saleno, sale_date, isnull(datediff(day, lag(sale_date,1,null)over(partition by custno order by saleno), sale_date),0) as date_diff from @YourTable order by custno, saleno to give the results custno saleno sale_date date_diff ----------- ----------- ---------- ----------- 89 124 2005-09-22 0 408 187 2001-04-04 0 408 222 2005-02-14 1412 408 378 2007-04-07 782 442 764 2010-01-29 0 442 888 2013-05-24 1211 (6 row(s) affected) [1]:
https://msdn.microsoft.com/en-us/library/hh231256
sorry got distracted! @ThomasRushton stepped up to the biscuit tho....
18 People are following this question.
