HeFmSSVJ

1SQL Reporting Aggregates and Joining Issues
2Week Of     Action 1  Action 2  Action 3  
3----------  --------  --------  --------
42011-11-07  34        55        35  
52011-11-14  34        55        35
6	
7CREATE TABLE WorkOrderHistory
8(
9WorkOrderHistoryID int, --PK
10WorkOrderActionID int,
11DateCompleted datetime
12)
13
14CREATE TABLE WorkOrderAction
15(
16WorkOrderActionID int --PK
17)
18
19DECLARE
20@StartDate DateTime,
21@EndDate DateTime,
22@SeasonID int;
23
24SET @SeasonID = 16;
25SELECT @StartDate =  '2011-09-01',
26@EndDate = ISNULL((SELECT TOP 1 StartDate FROM Season WHERE SeasonID > @SeasonID), GETDATE()) -- End date will be set to the current date if no season exists beyond the current
27
28--Method 3 Inner Joins. This fails because of my attempt to join on the WeekOf alias to the DATEADD function
29SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf,
30ArtworkCapture.WOsProcessed AS ArtworkCapture
31FROM WorkOrderHistory WOH (NOLOCK)
32INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
33INNER JOIN (SELECT COUNT (*) AS WOsProcessed,
34DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf
35FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
36WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
37AND WOH.WorkOrderActionID = 1 --Artwork Capture
38GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')) ArtworkCapture ON WOH.WeekOf = ArtworkCapture.WeekOf
39WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
40GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')
41ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')
42
43--Method 2 Subqueries. I can not figure out how to properly form this query.
44SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf,
45(SELECT COUNT (*)
46FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
47WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
48AND WOH.WorkOrderActionID = 1 --Artwork Capture
49GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'))
50AS ArtworkCapture,
51(SELECT COUNT (*)
52FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
53WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
54AND WOH.WorkOrderActionID = 3 --Art Entry
55GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'))
56AS ArtEntry
57FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
58WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
59GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')
60ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')
61
62--This query gives me all of the data I need but it is not aggregated, so there is a record for each action per week so [2011-11-07 - 1 - 34], [2011-11-14 - 1 - 34], [2011-11-07 - 2 - 55], [2011-11-14 - 1 - 55].
63SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf,
64WOA.WorkOrderActionID, COUNT (*) AS WorkOrdersProcessed
65FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
66WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
67GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'), WOA.WorkOrderActionID
68ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'), WOA.WorkOrderActionID
69	
70Declare  @WorkOrderHistory as table
71(
72WorkOrderHistoryID int, --PK
73WorkOrderActionID int,
74DateCompleted datetime
75)
76
77declare @WorkOrderAction as TABLE
78(
79WorkOrderActionID int, --PK
80Name varchar (50)
81)
82
83Insert into @WorkOrderAction 
84Values (1, 'Action1'),
85      (2, 'Action2'),
86       (3, 'Action3')
87
88INSERT INTO @WorkOrderHistory 
89VALUES (1, 1, '2011-11-07'),
90(2, 1, '2011-11-07'),
91 (3, 1, '2011-11-07'),
92 (4, 2, '2011-11-07'),
93 (5, 3, '2011-11-07'),
94 (6, 3, '2011-11-07'),
95 (8, 2, '2011-11-14'),
96 (9, 2, '2011-11-14'),
97 (10, 2, '2011-11-14'),
98 (11, 3, '2011-11-14'),
99 (12, 3, '2011-11-14'),
100 (13, 1, '2011-11-14')
101SELECT datecompleted, 
102       action1, 
103       action2, 
104       action3 
105FROM   (SELECT woa.name, 
106               COUNT(woh.workorderhistoryid) AS kount, 
107               datecompleted 
108        FROM   @WorkOrderHistory woh 
109               INNER JOIN @workOrderAction woa 
110                 ON woh.workorderactionid = woa.workorderactionid 
111        GROUP  BY woa.name, 
112                  datecompleted  ) p PIVOT ( SUM(kount) FOR p.name IN ( 
113       [Action1], 
114       [Action2], [Action3] ) ) AS pvt ​
115	
116datecompleted      action1 action2 action3 
117------------------ ------- ------- ------- 
1182011-11-07 0:00:00 3       1       2       
1192011-11-14 0:00:00 1       3       2