Number of Days Between

[mollie]

Hi --

I need to calculate the number of days between two records using the same date field.

Example:

Invoice# Date Status

1 12/01/02 Created
1 12/15/02 Submitted
1 03/01/03 Forwarded

There can be up to to 6 records per invoice number, each with a different status. I need to determine the number of days the invoice was in each status.

Any help would be appreciated.

Thanks

Mary

 

[lbass]

Try:

{Date}-Previous({Date})

-LB

 

[mollie]

Thanks very much for pointing me in the right direction.

I used your forumla and did a variation as follows:


if {TEST.Record#}=next({TEST.Record#}) then
(next({TEST.Date})-{TEST.Date})
else
CurrentDate-{TEST.Date}


because I need it to count the days and post next to the current record ONLY if there is a next record for the same group.

Thanks again!

Mary

 

[mollie]

Hi -

Now I'm stuck again.

The above formula works when the records are grouped by invoice # and sorted by date. (see my first posting above)

However, my final report needs to look like this: (grouped by Status)

Invoice Date Status #Days

1 12/01/02 Created 15
2 01/01/02 Created 30

1 12/16/02 Submitted 90
2 01/31/02 Submitted 46

and so on.

Any advice?

Thanks

Mary

 

[lyanch]

You can get the days between in two ways that I can see..

1. Add a subreport that calculates it. If you have a large number of detail lines this can seriously slow report generation.

2. Create a stored procedure that calculates the numbers that you want. This is assuming you are pre CR9, in CR9 i think you do this right in the SQL. The SQL might be a bit complex, but this is my preferred route.

Lisa

 

[lbass]

Mary,

I wanted to apologize for not taking the time to understand your original problem before responding the first time--"next" was obviously the way to go, along with the other conditions. With respect to your current issue, the following works without subreports, but might also be slow:

1-Create two alias tables in addition to your original table and link each one to the original table (Table1) on {Inv_#}.

2-Place the same general selects on Table2 and Table3, except limit each table to one status field, e.g., for table2, select {stat} = "Submitted" and for table3, select {stat} = "Forwarded".

3-Keep your groupings on Table1 as is.

4-Create a formula {@Days_in_Status} and place with other fields in details or Group2(Inv_#) header, as appropriate:

if {Table1.stat}= "Created" then
{Table2.Date} - {Table1.Date} else
if {Table1.stat} = "Submitted" then
{Table3.Date} - {Table1.Date} else
if {Table1.stat} = "Forwarded" then
Currentdate - {Table1.Date}

This should work as long as the status fields are not null. If you have to account for null fields, it becomes more complex.

-LB