1

I have the following question: To determine the progress of a production process the following records are stored:

+-------+-------------------------+-------+-------+
| Order | Date                    | Count | State |
+-------+-------------------------+-------+-------+
| X     | 2018-02-10 00:00:00.000 | 10    | 0     |
| X     | 2018-02-11 00:00:00.000 | 4     | 1     |
| X     | 2018-02-12 00:00:00.000 | 2     | 2     |
| X     | 2018-02-13 00:00:00.000 | 3     | 1     |
| X     | 2018-02-14 00:00:00.000 | 1     | 3     |
+-------+-------------------------+-------+-------+

The result of the query should be:

+-------+-------+-------+
| Order | Count | State |
+-------+-------+-------+
| X     | 3     | 0     |
| X     | 5     | 1     |
| X     | 1     | 2     |
| X     | 1     | 3     |
+-------+-------+-------+
  • 10 items are initially booked on state 0
  • On 2018-02-11 4 items are progressed to state 1, resulting in 6 remaining items on state 0 and 4 items on state 1.
  • On 2018-02-12 2 items are progressed from state 1 to state 2, resulting in 6 items on state 0, 2 items on state 1 and 2 items on state 2.
  • On 2018-02-13 3 items are progressed from state 0 to state 1, resulting in 3 items remaining on state 0, 5 items on state 1 and 2 items on state 2.
  • On 2018-02-14 1 items progressed from state 2 to 3, resulting in 3 items remaining on state 0, 5 items on state 1 and 1 item on state 2 and 1 item on state 3.

It is not possible to skip a state.

Sample script:

USE [tempdb]
GO

CREATE TABLE [dbo].[OrderItemState](
    [Gid] uniqueidentifier not null 
            constraint PK_OrderItemState primary key nonclustered
            constraint DC_OrderItemState default newid(),
    [Order] nchar(16) not null,
    [Date] datetime not null,
    [Count] int not null,
    [State] int not null
)
GO

insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-10', 10, 0)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-11', 4, 1)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-12', 2, 2)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-13', 3, 1)
insert into [dbo].[OrderItemState] values (newid(), 'X', '2018-02-14', 1, 3)
GO
0

2 Answers 2

2

In my opinion window functions will be the cleanest way to accomplish this (you didn't specify your version, so I'm assuming you're using 2012 or later).

WITH Progress AS
(
SELECT [Order], State,
  SUM(Count) AS ItemsProgressedToThisState,
  LEAD(SUM(Count), 1, 0) OVER (PARTITION BY [Order] ORDER BY State) AS ItemsProgressedPastThisState
FROM OrderItemState
GROUP BY [Order], State
)
SELECT [Order], State, ItemsProgressedToThisState - ItemsProgressedPastThisState AS [Count]
FROM Progress
ORDER BY [Order], State

The CTE isn't really necessary, you can subtract the SUM and LEAD directly. I just wanted to make the logic clear: count the number of items that have reached the current state, and then subtract the number of items that have reached the next state.

1

The key is to count all states and subtract them from precedent:

SELECT
   [Order]
   ,SUM(CASE WHEN ois.[State] = 0 THEN ois.[Count] ELSE 0 END) as [State_0]
   ,SUM(CASE WHEN ois.[State] = 1 THEN ois.[Count] ELSE 0 END) as [State_1]
   ,SUM(CASE WHEN ois.[State] = 2 THEN ois.[Count] ELSE 0 END) as [State_2]
   ,SUM(CASE WHEN ois.[State] = 3 THEN ois.[Count] ELSE 0 END) as [State_3]
FROM [dbo].[OrderItemState] as ois
GROUP BY [Order]

output here:

Order   State_0 State_1 State_2 State_3
X       10      7       2       1

The rest is just to obtain your output:

SELECT 
 [Order]
 ,[Count]
 ,RIGHT([State],1) AS [State]
FROM
(
SELECT
  grp.[Order]
  ,ISNULL(grp.[State_0],0) - ISNULL(grp.[State_1],0) as [State_0]
  ,ISNULL(grp.[State_1],0) - ISNULL(grp.[State_2],0) as [State_1]
  ,ISNULL(grp.[State_2],0) - ISNULL(grp.[State_3],0) as [State_2]
  ,ISNULL(grp.[State_3],0) as [State_3]
FROM
(
SELECT
   [Order]
   ,SUM(CASE WHEN ois.[State] = 0 THEN ois.[Count] ELSE 0 END) as [State_0]
   ,SUM(CASE WHEN ois.[State] = 1 THEN ois.[Count] ELSE 0 END) as [State_1]
   ,SUM(CASE WHEN ois.[State] = 2 THEN ois.[Count] ELSE 0 END) as [State_2]
   ,SUM(CASE WHEN ois.[State] = 3 THEN ois.[Count] ELSE 0 END) as [State_3]
FROM [dbo].[OrderItemState] as ois
GROUP BY [Order]
) grp
)s
UNPIVOT 
  (
    [Count] FOR [State] IN ([State_0],[State_1],[State_2],[State_3])
  ) AS p


Order   Count   State
X       3       0
X       5       1
X       1       2
X       1       3

dbdiffle here

1
  • Nice solution, at this moment this will work for me. But can it also be done (and how) with a recursive common table expression, so the number of states is not fixed?
    – pexxxy
    Commented Feb 26, 2018 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.