How to Calculate the Interval Days?

Analytics
Enthusiast

How to Calculate the Interval Days?

Dear who know how,

If one customer do a purchase from a shop on these day,

2007-05-04
2007-05-08
2007-05-17
2007-05-30

How to calculate the interval days?
I should get these answers:

from 2007-05-04 to 2007-05-08 = 4 days
from 2007-05-08 to 2007-05-17 = 9 days
from 2007-05-17 to 2007-05-30 = 13 days

Anyone who know the answer please give me a hand.

Thanks. :-)
6 REPLIES
Enthusiast

Re: How to Calculate the Interval Days?

Considering that you would also have to bother about duplicates on the date column,(customer returned on the same day) which I assume shouldn't be counted as 0 days, and that you also need both the dates (from and to) in your result set, ( and that my OLAP is not so good :-) )

This is what I could think of.

WITH CUSTDATA(CID, BILLDATE, RANKID) AS
(
SELECT CID, BILLDATE, RANK() OVER(PARTITION BY CID ORDER BY BILLDATE)
FROM CUSTINFOTBL
GROUP BY 1, 2
)
SELECT C1.CID, C1.BILLDATE FROMDATE, C2.BILLDATE TODATE, TODATE - FROMDATE NOOFDAYS
FROM CUSTDATA C1 INNER JOIN CUSTDATA C2
ON C1.CID = C2.CID AND C1.RANKID = C2.RANKID-1
ORDER BY 1, 2, 3
;
Enthusiast

Re: How to Calculate the Interval Days?

Actually I only need the results.

Many Thanks.
Enthusiast

Re: How to Calculate the Interval Days?

Well, in that case this is all you need.

SELECT CID, BILLDATE - ((SUM(BILLDATE(INTEGER)) OVER (PARTITION BY CID ORDER BY BILLDATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )) (DATE)) NOOFDAYS
FROM CUSTINFOTBL
QUALIFY( NOOFDAYS IS NOT NULL AND NOOFDAYS <> 0)
;
Enthusiast

Re: How to Calculate the Interval Days?

On third thoughts , you can even get both dates from it as well.

SELECT CID
,BILLDATE - ((SUM(BILLDATE(INTEGER)) OVER (PARTITION BY CID ORDER BY BILLDATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )) (DATE)) NOOFDAYS
,BILLDATE - NOOFDAYS FROMDATE
,BILLDATE TODATE
FROM CUSTINFOTBL
QUALIFY( NOOFDAYS IS NOT NULL AND NOOFDAYS <> 0)
ORDER BY 1, 2
;

funny, sometimes those little things keep staring at you all along and you fail to notice it :-)
Enthusiast

Re: How to Calculate the Interval Days?

Thanks Joedsilva.

Something staring on me? I didnt know that. (^_^)

Enthusiast

Re: How to Calculate the Interval Days?

Dear All,
Actually I found another solution, just change the date to integer than apply the MDIFF function.

sel CID
,Purchase_Dt
,Cast (Purchase_Dt as INT) as Cdays
,MDIFF(Cdays,1,Purchase_Dt)
From The result will return the interval days.
^_^

Cheer!