Блог

Я пытаюсь создать отчет для заказов, выполняемых с помощью различных действий на еженедельной основе. Не могли бы вы, ребята, помочь мне с этим?

Требуемый отчет (простой…правильно):

 Week Of     Action 1  Action 2  Action 3  
----------  --------  --------  --------
2011-11-07  34        55        35  
2011-11-14  34        55        35
  

И вот что я пробовал до сих пор, все из которых были жалкими неудачами:

 CREATE TABLE WorkOrderHistory
(
WorkOrderHistoryID int, --PK
WorkOrderActionID int,
DateCompleted datetime
)

CREATE TABLE WorkOrderAction
(
WorkOrderActionID int --PK
)

DECLARE
@StartDate DateTime,
@EndDate DateTime,
@SeasonID int;

SET @SeasonID = 16;
SELECT @StartDate =  '2011-09-01',
@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

--Method 3 Inner Joins. This fails because of my attempt to join on the WeekOf alias to the DATEADD function
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf,
ArtworkCapture.WOsProcessed AS ArtworkCapture
FROM WorkOrderHistory WOH (NOLOCK)
INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
INNER JOIN (SELECT COUNT (*) AS WOsProcessed,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf
FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
AND WOH.WorkOrderActionID = 1 --Artwork Capture
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')) ArtworkCapture ON WOH.WeekOf = ArtworkCapture.WeekOf
WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')
ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')

--Method 2 Subqueries. I can not figure out how to properly form this query.
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf,
(SELECT COUNT (*)
FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
AND WOH.WorkOrderActionID = 1 --Artwork Capture
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'))
AS ArtworkCapture,
(SELECT COUNT (*)
FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
AND WOH.WorkOrderActionID = 3 --Art Entry
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'))
AS ArtEntry
FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')
ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101')

--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].
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101') AS WeekOf,
WOA.WorkOrderActionID, COUNT (*) AS WorkOrdersProcessed
FROM WorkOrderHistory WOH (NOLOCK) INNER JOIN WorkOrderAction WOA (NOLOCK) ON WOH.WorkOrderActionID = WOA.WorkOrderActionID
WHERE WOH.DateCompleted >= @StartDate AND WOH.DateCompleted < @EndDate
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'), WOA.WorkOrderActionID
ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', WOH.DateCompleted), '19000101'), WOA.WorkOrderActionID
  

Похоже, вы хотите преобразовать строки в столбцы. Вам не нужно использовать вложенные запросы, используйте либо Pivot (для SQL 2005 ), либо SUM / CASE.

Примечания: Я не стал утруждать себя вычислением недели, поскольку оно у вас уже есть. Я также добавил имя для WorkOrderAction, поскольку я предполагаю, что у вас есть это, а не просто идентификатор

 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, 1, '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 ​
  

Выдает этот результат

 datecompleted      action1 action2 action3 
------------------ ------- ------- ------- 
2011-11-07 0:00:00 3       1       2       
2011-11-14 0:00:00 1       3       2   
  

смотрите пример в data exchange