Finding original date over a series of rows

[roena26]

I'm working in Crystal Reports 2008 and SQL Server 2008 R2. I need to find the original date an order was placed when the parent (original) record isn't carried throughout the updated records.

Order_ID Order_Written Prev_Ord_ID
10847685 3/15/2013 NULL
10847686 4/12/2013 10847685
10847689 8/16/2013 10847686
11981012 8/21/2013 10847689

So I basically need to be able to say that Order #11981012 was originally written on 3/15/2013. There could be any number of updates for a particular order.

Any thoughts? Is this possible? Do you need more information to make that type of determination?

 

[pmax9999]

Is there any reason why you couldn't just use the Minimum date?

The alternative would be to use a variable that is set for the first record (or 1st record in the group).

Does that help?


Pete.

 

[roena26]

That was my first thought but I don't see how I could get the minimum date for the group of records when there's nothing tying the first record that has the date that I want with the order ID of the last record except for the two records in between.

[tt]Row Order_ID Ord_Write Prev_Ord_ID
001 10847685 3/15/2013 NULL
002 10847686 4/12/2013 10847685
003 10847689 8/16/2013 10847686
004 11981012 8/21/2013 10847689[/tt]


Row 001 is the first record (order that was placed on 3/15/2013. On 4/12/2013, Row 002 is where the original order was modified, creating a new Order ID. You can tell by the Order ID from Row 1 appearing in the Prev_Ord_ID column for Row 002. The order was modified again on 8/16/2013 and 8/21/2013. I would like to have the most current Order ID (which would be from the very last row in the group of records) and the date the order was originally placed (which is from the very first row in the group of records).

 

[pmax9999]

I don't follow. If the above data is representative of what the report returns, a simple Minimum would work. If there are orders across multiple clients, couldn't you jut group by Client ID and still use the Minimum.

Perhaps you need to give a more representative sample of data, and explain how you would be able to determine programmatically/logically which orders related to a previous order.


Pete

 

[Charliy]

You could do a Running Total on the date field that does Minimum only if the Previous Order is null.

 

[roena26]

I guess additional data examples would help explain why I can't just take a minimum as a client can have multiple orders:

[tt]
Row C_ID Order_ID Ord_Write Prev_Ord_ID
001 0001 10847685 3/15/2013 NULL
002 0001 10847686 4/12/2013 10847685
003 0001 10847689 8/16/2013 10847686
004 0001 11981012 8/21/2013 10847689
005 0001 12163489 5/13/2013 NULL
006 0001 12165789 6/22/2013 12163489
007 0001 12965780 7/19/2013 12165789
008 0001 13000219 3/17/2013 NULL
[/tt]

Rows 1-4 represent an order that was placed originally on 3/15/2013 and has been updated/modified three additional times.
Rows 5-7 represent an order that was placed originally on 5/13/2013 and has been updated/modified two additional times.
Rows 8 represent an order that was placed originally on 3/17/2013 and has not bee updated/modified.

All three orders are by the same client. What I wanted to see is the following:

[tt]C_ID Order_ID Order_Write
0001 11981012 3/15/2013
0001 12965780 5/13/2013
0001 13000219 3/17/2013[/tt]

I honestly can't think of a way to program this without possibly functionality that isn't available in Crystal

I solved the problem by creating multiple queries and using a Command statement in Crystal to get what I needed. I was just hoping that someone with more experience than me would have had an idea of a better answer. Thanks for your help.

 

[Charliy]

Create a formula like this:
//@grorder
if {table.prev_order_id} = 'NULL'
then [table.order.id}
else {table.prev_order_id}

Group on that formula, then sort by date.

Create a Running Total that does a Minimum of date for that group.

You can then put order_id in the group footer and it will be the right one.