Reporting Hours by Day (Split Record)

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

Reporting Hours by Day (Split Record)

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2011-03-16 : 10:53:19

I have the following query that returns the weekly time information in a single record for sat-fri:

timeID project_number project_name task_number task_type sat sun mon tue wed thu fri line_total
123 1000 Project #1 Task #1 1.52 Hours Worked NULL NULL 7.5 11 9 9 8 44.5
124 1001 Project #2 Task#2 1.19 Hours Worked NULL NULL NULL NULL 1 NULL NULL 1
125 1002 Project #3 Task #3 Design 1.03 Hours Worked NULL NULL 1 NULL NULL NULL NULL 1

I would like to have them return as:

Saturday
project_number hour
1000 0
1001 0
1002 0

Sunday
project_number hour
1000 0
1001 0
1002 0

Monday
project_number hour
1000 0
1001 7.5
1002 1

Tuesday
project_number hour
1000 9
1001 1
1002 0

continuing through the rest of the week? Any suggestions on how I should rework the query?

Query:

SELECT ts.timeID, ts.project_number, p.PROJ_NAME, tsk.TASK_NAME, ts.task_number, ts.task_type, ts.sat, ts.sun, ts.mon, ts.tue, ts.wed, ts.thu, ts.fri, SUM(ISNULL(ts.sat, 0) + ISNULL(ts.sun, 0) + ISNULL(ts.mon, 0) + ISNULL(ts.tue, 0) + ISNULL(ts.wed, 0) + ISNULL(ts.thu, 0) + ISNULL(ts.fri, 0)) AS lineTotal
FROM OPI_TIMESHEETS AS ts LEFT OUTER JOIN TIME_PROJECT AS p ON p.PROJ_NO = ts.project_number LEFT OUTER JOIN TIME_TASK AS tsk ON p.PROJ_NO = tsk.PROJ_NO AND ts.task_number = tsk.TASK_NO
WHERE (ts.email_address = 'test@test.com') AND (ts.export_line = 1)
GROUP BY ts.timeID, ts.project_number, p.PROJ_NAME, tsk.TASK_NAME, ts.task_number, ts.task_type, ts.sat, ts.sun, ts.mon, ts.tue, ts.wed, ts.thu, ts.fri
ORDER BY ts.project_number, ts.task_number

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-16 : 15:10:11

well the specified format seems to be Report Designer Level, for which you may use the data set as following.

Select 'Saturday' as [WeekDay],TS.Project_Number,TS.sat
FROM OPI_TIMESHEETS AS

Union All

Select 'Sunday' as [WeekDay],TS.Project_Number,TS.sun
FROM OPI_TIMESHEETS AS

Union All

Select 'Monday' as [WeekDay],TS.Project_Number,TS.mon
FROM OPI_TIMESHEETS AS

Union All

Select 'Tuesday' as [WeekDay],TS.Project_Number,TS.tue
FROM OPI_TIMESHEETS AS
.
.
.
.

Cheers
MIK

bielen
Yak Posting Veteran

97 Posts

Posted - 2011-03-16 : 15:20:42

Thank you. I also found UNION ALL and my script is almost idential. One question, I have LEFT JOINS which are repeated within each UNION. Can I join on all the UNIONs or must I do the joins individually?

I have the joins working individually, but I'd like to improve the overall query execution time.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-16 : 15:30:06

As per your original post's query I think there is no need for Left Join in your case, as all required columns are the part of OPI_timeSheets table as well as the ones used in Where Clause. So even if you don't join it with the other table you should get same result. Try and check it.

Cheers
MIK

bielen
Yak Posting Veteran

97 Posts

Posted - 2011-03-16 : 16:56:33

I originally only provided a snippet of the output. I would want to get data from other joined tables such as the project name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources