datediff betweens rows in a table

[dfwcharles]

Cyrstal v8, MS Sql 2000 db.

I'm trying to get the age, in days, of Workorder statuses. The statuses are stored in one table(wostatus) with a change date. Here's an example:

wonum status changedate
---------------------------------
2932 WAPPR 2005-05-01 08:55:00.000
2932 APPR 2005-05-03 09:55:00.000
2932 COMP 2005-05-04 08:21:00.000
2932 CLOSE 2005-05-04 08:22:00.000

What I want to get is the age between rows 1 & 2, 2 & 3, and 3 & 4. The age of the last row is irrelevent to the report. The report will also be grouped on wonum.

I can accomplished this in SQL with a stored procedure, but I would rather do everything in Crystal.

I was thinking that I could declare variables for each changedate and then use the datediff function to get the age. But I'm not sure how to write in crystal, something like this: Local dateVar d1 : = {wostatus.changedate}; where status = 'wappr'.

Anyhelp would be appreciated.

 

[synapsevampire]

More simply, use a formula in the details such as:

if {table.wonum} = previous({table.wonum})
and
{table.wonum} = next({table.wonum}) then
datediff("d",{table.date},{previous{table.date})
else
0

-k

 

[dfwcharles]

Thanks synapsevampire, thats pretty close. It gives me the age, but it places it on the wrong row, heres an example:

WAPPR 2004 1:39:00PM 0.00
APPR 2004 1:02:00PM 5.00
COMP 2005 10:04:00AM 190.00
CLOSE 2005 10:13:00AM 0.00

What I really need need is the "5" on the same row as "wappr" and "190" on "appr" row. And of course the age of "comp" as well.Here's the forumla I used in the report.

if {wostatus.wonum}= previous({wostatus.wonum})
and
{wostatus.wonum}= next({wostatus.wonum}) then
datediff("d",previous({wostatus.changedate}),{wostatus.changedate})
else
0

 

[dfwcharles]

Wait, I'm getting closer. I used this forumla and now I get what I want except on the very frist record. That age is always empty.

if {wostatus.wonum}= previous({wostatus.wonum})
and
{wostatus.wonum}= next({wostatus.wonum})
then
datediff("d",{wostatus.changedate},next({wostatus.changedate}))
else
0

 

[dfwcharles]

Sorry, I meant to say, the age of the very frist record in the report is always empty. The rest of the records the frist age is always zero.

11054 WAPPR 3/15/04
11054 APPR 3/15/04 392.00
11054 COMP 4/11/05 21.00
11054 CLOSE 5/2/05 0.00

19752 WAPPR 10/5/04 0.00
19752 APPR 10/10/04 190.00
19752 COMP 4/18/05 14.00
19752 CLOSE 5/2/05 0.00

19753 WAPPR 10/5/04 0.00
19753 APPR 10/10/04 190.00
19753 COMP 4/18/05 14.00
19753 CLOSE 5/2/05 0.00

 

[lbass]

All you really need is:

if {wostatus.wonum}= next({wostatus.wonum}) then
datediff("d",{wostatus.changedate},next({wostatus.changedate}))
else
0

-LB

 

[dfwcharles]

Thanks lbass, that got it. Also thanks synapsevampire.

 

[saltcoats]

We are using CR 10 on Oracle 9i

We are having a similar issue. We are trying to measure the date and time difference between two statuses on the same wonum. The WSCH to the COMP or CLOSE status, whichever is earlier?

 

[lbass]

If you sort on changedate, then the earlier status will be the next record compared to WSCH.

-LB

 

[saltcoats]

Thanks LB What we are trying to measure is the the time it took to complete or close a workorder after it was created. The issue we have is that there may be other statuses between WSCH and COMP

259873 WSCH 10/17/2005 3:10:00 AM
259873 APPR 10/18/2005 3:10:55 AM
259873 INPRG 10/18/2005 3:10:58 AM
259873 COMP 10/18/2005 3:13:58 AM
259873 CLOSE 10/19/2005 7:48:00 AM

Saltcoats

 

[lbass]

Can't you filter out the irrelevant statuses in the record selection formula?

-LB