Should this be done as a subquery or how else should i do this?

[Rmck87]

I have a table with these columns in it that i need:

Time_Entry
Hours_Actual Billable_Flag Date_Start
4.0 0 7/30/2002
.25 -1 12/11/2002
8.0 -1 12/30/2002
1.5 -1 1/11/2002
6.75 0 1/11/2002
3.0 0 4/30/2002
3.25 -1 6/11/2002
8.0 0 8/30/2002
8.0 0 8/30/2002
8.0 -1 8/30/2002


Random hours, and a billable flag. The flag is based on a yes/no data type. What i want to do is:

1. Get the total amount of hours for each time period, (whenever the date_start fields are the same add up the number of actual_hours)

2. Whenever the billable flag is -1 or yes (Billable), i want to add up the hours for that, and place them on the same line as the criteria for #1.

3. Whenever the billable flag is 0 or no (NonBillable), i want to add up the hours for that, and place them on the same line as the criteria for #1, and #2.

So i should get something like this.....

Actual_Hours Billable NonBillable Date_Start
4.0 0.0 4.0 7/30/2002
0.25 0.25 0.0 12/11/2002
8.0 8.0 0.0 12/30/2002
8.25 1.5 6.75 1/11/2002
3.0 0.0 3.0 4/30/2002
3.25 3.25 0.0 6/11/2002
24.0 8.0 16.0 8/30/2002




One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Chopper]

You could achieve this in a crosstab query.

-Chopper

 

[Rmck87]

and how would i do that?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.