Evaluate/Calculate records against each other?

[TMcCrillis]

I'm new to crystal and know what I do only by "hands on", what I've figured out on my own... so be gentle....
I'm trying to calculate the # Days a product was on the shelf. I have records from the same table that I need to calculate and I don't have the first idea how.
I've created the main report (Part Number Info) and added a subreport to pull the transaction detail for that specific part number and sorted by serial number. Each serial number should have two records J55(off shelf) and J52(on shelf) among others. I need to get the datediff between these two records but I don't know how to evaluate records against themselves because these will post to the report as individual records, not as something that I can put into multiple columns. Once that is out of the way, I know how to evaluate days on shelf for a product that is still in stock.
I hope that explains it clearly. I'd appreciate any help.

 

[lbass]

Please show some sample data. Are there more than two records per serial number? If so, please show the options, sorted or grouped by serial number and then by this field (please name it).

-LB

 

[TMcCrillis]

Part_Number (from Main Report)
115TL20
Serial_Number Date_History CODE_TRANSACTION
44586 04/26/2006 J52 (On Shelf)
44586 05/10/2006 J55 (Off Shelf)
54423 08/21/2006 J52
56871 09/25/2006 J52
56872 09/25/2006 J52
56873 09/25/2006 J52
56873 11/21/2006 J55

If the serial numbers are the same and one code is J52 and another is J55 I need the number of days between them. If there isn't a J55 transaction, then I need to calculate based on today's date.
Essentially, what I need is the days the item was on the shelf.

 

[lbass]

Insert a second group on serial number and then create three formulas:

//{@reset} to be placed in the serial number group header:
whileprintingrecords;
numbervar add := 0;

//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {table.date_history}, next({table.date_history}));

if {table.code_transaction} = 1 and
{table.serialno} = next({table.serialno}) then
add := add + diff else
if {table.serialno} <> next({table.serialno}) and
{table.code_transaction} = 1 then
add := add + (currentdate-{table.date_history});
add

If you want a summary in the serial group footer, use:
whileprintingrecords;
numbervar add;

-LB

 

[TMcCrillis]

if {V_ITEM_HISTORY.CODE_TRANSACTION} = 1 and
{V_ITEM_HISTORY.SERIAL_NUMBER} = next({V_ITEM_HISTORY.SERIAL_NUMBER})
then add := add + diff else
if {V_ITEM_HISTORY.SERIAL_NUMBER} <> next({V_ITEM_HISTORY.SERIAL_NUMBER})
and {V_ITEM_HISTORY.CODE_TRANSACTION} = 1 then
add := add + (currentdate-{V_ITEM_HISTORY.DATE_HISTORY});
add

I'm getting an error on this formula. It's noting an expectation after the first "then". Ideas? Did I enter something wrong?

 

[lbass]

The whole formula includes the variable declaration and should look like:

//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {table.date_history}, next({table.date_history}));

if {table.code_transaction} = 1 and
{table.serialno} = next({table.serialno}) then
add := add + diff else
if {table.serialno} <> next({table.serialno}) and
{table.code_transaction} = 1 then
add := add + (currentdate-{table.date_history});
add

-LB

 

[TMcCrillis]

Thank you so much for your help. I really appreciate it. If I could ask one more question, how can I get an average days on shelf for each part number.
I tried:
Average ({@diff}) ...which didn't work.
Thanks again!

 

[lbass]

Change the display formula in the serial group footer to:

whileprintingrecords;
numbervar add;
numbervar cnt := cnt + 1;
numbervar sumadd := sumadd + add;
add

Then in the subreport report footer use this formula:

whileprintingrecords;
numbervar cnt;
numbervar sumadd;
sumadd/cnt

-LB

 

[TMcCrillis]

Average works fine thank you!- however I need to account for the built to order parts which have a day count of 0-2 days. So what I'm looking to do is have a set of results which contain 9 serial numbers, with the following results 8,7,7,7,0,0,17,14,25 for number of days on shelf (@diff)from this formula:
//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {table.date_history}, next({table.date_history}));

if {table.code_transaction} = 1 and
{table.serialno} = next({table.serialno}) then
add := add + diff else
if {table.serialno} <> next({table.serialno}) and
{table.code_transaction} = 1 then
add := add + (currentdate-{table.date_history});
add
...which is correct when it produces a value of 9 (rounded of course) when what I'm really looking for is that same data (a total of 85 days) averaged across 7 serial numbers instead of 9 since 2 of those 9 were build to order based on the number of days on shelf being less than 2.

Additionally this @diff formula is then part of a SubFormula/MainFormula transfer back to the main report.

So in the beginner's unknowing mind, I'm thinking that if @diff is <2 I don't want it included in the average.

I hope that clearly explains what I'm trying to do. Any assistance would be great. Perhaps I've made a mistake along the way, but what was provided in

http://www.tek-tips.com/viewthread.cfm?qid=1383200&page=1

didn't help me. I'd be happy to send you the report if it would help in anyway.

 

[lbass]

Please explain why the suggestion didn't work in that thread. Here's the problem with multiple threads--now we're forced to go back and forth.

-LB

 

[TMcCrillis]

Changing the formula per your suggestion, to:
//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {V_ITEM_HISTORY.DATE_HISTORY}, next({V_ITEM_HISTORY.DATE_HISTORY}));

if {V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' and
{V_ITEM_HISTORY.SERIAL_NUMBER} = next({V_ITEM_HISTORY.SERIAL_NUMBER}) and
diff > 1 then
add := add + diff else

if
(
nextisnull({V_ITEM_HISTORY.SERIAL_NUMBER}) or
{V_ITEM_HISTORY.SERIAL_NUMBER} <> next({V_ITEM_HISTORY.SERIAL_NUMBER})
) and
{V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' )and
currentdate-{V_ITEM_HISTORY.DATE_HISTORY}) > 1 //not sure about this--
//depends on YOUR logic
then
add := add + (currentdate-{V_ITEM_HISTORY.DATE_HISTORY});
add

did not change the results. In the example noted above, it would still produce a result of 9 when it should have been 12. Initally I'd get an error that the "then" was missing, but it appeared to be fixed by adding ) where noted in green. My concern is that my changing ) altered the formula somehow.

 

[lbass]

No, that paren doesn't go there, and you have an extra one in another place. I just tested this, and it omitted values of 0,1:

//{@diff} to be placed in the detail section:

if {V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' and
{V_ITEM_HISTORY.SERIAL_NUMBER} = next({V_ITEM_HISTORY.SERIAL_NUMBER}) and
diff > 1 then
add := add + diff else
//{@diff} to be placed in the detail section:
whileprintingrecords;
numbervar add;
numbervar diff := datediff("d", {V_ITEM_HISTORY.DATE_HISTORY}, next({V_ITEM_HISTORY.DATE_HISTORY}));

if
(
nextisnull({V_ITEM_HISTORY.SERIAL_NUMBER}) or
{V_ITEM_HISTORY.SERIAL_NUMBER} <> next({V_ITEM_HISTORY.SERIAL_NUMBER})
) and
{V_ITEM_HISTORY.CODE_TRANSACTION} = 'J52' and
currentdate-{V_ITEM_HISTORY.DATE_HISTORY} > 1 //not sure about this--
//depends on YOUR logic
then
add := add + (currentdate-{V_ITEM_HISTORY.DATE_HISTORY});
add

You do realize that this formula will only show the correct sum in the detail section, right? If you want to check the value in the group footer, you need to use a formula like:

whileprintingrecords;
numbervar add;

If you simply copied {@diff} and placed it in the group footer, it would add the last record a second time.

-LB