Selecting the minimum date from a date field

[rorymo]

Hi,
I am using BOXI with Sql Server db.
The database is a "help desk" type db where tickets are created, closed, and sometimes re-opened and then later closed again. So one ticket could possibly have been closed twice (or more). I am needing to calculate the time it takes from the date/time the ticket was opened to the date/time that the ticket was closed. When I try to get the oldest (first) close date, it always returns the most current one, not the oldest.
I am grouping on the ticket number, then the close date.
I went into the group formula and entered this that I saw in another thread: {table.date} = minimum({table.date},{table.id}) but that didn't do anything. I have a rather large record select formula and I was wondering if I should only have the group or record selections, not both.
As an alternate solution, I also tried adding a command: SELECT "ahd"."call_req"."ref_num", min("ahd"."call_req"."close_date") as mindate
FROM "ahd"."ahd"."call_req"
GROUP BY "ahd"."call_req"."ref_num"
but it took forever to run. I couldn't figure out how to streamline it. (I am not very good at sql). And it would still have the large record selection formula up front.
Any help would be appreciated! Thanks in advance, Rory

 

[lbass]

How about showing some sample data? Do you have separate fields for open and close date? It might make sense to remove the group on close, and just sort by open date, and then use a formula that uses the previous or next functions to compare corresponding records.

-LB

 

[rorymo]

LB,

Thanks for the reply.
Here is some data:
Report shows:
Ticket# Open Date Close Date
12345 8/1/06 10:04 am 2/6/07 1:38 pm

The actual sequence of events is:
Ticket# Open Date Close Date
12345 8/1/06 10:04 am 8/2/06 7:57 am

12345 8/2/06 9:19 am (reopen same ticket)

2/6/07 1:38 pm (close same ticket again)

(sorry it's not very pretty.)
I need to count time from open date to original close date, not latest close date.
Yes, I do have seperate open and close date fields. I didn't think about previous or next. I will try your suggestion and let you know what happens.
Thanks again.
Rory

 

[lbass]

If you mean you need to calculate the difference between the original open date to the original close date, then you could use a group selection like:

{table.opendate} = minimum({table.opendate},{table.ticket})

This would display only the first record per ticket. Then you could use a simple datediff formula:

datediff("s",{table.opendate},{table.closedate})

If you wanted then to summarize the difference across tickets, you would need to use a running total.

-LB

 

[rorymo]

LB,
Thank you so much. That is exactly what I needed. I appreciate your help.