Selection criteria to exclude the last record for a group/day

[mgrover2k2]

Using:CR 9 , SQL Server

I'm grouping on "type" and "date_time" (by day) and then Sum the "count" for each group.

Type count date_time
1042 2 12/19/2003 11:50:31 PM
1042 2 12/19/2003 11:58:31 PM
1042 4 12/19/2003 11:59:31 PM
1042 3 12/20/2003 12:15:00 AM
1042 4 12/20/2003 12:30:02 AM
1043 4 12/20/2003 12:45:02 AM
1043 4 12/20/2003 1:00:02 AM
1043 5 12/20/2003 1:15:02 AM

I want a daily sum of the "count" for each "type"... BUT I want to exclude the last daily record for each "type".

type 1042 for 12/19 would = 4
type 1042 for 12/20 would = 3
type 1043 for 12/20 would = 8

Thanks for any help!

 

[synapsevampire]

Here's a formula cheat providing that your data is sorted as displayed, place the following formula in the date_time group footer:

sum({table.count},{date_time})-{table.count}

-k