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' a.k. Склад. Рассчитывается запас + сделки. Каждый год начинается с транзакции Buchungsart='Bestandserfassung' для склада, чтобы установить начальное количество (Bestand) трех различных материалов (Standard, rutschhemmend, dick) на складе. Возможно какие-то куски материала потеряются, тогда это количество можно будет исправить. Поэтому в строке 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

Но три столбца Sum не учитывают правильный источник и пункт назначения. Как я могу добиться, чтобы суммы для каждого склада/материала суммировались вместе, даже если склад в одной транзакции является источником, а в другом случае - местом назначения?

Это должно дать такие результаты, как:

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

Я был бы очень рад получить ваше объяснение.


sql-server tsql cumulative-sum running-balance
person Java-Jim    schedule 29.12.2017    source источник
comment
Вы хорошо поработали, предоставив тестовые данные и ожидаемый результат, но постарайтесь ограничить свой вопрос основной проблемой, а не добавлять последующие запросы (поворот). Кроме того, я думаю, вы найдете этот инструмент полезным для форматирования ваших данных, чтобы они были более разборчивый   -  person scsimon    schedule 29.12.2017
comment
Одно примечание о remaining stock (Restbestand) and reserved stock (Restbestand) — остальные и зарезервированные одинаковые или это опечатка?   -  person Jorge Campos    schedule 29.12.2017
comment
Генератор таблиц ASCCI отлично работает! Но только если вывод помещается в блок кода. Спасибо сцимон. Отредактировано: резервный запас (Reserviert) это опечатка. Спасибо, Хорхе.   -  person Java-Jim    schedule 29.12.2017
comment
Я не понимаю ваших ожидаемых результатов: Restbestand представляет собой оставшийся запас для Source (QuellBez) или Destination (ZielBez)? Например, в 7-й строке кажется, что 490 вычислено для одного и того же ZielBez (без учета разных QuellBez). Но для 8-й строки 468 вычисляется из предыдущей строки, даже если Im Wald один раз является QuellBez, а в следующий раз ZielBez.   -  person Razvan Socol    schedule 29.12.2017
comment
Попробуйте удалить нерелевантный запас для каждого типа транзакции и оставить только один столбец запасов (только один столбец Bez вместо QuellBez и ZielBez).   -  person Razvan Socol    schedule 29.12.2017
comment
@Ravzan Socol я отредактировал свой вопрос для уточнения.   -  person Java-Jim    schedule 30.12.2017
comment
@Ravzan Socol Большой намек заключался в том, чтобы исключить ненужные столбцы и рассчитать на основе одного столбца Lagerplatz. Это открыло мне глаза, и я использовал CTE в три шага для расчета количества.   -  person Java-Jim    schedule 30.12.2017
comment
ТАК чего тебе еще не хватает?   -  person nimdil    schedule 07.02.2018

Ответы (1)


arrow_upward
0
arrow_downward

Это ваш запрос:

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
person MtwStark    schedule 18.04.2018