PIVOT 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

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT help

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-30 : 12:22:27

Hi,

Given the following tables

Jobs (JobID, ShiftID, EquipmentID, StartTime, EndTime)
Shift (ShiftID, ShiftName, StartTime, EndTime)
Equipment (EquipmentID, EquipmentDescr)

I would appreciate your help in doing a pivot query to get following results

EquipmentDescr | ShiftName | ShiftTotal Hours | Shift Assigned Hours
Saw #1 | Shift 1 | 300 | 250

ShiftTotal Hours is the sum of the hours/minutes of that shift
Shift Assigned Hours is the sum of jobs assigned to a specific shift and on specific equipment

Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 12:51:12

will be you be using a single equipment for a job?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-30 : 13:19:49

Yes single equipment and single shift

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 13:32:44

quote:
Originally posted by yosiasz

Yes single equipment and single shift

SELECT e.EquipmentDescr , s.ShiftName,
SUM(DATEDIFF(ss,s.StartTime, s.EndTime))/60 AS [ShiftTotal Hours],
j.[Shift Assigned Hours]
FROM Shift s
INNER JOIN (SELECT ShiftID,
            SUM(DATEDIFF(ss,j.StartTime, j.EndTime))/60 AS Shift Assigned Hours
FROM Jobs 
GROUP BY ShiftID)j
ON j.ShiftID=s.ShiftID
INNER JOIN Equipment e
ON e.EquipmentID=j.EquipmentID
GROUP BY e.EquipmentDescr , s.ShiftName

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-30 : 13:59:46

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-30 : 14:03:18

Thanks very much !!!! this is very nice. but how do I pivot this in order to see it as follows
Items in bracket are just FYI ...
{EquipmentDescr | ShiftName | ShiftTotal Hours | Shift Assigned Hours }
Saw #1 | Shift 1 | 300 | 250

Much obliged

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 01:11:56

quote:
Originally posted by yosiasz

Thanks very much !!!! this is very nice. but how do I pivot this in order to see it as follows
Items in bracket are just FYI ...
{EquipmentDescr | ShiftName | ShiftTotal Hours | Shift Assigned Hours }
Saw #1 | Shift 1 | 300 | 250

Much obliged

Didint get that. are you telling you want them to be column headers?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-07-01 : 10:13:18

Hi Visakh. yes I wanted them to be table heads.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 13:35:50

quote:
Originally posted by yosiasz

Hi Visakh. yes I wanted them to be table heads.

then what will be data shown under each column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources