Assigning Ordinal Values to Progressive Dates

[fsh]

I’m having a problem trying to find out how to assign ordinal values to variables, perhaps by using some sort of CASE statement approach. I need to calculate the DATEDIFF between dates in a database, but, in order to aggregate the average DATEDIFF, I need to order the dates so that I can calculate the difference between the 1st and 2nd dates when a customer called, the 2nd and 3rd, and so on. I would be most grateful if someone could offer advice.

Cheers

FSH

 

[clarkin]

so, you have a table with a datetime column CallDate and an integer column CustID (easier if I just make up the names).

First off you want to order them, then you want to get the DATEDIFF between calls per customer, right?

You probably already know how to order them:

SELECT * FROM CallTable ORDER BY CustID, CallDate

I think to do this you may have to use the above and SELECT INTO to create a temp table with your data ordered and numbered - perhaps with an identity column. So you'd have:

CallTable
RowNum   CustID   CallDate
  1        1       2003-01-01 18:14:00.000
  2        1       2003-01-20 16:12:20.000
  3        2       2003-04-10 12:50:00.000
..

From here you could probably create a select with a sub select that examines RowNum + 1 to get the datediff.

Give it a go, if your stuck we'll see if we can help


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[lienyi]

try this:

select a.CustID,
max(b.CallDate) as previousCallDate,
a.CallDate as nextCallDate,
datediff(day, max(b.CallDate), a.CallDate) as daysSince
from CallTable a
join CallTable b on a.CustID = b.CustID and a.CallDate > b.CallDate
group by a.CustID, a.CallDate

 

[clarkin]

oo, that looks good - can you show us an example of what the above gives you fsh?


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].