Query Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

Big Bald
Starting Member

3 Posts

Posted - 2008-05-29 : 10:19:53

Greetings,

I am just getting started with learning SQL. I have what I hope is a simple question.

I have this view that I created on our database that contains all the data I need. This view contains travel hours by date. I am able to write two queries from this view, one that gives me the total travel hours for each employee and one that gives me the travel hours per month for each employee. I am trying to create a query that will list the employee name, the hours traveled for each month and the total hours for the year all on the same row.

EX
StaffPerson, Joe 0 25 0 3 5.... 33

I am thinking I am need to create a table for the monthly hours but i am not totally sure. Thanks for any and all help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 12:17:10

[code]SELECT a.EmployeeID,a.Jan,a.Feb,...,a.Dec,b.TotalHours
FROM
(SELECT EmployeeID,
SUM(CASE WHEN MONTH(Date)=1 THEN TravelHours ELSE 0 END)AS Jan,
SUM(CASE WHEN MONTH(Date)=2 THEN TravelHours ELSE 0 END)AS Feb,
SUM(CASE WHEN MONTH(Date)=3 THEN TravelHours ELSE 0 END)AS Mar,
SUM(CASE WHEN MONTH(Date)=4 THEN TravelHours ELSE 0 END)AS Apr,
SUM(CASE WHEN MONTH(Date)=5 THEN TravelHours ELSE 0 END)AS May,
SUM(CASE WHEN MONTH(Date)=6 THEN TravelHours ELSE 0 END)AS Jun,
....
SUM(CASE WHEN MONTH(Date)=12 THEN TravelHours ELSE 0 END)AS Dec
FROM YourView
GROUP BY EmployeeID)a
INNER JOIN
(
SELECT EmployeeID,SUM(TravelHours) AS Total Hours
FROM YourView
GROUP BY EmployeeID)b
on b.EmployeeID=a.EmployeeID[/code]

Big Bald
Starting Member

3 Posts

Posted - 2008-05-29 : 14:04:45

Thank you very much. I was able to extract exactly what I needed from this.

--Jerrome Blackman
--Systems Admin

Big Bald
Starting Member

3 Posts

Posted - 2008-05-29 : 15:37:45

I have another question which may be two fold but part of this. What if I pick say the date range "12/01/2007 - 4/30/2008". Is there a way to arrange the columns by the date range the user enters and how do I filter out the months not mention in that range.

Ex.
Dec Jan Feb Mar Apr GrandTotal
12 13 15 0 5 45

Thanks again for any and all help.

--Jerrome Blackman
--Systems Admin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources