x

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

more ▼

asked Feb 14 at 10:36 AM in Default

avatar image

David 2 1
832 54 60 67

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Feb 15 at 11:47 AM

avatar image

ThomasRushton ♦♦
41.4k 20 50 53

Fantastic, thank you very much. I'd like to award both answers but I have to choose one. :/

Feb 15 at 12:35 PM David 2 1
(comments are locked)
10|1200 characters needed characters left

If you are on SQL 2012 or later, then you could use the LAG function 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)


more ▼

answered Feb 14 at 10:45 AM

avatar image

Kev Riley ♦♦
65.2k 48 62 81

Thanks. Is there a way in SQL2000?

Feb 14 at 12:28 PM David 2 1

yes...upgrade ;) - give me a few mins........

Feb 14 at 12:30 PM Kev Riley ♦♦

Ha ha. Any luck? I admit this system is a pain but it's unsupported.

Feb 15 at 11:00 AM David 2 1

sorry got distracted! @ThomasRushton stepped up to the biscuit tho....

Feb 15 at 08:03 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x411
x69
x13

asked: Feb 14 at 10:36 AM

Seen: 41 times

Last Updated: Feb 15 at 08:03 PM

Copyright 2016 Redgate Software. Privacy Policy