0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

Q&A for anime and manga fans

-- Enter Query Title
-- Enter Query Description

Declare  @WorkOrderHistory as table
(
WorkOrderHistoryID int, --PK
WorkOrderActionID int,
DateCompleted datetime
)

declare @WorkOrderAction as TABLE
(
WorkOrderActionID int, --PK
Name varchar (50)
)

Insert into @WorkOrderAction 
Values (1, 'Action1'),
      (2, 'Action2'),
       (3, 'Action3')

INSERT INTO @WorkOrderHistory 
VALUES (1, 1, '2011-11-07'),
(2, 1, '2011-11-07'),
 (3, 1, '2011-11-07'),
 (4, 2, '2011-11-07'),
 (5, 3, '2011-11-07'),
 (6, 3, '2011-11-07'),
 (8, 2, '2011-11-14'),
 (9, 2, '2011-11-14'),
 (10, 2, '2011-11-14'),
 (11, 3, '2011-11-14'),
 (12, 3, '2011-11-14'),
 (13, 5, '2011-11-14')



SELECT datecompleted, 
       action1, 
       action2, 
       action3 
FROM   (SELECT woa.name, 
               COUNT(woh.workorderhistoryid) AS kount, 
               datecompleted 
        FROM   @WorkOrderHistory woh 
               INNER JOIN @workOrderAction woa 
                 ON woh.workorderactionid = woa.workorderactionid 
        GROUP  BY woa.name, 
                  datecompleted  ) p PIVOT ( SUM(kount) FOR p.name IN ( 
       [Action1], 
       [Action2], [Action3] ) ) AS pvt 

fork query

created nov 8 11 conradfrix