Using SUM in Query to total hours based on Months.

[silversubey]

I am using a query to total hours (Cltbudget.CBud hours) and dollars (CltBudget.CBud Fee) Grouped by client names (Cltbudget.CBud CltName).
here is the Query:

SELECT CltBudget.CBudC ltName AS Client
, SUM(CltBudget.C Budfee) AS 'Total Dollars'
, SUM(CltBudget.C Budhours) AS 'Total Hours'
FROM VPM.dbo.CltBudg et CltBudget, CltDue
WHERE CltBudget.CBudD ueID = CltDue.CDID AND ((CltDue.CDTarg et Between '01-01-2007' And '12-31-2008')
AND (CltDue.CDStatu s Not In ('Job Cancelled', 'Delivered', 'Job On Hold')
AND (CltDue.CDCltID Not In (1034,54,1404,1 499))))
GROUP BY CltBudget.CBudC ltName
ORDER BY CltBudget.CBudC ltName

(CltDue.CDTarge t) is the due date for the projects.
here is an example of the output:


Client name Total Dollars Total Hours

I am wanting to be able to Sum the hours for each month as well.

Client name Totaldollars Total Hours jan feb mar etc.

Any help is appreciated, thanks.

[silversubey]

I was able to get this to work woth the following code. If it lokos sloppy, please let me know how to fix it.

SELECT CltBudget.CBudC ltName AS Client
, SUM(CltBudget.C Budfee) AS 'Total Dollars'
, SUM(CltBudget.C Budhours) AS 'Total Hours'
, COUNT(CltBudget .CBudDueID) AS 'Project Count'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 1 THEN CltBudget.CBudH ours ELSE 0 END) AS 'January'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 2 THEN CltBudget.CBudH ours ELSE 0 END) AS 'February'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 3 THEN CltBudget.CBudH ours ELSE 0 END) AS 'March'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 4 THEN CltBudget.CBudH ours ELSE 0 END) AS 'April'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 5 THEN CltBudget.CBudH ours ELSE 0 END) AS 'May'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 6 THEN CltBudget.CBudH ours ELSE 0 END) AS 'June'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 7 THEN CltBudget.CBudH ours ELSE 0 END) AS 'July'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 8 THEN CltBudget.CBudH ours ELSE 0 END) AS 'August'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 9 THEN CltBudget.CBudH ours ELSE 0 END) AS 'September'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 10 THEN CltBudget.CBudH ours ELSE 0 END) AS 'October'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 11 THEN CltBudget.CBudH ours ELSE 0 END) AS 'November'
, SUM(CASE MONTH(CltDue.CD Target) WHEN 12 THEN CltBudget.CBudH ours ELSE 0 END) AS 'December'
FROM VPM.dbo.CltBudg et CltBudget, CltDue
WHERE CltBudget.CBudD ueID = CltDue.CDID AND ((CltDue.CDTarg et between '01-01-2007' AND '12-31-2008')
AND (CltDue.CDStatu s Not In ('Job Cancelled', 'Delivered', 'Job On Hold')
AND (CltDue.CDCltID Not In (1034,54,1404,1 499))))
GROUP BY CltBudget.CBudC ltName
ORDER BY CltBudget.CBudC ltName