Using SUM in Query to total hours based on Months.

[silversubey]

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

SELECT CltBudget.CBudCltName AS Client
, SUM(CltBudget.CBudfee) AS 'Total Dollars'
, SUM(CltBudget.CBudhours) AS 'Total Hours'
FROM VPM.dbo.CltBudget CltBudget, CltDue
WHERE CltBudget.CBudDueID = CltDue.CDID AND ((CltDue.CDTarget Between '01-01-2007' And '12-31-2008')
AND (CltDue.CDStatus Not In ('Job Cancelled', 'Delivered', 'Job On Hold')
AND (CltDue.CDCltID Not In (1034,54,1404,1499))))
GROUP BY CltBudget.CBudCltName
ORDER BY CltBudget.CBudCltName

(CltDue.CDTarget) 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.CBudCltName AS Client
, SUM(CltBudget.CBudfee) AS 'Total Dollars'
, SUM(CltBudget.CBudhours) AS 'Total Hours'
, COUNT(CltBudget.CBudDueID) AS 'Project Count'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 1 THEN CltBudget.CBudHours ELSE 0 END) AS 'January'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 2 THEN CltBudget.CBudHours ELSE 0 END) AS 'February'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 3 THEN CltBudget.CBudHours ELSE 0 END) AS 'March'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 4 THEN CltBudget.CBudHours ELSE 0 END) AS 'April'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 5 THEN CltBudget.CBudHours ELSE 0 END) AS 'May'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 6 THEN CltBudget.CBudHours ELSE 0 END) AS 'June'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 7 THEN CltBudget.CBudHours ELSE 0 END) AS 'July'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 8 THEN CltBudget.CBudHours ELSE 0 END) AS 'August'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 9 THEN CltBudget.CBudHours ELSE 0 END) AS 'September'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 10 THEN CltBudget.CBudHours ELSE 0 END) AS 'October'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 11 THEN CltBudget.CBudHours ELSE 0 END) AS 'November'
, SUM(CASE MONTH(CltDue.CDTarget) WHEN 12 THEN CltBudget.CBudHours ELSE 0 END) AS 'December'
FROM VPM.dbo.CltBudget CltBudget, CltDue
WHERE CltBudget.CBudDueID = CltDue.CDID AND ((CltDue.CDTarget between '01-01-2007' AND '12-31-2008')
AND (CltDue.CDStatus Not In ('Job Cancelled', 'Delivered', 'Job On Hold')
AND (CltDue.CDCltID Not In (1034,54,1404,1499))))
GROUP BY CltBudget.CBudCltName
ORDER BY CltBudget.CBudCltName