SQL Server: запуск Sum() через раздел
Я работаю над внешним интерфейсом MS Access с SQL Server 2017 в качестве внутреннего хранилища таблиц и представлений.
Я хочу рассчитать (на стороне SQL Server) количество запаса (Bestand), оставшегося запаса (Restbestand) и зарезервированного запаса (Reserviert) из складов (Lager) и поля Склады (Baustelle) на основе таблицы операций с акциями.
Транзакции могут включать инвентаризацию (Bestandserfassung), коррекцию запасов (Bestandskorrektur), перевод на склад (Einlagern), выпуск со склада (Auslagern) и перемещение (Umlagern). Большинство транзакций имеют источник и назначение, а также количество и тип материала.
РЕДАКТИРОВАТЬ: Для пояснения: Restbestand - это оставшийся запас для склада ZielTyp='Lagerplatz', а также хранилища QuellTyp='Lagerplatz'. Рассчитывается акции + транзакции. Каждый год начинается с транзакции Buchungsart='Bestandserfassung' для склада, чтобы установить начальное количество (Bestand) трех различных материалов (Standard, rutschhemmend, Dick) на складе. Возможно, некоторые куски материала будут потеряны, тогда это количество можно исправить. Поэтому Buchungsart='Bestandskorrektur' используется в строке 7 (500x Стандарт + -10x Стандарт = 490x Стандарт). Эти два типа транзакции "Bestandserfassung/Bestandskorrektur" не имеют источника, поскольку они влияют только на склад назначения. Возможно, на каком-то складе слишком мало запасов для своего региона, тогда материал можно отправить с исходного склада на этот склад назначения. Это Бучунгсарт = "Умбучунг". Добавление трех транзакций дает количество акций (Bestand). В этом случае складом назначения всегда является ZielBez. В течение года материал покидает склад и возвращается позже. Это Buchungsart = Auslagern или Einlagern. Если материал выходит, количество является отрицательным, а склад является источником (QuellBez), если он возвращает, оно является положительным, а склад является пунктом назначения.
Я попробовал несколько СУММЕРНЫХ утверждений, но не смог достичь своей цели.
Давайте посмотрим, что я получил:
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
Я был бы очень рад получить ваше объяснение.
1 ответ
Это ваш запрос:
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