Help with generating a reporting query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with generating a reporting query

Author

Topic

webuser
Starting Member

4 Posts

Posted - 2004-06-02 : 15:20:01

I have a table <billing> that has the following columns:

client - varchar(75)
consultant - varchar(35)
bill_date - datetime
bill_hours - double

This is a typical billing table, where consultants charge billable hours to clients for a specific period. I need to generate a report of the total billable hours per consultant per client between two dates, for the consultants - John, Perry, and Tom.

I tried several combinations of queries, but I am not able to arrive at the desired report summary. One of the queries I tried was...

[CODE]
SELECT client, consultant, sum(bill_hours), bill_date
FROM billing
GROUP BY client, consultant, bill_date
HAVING consultant IN ('John', 'Perry', 'Tom') AND
SUM(bill_hours) > 0.0 AND
bill_date BETWEEN '2004-05-01' AND '2004-06-01'
ORDER BY consultant, client, SUM(bill_hours)
[/CODE]

This query is syntactically correct and obtains some records. I want to aggregate the "bill_hours" per consultant per client between the specified dates, but the records are showing up aggregated billable hours **per day** between the two dates.

Can I please get some help on how to aggregate the billable hours for the period between the two dates, per consultant per client, thereby displaying a result in the form...

[CODE]
[CONSULTANT] [CLIENT] [TOTAL BILLABLE HOURS]
------------------------------------------------------------
John CompanyA 20.5
John CompanyB 100.0
Perry CompanyC 77.5
Tom CompanyB 112.0
Tom CompanyD 24.0
Tom CompanyE 47.5
[/CODE]

I greatly appreciate all the help I get. Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-02 : 15:22:46

Get rid of bill_date in the grouping?


  SELECT client, consultant, sum(bill_hours)
    FROM billing
GROUP BY client, consultant, bill_date
   WHERE consultant IN ('John', 'Perry', 'Tom') AND
       SUM(bill_hours) > 0.0 AND
       bill_date BETWEEN '2004-05-01' AND '2004-06-01'
ORDER BY consultant, client, SUM(bill_hours)

Brett

8-)

webuser
Starting Member

4 Posts

Posted - 2004-06-02 : 15:30:11

Thank you, Brett, for such a fast response. I tried your query out in SQL Server and it generates the following error message:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHERE'.

From my understanding, the WHERE clause is **not** compatible with the GROUP BY clause. I believe this is the reason for the above error, but I may be off track.

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-02 : 15:44:07

Sorry...

Try this....


  SELECT client, consultant, sum(bill_hours)
    FROM billing
   WHERE consultant IN ('John', 'Perry', 'Tom') 
     AND bill_hours > 0.0 
     AND bill_date BETWEEN '2004-05-01' AND '2004-06-01'
GROUP BY client, consultant, bill_date
ORDER BY consultant, client

Brett

8-)

webuser
Starting Member

4 Posts

Posted - 2004-06-02 : 16:03:43

Brett, your revised query is giving me the results that are similar to my old query. That is, the results are generated in the form...

[CODE]
[CONSULTANT] [CLIENT] [TOTAL BILLABLE HOURS]
------------------------------------------------------------
John CompanyA 16.5
John CompanyA 4.0 *
John CompanyB 20.5
John CompanyB 42.5 *
John CompanyB 37.0 *
Perry CompanyC 51.0
Perry CompanyC 26.5 *
Tom CompanyB 23.0
Tom CompanyB 54.5 *
Tom CompanyB 34.5 *
Tom CompanyD 24.0
Tom CompanyE 47.5
[/CODE]

...as opposed to the desired results...

[CODE]
[CONSULTANT] [CLIENT] [TOTAL BILLABLE HOURS]
------------------------------------------------------------
John CompanyA 20.5
John CompanyB 100.0
Perry CompanyC 77.5
Tom CompanyB 112.0
Tom CompanyD 24.0
Tom CompanyE 47.5
[/CODE]

If you notice, the billable hours are aggregated for a consultant-client pair in the second results table. Wheras in the first results table, the billable hours are _not_ aggregated for a consultant-client pair.

I apologize if my question was not clear enough in the first place. But the second set of results is what I am looking for, where the billable hours per consultant per client are aggregated, and there is only **one** record for a consultant-client association.

Thanks.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-02 : 16:08:24

Try this:


  SELECT client, consultant, sum(bill_hours)
    FROM billing
   WHERE consultant IN ('John', 'Perry', 'Tom') 
     AND bill_hours > 0.0 
     AND bill_date BETWEEN '2004-05-01' AND '2004-06-01'
GROUP BY client, consultant---, bill_date
ORDER BY consultant, client

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-02 : 16:26:32

quote:
Originally posted by webuser

Brett, your revised query is giving me the results that are similar to my old query.

I don't beleieve it...

Did you run that query in QA?

Brett

8-)

webuser
Starting Member

4 Posts

Posted - 2004-06-02 : 16:33:18

Yippee...! I got the results I wanted because of help from you both.

Thanks for also teaching me that WHERE and GROUP BY/HAVING can be used together. It feels good to be corrected/disassociated from a wrong notion. :)

Thanks Brett and <drymchaser>!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources