Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2007
03:20 AM

06-06-2007
03:20 AM

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. :-)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2007
06:46 AM

06-06-2007
06:46 AM

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

;

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

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2007
07:13 AM

06-06-2007
07:13 AM

Re: How to Calculate the Interval Days?

Actually I only need the results.

Many Thanks.

Many Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2007
07:37 AM

06-06-2007
07:37 AM

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)

;

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)

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2007
11:59 AM

06-06-2007
11:59 AM

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 :-)

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 :-)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-06-2007
09:44 PM

06-06-2007
09:44 PM

Re: How to Calculate the Interval Days?

Thanks Joedsilva.

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-07-2007
04:06 AM

06-07-2007
04:06 AM

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)

FromThe result will return the interval days.

^_^

Cheer!

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

^_^

Cheer!