SQL Record Selectons by Group - 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

SQL Record Selectons by Group

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2007-06-22 : 14:51:14

I have a set of timecard records with a projectNumber, taskNumber and TimeCardHours:

Rec# projectNumbeer taskNumber Employee timeCardHours
1 123 1.01.Test1 John 4
2 123 1.01.Test1 John 6
3 123 1.99.Test3 Mary 8
4 123 1.02.Test5 Mary 4
5 123 1.06.Test6 Mary 4
6 123 1.08.xyz5 Mary 6
7 123 1.03.xyz7 Jane 8
8 123 1.01.xyz4 Jane 5
9 123 1.04.xyz7 Jane 3
10 123 1.05.Test6 Mary 4
11 123 1.07.Test6 Mary 4

I have the following fixed set of tasks:

taskID taskNumberPrefix Task Description phaseID
1 1.01 Planning 1
2 1.02 Design 2
3 1.03 Requirements 2
4 1.04 Coding 3
5 1.05 Testing 4
6 1.06 Build 3
7 1.07 Packaging 3
8 1.08 Documentation 5
9 1.99 Management 1

Finally, I have different phases:

1 Planning
2 Analysis
3 Development
4 Testing
5 Documentation

What I would like to do is obtain a summary of timecard hours for each phase.

So for example, in Phase 1, Planning, this has two tasks based on PhaseID=1, Planning and Management which have task number prefixes of 1.01 and 1.99 respectively.

What I would like to is sum all the timecard records are LIKE 1.01% or 1.99% so my final results based on the above data would provide the phase name and total hours.

Planning Phase total Hours
Planning 23 (Sum of records 1,2,3,8)
Analysis 12 (Sum of records 4,7)
Coding 8 (Sum of records 5,9,11)
Testing 4 (Sum of record 10)
Documentation 6 (Sum of record 6)

I have tried various select statements, but helps up when I want to have TaskNumberPrefix LIKE taskNumber%.

Thanks for any assistance.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-22 : 15:06:44

Something like this:


select P.Description, sum(Tc.timeCardHours)
from phases P
left join tasks T On P.PhaseId = T.phaseID
join Timecard TC on Tc.taskNumber like T.taskNumberPrefix + '%' 
Group by P.Description

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bielen
Yak Posting Veteran

97 Posts

Posted - 2007-06-23 : 19:55:47

Thank you your help. The script pointed me in the right direction. Here's my final script:

SELECT P.phaseDesc, SUM(TC.timecardHours) AS totalPhaseHours, PD.originalBudget, PD.newBudget
FROM TCD_PHASES P LEFT OUTER JOIN
TCD_TASKS T ON P.phaseID = T.phaseID LEFT OUTER JOIN
TCD_PHASES_DETAIL PD ON PD.phaseID = P.phaseID AND PD.projectNumber = 9992 INNER JOIN
TCD_DATA TC ON TC.taskNumber LIKE T.taskNumberPrefix + '%'
WHERE (TC.projectNumber = 9992)
GROUP BY P.phaseDesc, PD.originalBudget, PD.newBudget, P.phaseID
ORDER BY P.phaseID

bielen
Yak Posting Veteran

97 Posts

Posted - 2007-06-28 : 13:44:10

I have one follow-up question. The script I have currently displays a phase from TCD_PHASES only when a I have corresponding records in the TCD_DATA table. How can I modify the script to always display all TCD_PHASES regardless if there is matching hours available or not (i.e. SUM(TC.timecardHours) = 0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-28 : 16:00:32

Move the WHERE clause to the join should do the trick:

SELECT P.phaseDesc, SUM(TC.timecardHours) AS totalPhaseHours, PD.originalBudget, PD.newBudget
FROM TCD_PHASES P LEFT OUTER JOIN
TCD_TASKS T ON P.phaseID = T.phaseID LEFT OUTER JOIN
TCD_PHASES_DETAIL PD ON PD.phaseID = P.phaseID AND PD.projectNumber = 9992 INNER JOIN
TCD_DATA TC ON TC.taskNumber LIKE T.taskNumberPrefix + '%'
AND TC.projectNumber = 9992
WHERE (TC.projectNumber = 9992)
GROUP BY P.phaseDesc, PD.originalBudget, PD.newBudget, P.phaseID
ORDER BY P.phaseID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources