DateDiff on columns in different rows

[BB45]

CR 10
SQL 2000

I am trying to get the number of days between two dates that exist in the same column but different rows.

I tried: if {table.Action} = "Checked"
then datediff ("d", {@Received}, {table.DateCreated})

The formula reads: if {table.Action} = "Received"
then {table.DateCreated}

I have a group on an element that these two relate to but i cannot figure out how to show the datediff.

thanks
BB

 

[synapsevampire]

Try showing example data and the expected output.

If they are in a group, and sorted appropriately, then you might use:

if {table.Action} = "Checked"
and
previous({table.Action}) = "Received"
then datediff ("d", previous({table.DateCreated}), {table.DateCreated})

-k

 

[BB45]

That works somewhat but there are times when the received will not be the the previous record but will come before the checked. Is there a way to evaluate the data set in each group?

Thanks
BB

 

[lbass]

Try this:

Use your formula {@received}:

if {table.Action} = "Received"
then {table.DateCreated}

...like this:

if {table.Action} = "Checked"
then datediff ("d", maximum({@Received},{table.groupfield}), {table.DateCreated})

It's not clear which date is more recent, but that one should be the last argument. This will give you the difference in the same row as the "checked" date. If you want it in the group footer, then create a similar formula for {@checked}, as in:

if {table.Action} = "Checked"
then {table.DateCreated}

Then the formula for the group footer would be:

datediff ("d", maximum({@Received},{table.groupfield}), maximum({@checked},{table.groupfield}))

-LB

 

[synapsevampire]

Try:

whileprintingrecords;
datevar received;
if {table.Action} = "Received" then
received:={table.DateCreated};
if {table.Action} = "Checked"
then datediff ("d", received, {table.DateCreated})

Assumes there's always a received date

-k