SQL Query :Is it possible to count / group based on date with a sum over the group 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
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.