Calculate number of days between dates on different rows per group

[IvyZZZ]

Hello,
I'm trying to figure out how to calculate number of days between dates on different rows per group.

example:
ClientID     Item    Date_Created      
1001          A01        1-Mar-13
                 A01        10-Mar-13
                 A02        20-Mar-13
1002          A02        31-Jan-13
                 A01        24-Feb-13
1003          A03        8-Oct-13

I would like to know the difference between the Date_Created per ClientID, like:

ClientID     Item    Date_Created    Time_Intervals_Days 
1001          A01        1-Mar-13                    0
                 A01        10-Mar-13                  9
                 A02        20-Mar-13                  10
1002          A02        31-Jan-13                   0
                 A01        24-Feb-13                   24
1003          A03        8-Oct-13                     0

I can't figure out a way to calculate days on different rows...any ideas? :'(

Appreciate in advance for your help!

[BigChris]

You probably want something along the lines of:

_days_between([DateCreated],minimum([DateCreated] for [ClientID])

[IvyZZZ]

Thanks Chris! It does capture the difference of days, but the numbers are only based on the first Created_Date per ClientID...
I would like to know the difference between consecutive Created_Date for each ClientID.
I have no clue which function to use in terms of switching the 'minimum' to something like 'previous'...any idea? :'(

[Lynn]

You are trying to apply a procedural approach to set based SQL. You could try running-difference, but I think this is intended for numbers rather than dates. Perhaps converting the date to Julian might work but I've never tried it.

[BigChris]

My apologies, I didn't spot that. I'm not sure that you can do that, but I haven't given up yet...if I can think of a way to do it I'll post back on here. My first thought would be to have two queries, one of which calculates the rank of the date, and the other which calculates rank-1...then join on rank and rank-1 for the item and client. It might not be elegant, and you'd need to check the performance, but it might work.

[IvyZZZ]

That's a really good idea!!!

So now I have the Rank and Rank-1 calculated as below:
ClientID     Item    Date_Created      Rank         Rank-1
1001          A01        1-Mar-13           1                 0
                 A01        10-Mar-13          2                1
                 A02        20-Mar-13          3                2
1002          A02        31-Jan-13          1                0
                 A01        24-Feb-13          2                1
1003          A03        8-Oct-13            1                0

Is there any chance you know how to join the Item and Date_Created for Rank-1??

Thanks again!

[Lynn]

What is your database? I tried my suggestion and it is a piece of cake if you have a function available to convert dates to Julian. DB2 and Oracle certainly have this.

For DB2 it is simply this expression in a query calculation to get the Julian date:

Code Select Expand


julian_day( [Date Field] )


Then you create another query calculation for the running-difference with the appropriate scope defined in the "for" clause:

Code Select Expand


running-difference ( [Julian Day] for [Customer Name] )


[MFGF]

What is your database? I tried my suggestion and it is a piece of cake if you have a function available to convert dates to Julian. DB2 and Oracle certainly have this.

For DB2 it is simply this expression in a query calculation to get the Julian date:

Code Select Expand


julian_day( [Date Field] )


Then you create another query calculation for the running-difference with the appropriate scope defined in the "for" clause:

Code Select Expand


running-difference ( [Julian Day] for [Customer Name] )


Wow! What a smart suggestion!!! I doff my cap to you, madam! :)

"Piece of cake" sounds like a very British way of describing the approach! Do I detect a faint English accent creeping in to your posts? ;) I'd have thought "A walk in the pie" or "Easy as park" would have been your de-facto terminology?

To redress the balance, I shall finish in my best American accent...

AWESOME!!! :)

MF.

[IvyZZZ]

What is your database? I tried my suggestion and it is a piece of cake if you have a function available to convert dates to Julian. DB2 and Oracle certainly have this.

For DB2 it is simply this expression in a query calculation to get the Julian date:

Code Select Expand


julian_day( [Date Field] )


Then you create another query calculation for the running-difference with the appropriate scope defined in the "for" clause:

Code Select Expand


running-difference ( [Julian Day] for [Customer Name] )


Thanks Lynn!!! It is an Oracle database.
I have tried your suggestion, but it returned multiple errors like:
UDA-QOS-0006 Error
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00904: "JULIAN_DAY":invalid identifier
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00904: "JULIAN_DAY": invalid identifier
RSV-VAL-0004 Unable to find query information for the item ClientID.
RSV-VAL-0004 Unable to find query information for the item Date_Created.
......

the julian_day( ) function does make sense but I have no idea why it's not working... :-[

[MFGF]

Thanks Lynn!!! It is an Oracle database.
I have tried your suggestion, but it returned multiple errors like:
UDA-QOS-0006 Error
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00904: "JULIAN_DAY":invalid identifier
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00904: "JULIAN_DAY": invalid identifier
RSV-VAL-0004 Unable to find query information for the item ClientID.
RSV-VAL-0004 Unable to find query information for the item Date_Created.
......

the julian_day( ) function does make sense but I have no idea why it's not working... :-[

I think Julian_day() is a DB2 function not an Oracle function.

For Oracle, I'd try:

to_char([Date Field], 'J')

I don't have Oracle so I can't test it, sorry.

MF.

[IvyZZZ]

I think Julian_day() is a DB2 function not an Oracle function.

For Oracle, I'd try:

to_char([Date Field], 'J')

I don't have Oracle so I can't test it, sorry.

MF.

I combined both yours and Lynn's functions and it worked out!!!
Thank you all very much~ ! ;D

[nsaxena]

Following this post,
hats off to all who put time to provide solutions!!

You guys are great :)

[BigChris]

You learn something every day! I thought Julian related to carrot sticks. I still think I could have made my method work, but given that you've got a solution I won't pursue it...but well done for coming up with something much simpler.

[Lynn]

Following this post,
hats off to all who put time to provide solutions!!

You guys are great :)

I think everyone can learn a lot from Cognoise! It is a fantastic resource and I've learned so much just by playing around trying to solve problems people post and reading solutions that others offer.

[Lynn]

You learn something every day! I thought Julian related to carrot sticks. I still think I could have made my method work, but given that you've got a solution I won't pursue it...but well done for coming up with something much simpler.

Ha ha, I'll have some ranch dip with my carrots please  ;D

I think your solution would certainly work. You took the time to also note the caveats in terms of complexity and possible performance implications which is always helpful so people can assess options for themselves.

[Lynn]

Wow! What a smart suggestion!!! I doff my cap to you, madam! :)

"Piece of cake" sounds like a very British way of describing the approach! Do I detect a faint English accent creeping in to your posts? ;) I'd have thought "A walk in the pie" or "Easy as park" would have been your de-facto terminology?

To redress the balance, I shall finish in my best American accent...

AWESOME!!! :)

MF.

Very funny, Muppet </sarcasm>

The accents and expressions can be challenging in cross-cultural situations.

I've heard people say "Bog-standard" so many times and always thought they were saying "Bob-standard" which always made me wonder who is Bob and what is so standard about him?

The other day I was on the phone with a guy who has a strong northern British accent. He said a word I couldn't understand so I asked him to spell it.....he paused a moment and then replied "B-A-L-L".

:-[ Oops, didn't I feel silly!