我正在开发一个MS Access前端，将SQL Server 2017作为存储表和视图的后端。
我想根据库存交易表计算(在 SQL Server 端)仓库(Lager)和现场仓库 (Baustelle) 的库存数量(Bestand)、剩余库存 (Restbestand) 和预留库存 (Reserviert)。
这些交易可以是盘点(Bestandserfassung)，库存更正(Bestandskorrektur)，转入库存(Einlagern)，从库存中释放(Auslagern)和搬迁(Umlagern)。大多数交易都有来源和目的地以及材料的数量和类型。
编辑：为了澄清： Restbestand是ZielTyp='Lagerplatz'以及QuellTyp='Lagerplatz'又名仓库的剩余库存。它是计算的股票+交易。 每年从仓库的Buchungsart='Bestandserfassung'交易开始，以设置库存中三种不同材料(标准，rutschhemmend，dick)的起始数量(Bestand)。 也许有些材料丢失了，那么这些数量可以纠正。因此，在第 7 行中使用了 Buchungsart='Bestandskorrektur' (500x Standard + -10x Standard= 490x Standard)。 这两种交易类型"Bestandserfassung/Bestandskorrektur"没有来源，因为它只影响目标仓库。 也许某些仓库的库存对于其区域来说太少，那么可以将物料从源仓库发送到此目标仓库。这是Buchungsart='Umbuchung'。 将三笔交易相加得到库存数量(Bestand)。在这种情况下，目标仓库始终是ZielBez。 一年后，物料离开仓库，稍后返回。这是Buchungsart='Auslagern'或'Einlagern'。如果物料离开，则数量为负数，仓库为源(QuellBez)，如果物料返回，则为正数，仓库为目的地。
我尝试了几个 SUM OVER 语句，但我无法实现我的目标。
让我们来看看我得到了什么：
CREATE TABLE [dbo].[TestDaten]
(
[BuchID] [int] NOT NULL,
[BuchPosID] [int] NOT NULL,
[BuchungsArtID] [int] NULL,
[ArtikelID] [int] NOT NULL,
[Position] [int] NOT NULL,
[BuchungsNr] [int] NOT NULL,
[BuchungsDatum] [datetime2](0) NULL,
[BuchungsArt] [nvarchar](255) NULL,
[QuellTyp] [nvarchar](255) NULL,
[ZielTyp] [nvarchar](255) NULL,
[QuellBez] [nvarchar](255) NULL,
[ZielBez] [nvarchar](255) NULL,
[Bezeichnung] [nvarchar](255) NULL,
[Menge] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestDaten] ([BuchID], [BuchPosID], [BuchungsArtID], [ArtikelID], [Position], [BuchungsNr], [BuchungsDatum], [BuchungsArt], [QuellTyp], [ZielTyp], [QuellBez], [ZielBez], [Bezeichnung], [Menge])
VALUES (7, 1, 1, 1, 1, 1, CAST(N'2017-01-01T00:00:00.0000000' AS DateTime2), N'Bestandserfassung', NULL, N'Lagerplatz', NULL, N'Im Wald', N'(Standart)', 500),
(7, 2, 1, 2, 2, 1, CAST(N'2017-01-01T00:00:00.0000000' AS DateTime2), N'Bestandserfassung', NULL, N'Lagerplatz', NULL, N'Im Wald', N'(rutschhemmend)', 250),
(9, 3, 4, 1, 1, 2, CAST(N'2017-02-14T17:06:21.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Oderland', N'(Standart)', -22),
(10, 4, 2, 1, 1, 3, CAST(N'2017-01-05T00:00:00.0000000' AS DateTime2), N'Bestandskorrektur', NULL, N'Lagerplatz', NULL, N'Im Wald', N'(Standart)', -10),
(7, 5, 1, 3, 3, 1, CAST(N'2017-01-01T00:00:00.0000000' AS DateTime2), N'Bestandserfassung', NULL, N'Lagerplatz', NULL, N'Im Wald', N'(dick)', 50),
(9, 7, 4, 1, 2, 2, CAST(N'2017-02-14T17:06:21.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Oderland', N'(Standart)', -1),
(15, 12, 4, 1, 2, 6, CAST(N'2017-12-14T21:20:26.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Rötelbrunnen', N'(Standart)', 0),
(17, 13, 3, 1, 1, 8, CAST(N'2017-12-14T21:23:56.0000000' AS DateTime2), N'Einlagerung', N'Baustelle', N'Lagerplatz', N'BV Bruchhausen-Vilsen', N'Im Wald', N'(Standart)', 4),
(17, 14, 3, 2, 2, 8, CAST(N'2017-12-14T21:23:56.0000000' AS DateTime2), N'Einlagerung', N'Baustelle', N'Lagerplatz', N'BV Bruchhausen-Vilsen', N'Im Wald', N'(rutschhemmend)', 2),
(15, 15, 4, 1, 1, 6, CAST(N'2017-12-14T21:20:26.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Rötelbrunnen', N'(Standart)', -27),
(16, 16, 3, 3, 1, 7, CAST(N'2017-12-14T21:23:06.0000000' AS DateTime2), N'Einlagerung', N'Baustelle', N'Lagerplatz', N'BV Oderland', N'Im Wald', N'(dick)', 15),
(13, 17, 4, 2, 1, 5, CAST(N'2017-12-14T00:00:00.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Bruchhausen-Vilsen', N'(rutschhemmend)', -25),
(13, 18, 4, 1, 2, 5, CAST(N'2017-12-14T00:00:00.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Bruchhausen-Vilsen', N'(Standart)', -25),
(15, 19, 4, 2, 3, 6, CAST(N'2017-12-14T21:20:26.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Im Wald', N'BV Rötelbrunnen', N'(rutschhemmend)', -230),
(17, 20, 3, 1, 1, 8, CAST(N'2017-12-14T21:23:56.0000000' AS DateTime2), N'Einlagerung', N'Baustelle', N'Lagerplatz', N'BV Bruchhausen-Vilsen', N'Im Wald', N'(Standart)', 0),
(17, 21, 3, 2, 2, 8, CAST(N'2017-12-14T21:23:56.0000000' AS DateTime2), N'Einlagerung', N'Baustelle', N'Lagerplatz', N'BV Bruchhausen-Vilsen', N'Im Wald', N'(rutschhemmend)', 0),
(11, 25, 5, 1, 1, 4, CAST(N'2017-12-14T18:06:31.0000000' AS DateTime2), N'Umlagerung', N'Lagerplatz', N'Lagerplatz', N'Soest 123', N'Im Wald', N'(Standart)', 30),
(18, 26, 1, 1, 1, 9, CAST(N'2017-01-02T12:21:30.0000000' AS DateTime2), N'Bestandserfassung', NULL, N'Lagerplatz', NULL, N'Soest 123', N'(Standart)', 125),
(18, 27, 1, 2, 2, 9, CAST(N'2017-01-02T12:21:30.0000000' AS DateTime2), N'Bestandserfassung', NULL, N'Lagerplatz', NULL, N'Soest 123', N'(rutschhemmend)', 75),
(18, 28, 1, 3, 3, 9, CAST(N'2017-01-02T12:21:30.0000000' AS DateTime2), N'Bestandserfassung', NULL, N'Lagerplatz', NULL, N'Soest 123', N'(dick)', 25),
(19, 29, 4, 1, 1, 10, CAST(N'2017-12-29T12:34:52.0000000' AS DateTime2), N'Auslagerung', N'Lagerplatz', N'Baustelle', N'Soest 123', N'BV Oderland', N'(Standart)', -25)
GO
SELECT
BuchID, BuchPosID, BuchungsArtID, ArtikelID,
Position, BuchungsNr, BuchungsDatum, BuchungsArt,
QuellTyp, ZielTyp, QuellBez, ZielBez, Bezeichnung, Menge,
SUM(CASE WHEN BuchungsArtID <= 2 OR BuchungsArtID = 5 THEN Menge ELSE 0 END) OVER (PARTITION BY QuellBez, ZielBez, ArtikelID ORDER BY Buchungsdatum, BuchPosID) AS Bestand,
SUM(Menge) OVER (PARTITION BY QuellBez, ZielBez, ArtikelID ORDER BY Buchungsdatum, BuchPosID) AS Restbestand,
SUM(CASE WHEN BuchungsDatum > '2017-12-14 20:00:00' THEN Menge ELSE 0 END) OVER (PARTITION BY QuellBez, ZielBez, ArtikelID ORDER BY Buchungsdatum, BuchPosID) AS Reserviert
FROM
dbo.TestDaten
ORDER BY
BuchungsDatum, BuchPosID
结果如下所示：
BuchID BuchPosID BuchungsArtID ArtikelID Position BuchungsNr BuchungsDatum BuchungsArt QuellTyp ZielTyp QuellBez ZielBez Bezeichnung Menge Bestand Restbestand Reserviert
-------- ----------- --------------- ----------- ---------- ------------ --------------------- ------------------- ------------ ------------ ----------------------- ----------------------- ----------------- ------- --------- ------------- ------------
7 1 1 1 1 1 2017-01-01 00:00:00 Bestandserfassung NULL Lagerplatz NULL Im Wald (Standart) 500 500 500 0
7 2 1 2 2 1 2017-01-01 00:00:00 Bestandserfassung NULL Lagerplatz NULL Im Wald (rutschhemmend) 250 250 250 0
7 5 1 3 3 1 2017-01-01 00:00:00 Bestandserfassung NULL Lagerplatz NULL Im Wald (dick) 50 50 50 0
18 26 1 1 1 9 2017-01-02 12:21:30 Bestandserfassung NULL Lagerplatz NULL Soest 123 (Standart) 125 125 125 0
18 27 1 2 2 9 2017-01-02 12:21:30 Bestandserfassung NULL Lagerplatz NULL Soest 123 (rutschhemmend) 75 75 75 0
18 28 1 3 3 9 2017-01-02 12:21:30 Bestandserfassung NULL Lagerplatz NULL Soest 123 (dick) 25 25 25 0
10 4 2 1 1 3 2017-01-05 00:00:00 Bestandskorrektur NULL Lagerplatz NULL Im Wald (Standart) -10 490 490 0
9 3 4 1 1 2 2017-02-14 17:06:21 Auslagerung Lagerplatz Baustelle Im Wald BV Oderland (Standart) -22 0 -22 0
9 7 4 1 2 2 2017-02-14 17:06:21 Auslagerung Lagerplatz Baustelle Im Wald BV Oderland (Standart) -1 0 -23 0
13 17 4 2 1 5 2017-12-14 00:00:00 Auslagerung Lagerplatz Baustelle Im Wald BV Bruchhausen-Vilsen (rutschhemmend) -25 0 -25 0
13 18 4 1 2 5 2017-12-14 00:00:00 Auslagerung Lagerplatz Baustelle Im Wald BV Bruchhausen-Vilsen (Standart) -25 0 -25 0
11 25 5 1 1 4 2017-12-14 18:06:31 Umlagerung Lagerplatz Lagerplatz Soest 123 Im Wald (Standart) 30 30 30 0
15 12 4 1 2 6 2017-12-14 21:20:26 Auslagerung Lagerplatz Baustelle Im Wald BV Rötelbrunnen (Standart) 0 0 0 0
15 15 4 1 1 6 2017-12-14 21:20:26 Auslagerung Lagerplatz Baustelle Im Wald BV Rötelbrunnen (Standart) -27 0 -27 -27
15 19 4 2 3 6 2017-12-14 21:20:26 Auslagerung Lagerplatz Baustelle Im Wald BV Rötelbrunnen (rutschhemmend) -230 0 -230 -230
16 16 3 3 1 7 2017-12-14 21:23:06 Einlagerung Baustelle Lagerplatz BV Oderland Im Wald (dick) 15 0 15 15
17 13 3 1 1 8 2017-12-14 21:23:56 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (Standart) 4 0 4 4
17 14 3 2 2 8 2017-12-14 21:23:56 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (rutschhemmend) 2 0 2 2
17 20 3 1 1 8 2017-12-14 21:23:56 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (Standart) 0 0 4 4
17 21 3 2 2 8 2017-12-14 21:23:56 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (rutschhemmend) 0 0 2 2
19 29 4 1 1 10 2017-12-29 12:34:52 Auslagerung Lagerplatz Baustelle Soest 123 BV Oderland (Standart) -25 0 -25 -25
但是三个总和列并没有考虑相应的来源和目的地。 即使仓库在一笔交易中是源，而在另一种情况下是目的地，我怎样才能实现每个仓库/材料的金额相加？
它应该给出如下结果：
BuchID BuchPosID BuchungsArtID ArtikelID Position BuchungsNr BuchungsDatum BuchungsArt QuellTyp ZielTyp QuellBez ZielBez Bezeichnung Menge Bestand Restbestand Reserviert
-------- ----------- --------------- ----------- ---------- ------------ ------------------ ------------------- ------------ ------------ ----------------------- ----------------------- ----------------- ------- --------- ------------- ------------
7 1 1 1 1 1 01.01.2017 00:00 Bestandserfassung NULL Lagerplatz NULL Im Wald (Standart) 500 500 500 0
7 2 1 2 2 1 01.01.2017 00:00 Bestandserfassung NULL Lagerplatz NULL Im Wald (rutschhemmend) 250 250 250 0
7 5 1 3 3 1 01.01.2017 00:00 Bestandserfassung NULL Lagerplatz NULL Im Wald (dick) 50 50 50 0
18 26 1 1 1 9 02.01.2017 12:21 Bestandserfassung NULL Lagerplatz NULL Soest 123 (Standart) 125 125 125 0
18 27 1 2 2 9 02.01.2017 12:21 Bestandserfassung NULL Lagerplatz NULL Soest 123 (rutschhemmend) 75 75 75 0
18 28 1 3 3 9 02.01.2017 12:21 Bestandserfassung NULL Lagerplatz NULL Soest 123 (dick) 25 25 25 0
10 4 2 1 1 3 05.01.2017 00:00 Bestandskorrektur NULL Lagerplatz NULL Im Wald (Standart) -10 490 490 0
9 3 4 1 1 2 14.02.2017 17:06 Auslagerung Lagerplatz Baustelle Im Wald BV Oderland (Standart) -22 0 468 0
9 7 4 1 2 2 14.02.2017 17:06 Auslagerung Lagerplatz Baustelle Im Wald BV Oderland (Standart) -1 0 467 0
13 17 4 2 1 5 14.12.2017 00:00 Auslagerung Lagerplatz Baustelle Im Wald BV Bruchhausen-Vilsen (rutschhemmend) -25 0 225 0
13 18 4 1 2 5 14.12.2017 00:00 Auslagerung Lagerplatz Baustelle Im Wald BV Bruchhausen-Vilsen (Standart) -25 0 442 0
11 25 5 1 1 4 14.12.2017 18:06 Umlagerung Lagerplatz Lagerplatz Soest 123 Im Wald (Standart) 30 30 472 0
15 12 4 1 2 6 14.12.2017 21:20 Auslagerung Lagerplatz Baustelle Im Wald BV Rötelbrunnen (Standart) 0 0 472 0
15 15 4 1 1 6 14.12.2017 21:20 Auslagerung Lagerplatz Baustelle Im Wald BV Rötelbrunnen (Standart) -27 0 472 -27
15 19 4 2 3 6 14.12.2017 21:20 Auslagerung Lagerplatz Baustelle Im Wald BV Rötelbrunnen (rutschhemmend) -230 0 225 -5
16 16 3 3 1 7 14.12.2017 21:23 Einlagerung Baustelle Lagerplatz BV Oderland Im Wald (dick) 15 0 25 65
17 13 3 1 1 8 14.12.2017 21:23 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (Standart) 4 0 472 -23
17 14 3 2 2 8 14.12.2017 21:23 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (rutschhemmend) 2 0 225 -25
17 20 3 1 1 8 14.12.2017 21:23 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (Standart) 0 0 472 -23
17 21 3 2 2 8 14.12.2017 21:23 Einlagerung Baustelle Lagerplatz BV Bruchhausen-Vilsen Im Wald (rutschhemmend) 0 0 225 2
19 29 4 1 1 10 29.12.2017 12:34 Auslagerung Lagerplatz Baustelle Soest 123 BV Oderland (Standart) -25 0 125 -25
编辑：现在我前进了...
/* erste Stufe Quelle + Ziel eleminieren */
WITH myBuchungen1 (BuchPosID, BuchungsArtID, BuchungsNr, BuchungsDatum, BuchungsArt, Lagerplatz, Bezeichnung, Menge)
AS
(
SELECT TOP 100 PERCENT BuchPosID, BuchungsArtID , BuchungsNr, BuchungsDatum, BuchungsArt,
CASE WHEN BuchungsArtID = 4 THEN QuellBez ELSE ZielBez END AS Lagerplatz, /* aus Quelle + Ziel nur noch eine Spalte machen um Rechnen zu erleichtern */
Bezeichnung, Menge
FROM dbo.TestDaten
),
/*zweite Stufe Bestand anhand der neuen Spalte Lagerplatz berechnen*/
myBuchungen2 (BuchPosID, BuchungsArtID, BuchungsNr, BuchungsDatum, BuchungsArt, Lagerplatz, Bezeichnung, Menge, Bestand)
AS
(
SELECT TOP 100 PERCENT BuchPosID, BuchungsArtID ,BuchungsNr, BuchungsDatum, BuchungsArt, Lagerplatz, Bezeichnung, Menge,
SUM(Case when BuchungsArtID <=2 OR BuchungsArtID =5
then Menge
else 0
end)
OVER (Partition by Lagerplatz, Bezeichnung Order by Buchungsdatum, BuchPosID) as Bestand
FROM myBuchungen1
)
/*letzte Stufe Restbestand und Reserviert hinzurechnen*/
SELECT BuchungsNr, BuchungsDatum, BuchungsArt, Lagerplatz, Bezeichnung, Menge, Bestand,
SUM(Case when BuchungsArtID =3 OR BuchungsArtID =4 AND BuchungsDatum < '2017-12-14 20:00:00'
then Menge
else 0
end)
OVER (Partition by Lagerplatz, Bezeichnung Order by Buchungsdatum, BuchPosID)
+ Bestand as Restbestand,
SUM(Case when BuchungsDatum > '2017-12-14 20:00:00'
then Menge
else 0
end)
OVER (Partition by Lagerplatz, Bezeichnung Order by Buchungsdatum, BuchPosID)
* -1 as Reserviert
FROM myBuchungen2
ORDER BY BuchungsDatum, BuchPosID
假设它是 2017-12-14 20：00：00，结果是这样的：
BuchungsNr BuchungsDatum BuchungsArt Lagerplatz Bezeichnung Menge Bestand Restbestand Reserviert
------------ --------------------- ------------------- ------------ ----------------- ------- --------- ------------- ------------
1 2017-01-01 00:00:00 Bestandserfassung Im Wald (Standart) 500 500 500 0
1 2017-01-01 00:00:00 Bestandserfassung Im Wald (rutschhemmend) 250 250 250 0
1 2017-01-01 00:00:00 Bestandserfassung Im Wald (dick) 50 50 50 0
9 2017-01-02 12:21:30 Bestandserfassung Soest 123 (Standart) 125 125 125 0
9 2017-01-02 12:21:30 Bestandserfassung Soest 123 (rutschhemmend) 75 75 75 0
9 2017-01-02 12:21:30 Bestandserfassung Soest 123 (dick) 25 25 25 0
3 2017-01-05 00:00:00 Bestandskorrektur Im Wald (Standart) -10 490 490 0
2 2017-02-14 17:06:21 Auslagerung Im Wald (Standart) -22 490 468 0
2 2017-02-14 17:06:21 Auslagerung Im Wald (Standart) -1 490 467 0
5 2017-12-14 00:00:00 Auslagerung Im Wald (rutschhemmend) -25 250 225 0
5 2017-12-14 00:00:00 Auslagerung Im Wald (Standart) -25 490 442 0
4 2017-12-14 18:06:31 Umlagerung Im Wald (Standart) 30 520 472 0
6 2017-12-14 21:20:26 Auslagerung Im Wald (Standart) 0 520 472 0
6 2017-12-14 21:20:26 Auslagerung Im Wald (Standart) -27 520 472 27
6 2017-12-14 21:20:26 Auslagerung Im Wald (rutschhemmend) -230 250 225 230
7 2017-12-14 21:23:06 Einlagerung Im Wald (dick) 15 50 65 -15
8 2017-12-14 21:23:56 Einlagerung Im Wald (Standart) 4 520 476 23
8 2017-12-14 21:23:56 Einlagerung Im Wald (rutschhemmend) 2 250 227 228
8 2017-12-14 21:23:56 Einlagerung Im Wald (Standart) 0 520 476 23
8 2017-12-14 21:23:56 Einlagerung Im Wald (rutschhemmend) 0 250 227 228
10 2017-12-29 12:34:52 Auslagerung Soest 123 (Standart) -25 125 125 25
待办事项：我需要进一步压缩此事务，就像这两个结果集一样，但我不知道如何？
ZielTyp ZielBez Bezeichnung Bestand Restbestand Reserviert
------------ ----------------------- ----------------- --------- ------------- ------------
Lagerplatz Im Wald (Standart) 490 445 25
Lagerplatz Im Wald (rutschhemmend) 250 50 10
Lagerplatz Im Wald (dick) 50 45 10
Lagerplatz Soest 123 (Standart) 125 99 12
Lagerplatz Soest 123 (rutschhemmend) 75 50 10
Lagerplatz Soest 123 (dick) 25 20 0
Baustelle BV Bruchhausen-Vilsen (Standart) 40 0 0
Baustelle BV Bruchhausen-Vilsen (rutschhemmend) 50 0 0
Baustelle BV Bruchhausen-Vilsen (dick) 2 0 0
Baustelle BV Oderland (Standart) 5 0 0
Baustelle BV Oderland (rutschhemmend) 5 0 0
Baustelle BV Oderland (dick) 3 0 0
Baustelle BV Rötelbrunnen (Standart) 26 0 0
Baustelle BV Rötelbrunnen (rutschhemmend) 25 0 0
Baustelle BV Rötelbrunnen (dick) 5 0 0
转换/枢轴材料和质量
ZielTyp ZielBez Bestand (Standard) Restbestand (standard) Reserviert (standard) Bestand (rutschhemmend) Restbestand (rutschhemmend) Reserviert (rutschhemmend) Bestand (dick) Restbestand (dick) Reserviert (dick)
------------ ----------------------- -------------------- ------------------------ ----------------------- ------------------------- ----------------------------- ---------------------------- ---------------- -------------------- -------------------
Lagerplatz Im Wald 490 445 25 250 225 5 50 45 10
Lagerplatz Soest 123 125 99 12 75 50 10 25 20 0
Baustelle BV Bruchhausen-Vilsen 40 20 2
Baustelle BV Oderland 5 5 3
Baustelle BV Rötelbrunnen 26 25 5
我很高兴得到你的解释。
这是您的查询：
declare @RefDate datetime2 = CAST(N'2017-12-14T20:00:00.0000000' AS DateTime2)
;with
q as (
-- pivoted qty per per operation and calc of Bestand and Restbestand
select *
, Isnull(Bestandserfassung, 0)+Isnull(Bestandskorrektur, 0)+Isnull(Umlagerung, 0) Bestand
, Isnull(Bestandserfassung, 0)+Isnull(Bestandskorrektur, 0)+Isnull(Auslagerung, 0)+Isnull(Einlagerung, 0)+Isnull(Umlagerung, 0) Restbestand
from (
-- get qty per operation per warehouse and save RefDate
select
LagerType, LagerBez, ArtikelID, Bezeichnung, BuchungsArt, q,
case when BuchungsDatum <= @RefDate then 1 else -1 end RefDate
from (
-- keep only transactions on source warehouses
select *, QuellTyp LagerType, QuellBez LagerBez, case when BuchungsArtID in (3,5) then -Menge else Menge end Q
from TestDaten
where QuellBez is not null
union all
-- keep only transactions on destination warehouses
select *, ZielTyp LagerType, ZielBez LagerBez, case when BuchungsArtID in (4) then -Menge else Menge end Q
from TestDaten
where ZielBez is not null
) x
) d
-- pivot sum qty per per operation
pivot (sum(q) for BuchungsArt in (Bestandserfassung, Bestandskorrektur, Auslagerung, Einlagerung, Umlagerung)) p
),
d1 as (
-- add Reserviert as calc of Restbestand after @RefDate
select IsNull(q1.LagerType, q2.LagerType) LagerType, IsNull(q1.LagerBez, q2.LagerBez) LagerBez, IsNull(q1.Bezeichnung, q2.Bezeichnung) ArtVariant, q1.Bestand, q1.Restbestand, q2.Restbestand as Reserviert
from q q1
full join q q2 on q1.LagerBez = q2.LagerBez and q1.ArtikelID = q2.ArtikelID and q1.RefDate = 1 and q2.RefDate = -1
where (ISNULL(q1.RefDate, 0) = 1) or ((q1.RefDate is null) and (ISNULL(q2.RefDate, 0) = -1))
),
d2 as (
-- unpivot to get all combinations operation/article
select LagerType, LagerBez, QtyTyp + ' ' + ArtVariant as QtyPerArt, Qty
from d1
unpivot (Qty for QtyTyp in (Bestand, Restbestand, Reserviert)) u
),
d3 as (
-- pivot on warehouse to get final result
select *
from d2
pivot (sum(qty) for QtyPerArt in (
[Bestand (Standart)],
[Restbestand (Standart)],
[Reserviert (Standart)],
[Bestand (rutschhemmend)],
[Restbestand (rutschhemmend)],
[Reserviert (rutschhemmend)],
[Bestand (dick)],
[Restbestand (dick)],
[Reserviert (dick)])
) p
)
select *
from d3
order by 1 desc,2