9

I am working on a MS Access frontend with a SQL Server 2017 as backend storing tables and views.

I want to calculate (on the SQL Server side) the quantity of stock (Bestand), remaining stock (Restbestand) and reserved stock (Reserviert) from Warehouses (Lager) and field Warehouses (Baustelle) based on a table of stock transactions.

The transactions could be stocktaking (Bestandserfassung), stockcorrection (Bestandskorrektur), transfer to stock (Einlagern), release from stock (Auslagern) and relocate (Umlagern). Most transactions have a source and destination and the quantity and type of material.

EDIT: For clarification: Restbestand is the remaining stock for a ZielTyp='Lagerplatz' as well as a QuellTyp='Lagerplatz' a.k. Warehouse. It is calculated stock + transactions. Each year starts with a transaction Buchungsart='Bestandserfassung' for a Warehouse to set the start quantity (Bestand) of three different materials (Standard, rutschhemmend, dick) on stock. Maybe some pieces of material gets lost, then these quantity can be corrected. Therefore a Buchungsart='Bestandskorrektur' is used in row 7 (500x Standard + -10x Standard= 490x Standard). These two transaction types "Bestandserfassung/Bestandskorrektur" have no source as it affects only the destination warehouse. Maybe some warehouse has too less stock for its region, then material can be sent from a source warehouse to this destination warehouse. This is Buchungsart='Umbuchung'. Adding the three transactions gives the quantity of stock (Bestand). In this cases the destination warehouse is always ZielBez. Over the year material leaves a warehouse and returns later. This is Buchungsart= ‚Auslagern‘ or ‚Einlagern‘. If material leaves the quantity is negativ and the warehouse is the source (QuellBez), if it´s returning it is positive and the warehouse is the destination.

I tried several SUM OVER statements, but I was unable to achieve my goal.

Let's have a look at what I got:

 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

The result looks like this:

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

But the three Sum colomns does not take the correkt source and destination into account. How can i achieve that the amounts for each warehouse/material are summed together even if the warehouse is in one transaction the source and in the other case it is the destination?

It should give results like:

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

EDIT: Now i forged ahead...

/* 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

Asuming it is 2017-12-14 20:00:00 the result is this:

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

TO DO: I need to compress this transactions further more like this two resultsets, but i do not know how?

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

transform / pivot materials and qualtities

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

I would be very glad to have your explanation.

Java-Jim
  • 93
  • 5
  • 9
    You did a good job of providing test data and expected output, but try and limit your question to the core problem versus adding follow on requests (the pivot). Also, I think you'd find [this tool helpful](https://ozh.github.io/ascii-tables/) for formatting your data so that it's more legible – S3S Dec 29 '17 at 14:28
  • 1
    One side note about `remaining stock (Restbestand) and reserved stock (Restbestand)` are the remaining and reserved the same or was it a typo? – Jorge Campos Dec 29 '17 at 14:54
  • The ASCCI Table Generator works great! But only if the output is put in a code block. Thanks scsimon. Edited: reserverd stock (Reserviert) it was a typo. Thanks Jorge – Java-Jim Dec 29 '17 at 15:11
  • I don't understand your expected results: the Restbestand represents the remaining stock for Source (QuellBez) or the Destination (ZielBez)? For example, in the 7th row, 490 seems to be computed for the same ZielBez (ignoring the different QuellBez). But for the 8th row, 468 is computed from the previous row even if the "Im Wald" is once a QuellBez, and next time a ZielBez. – Razvan Socol Dec 29 '17 at 17:05
  • 1
    Try eliminating the non-relevant stock for each transaction type and keep only one stock column (only one Bez column, instead of QuellBez and ZielBez). – Razvan Socol Dec 29 '17 at 17:07
  • @Ravzan Socol i edited my question for clarification. – Java-Jim Dec 30 '17 at 01:49
  • @Ravzan Socol The big hint was eliminating the non-relevant columns and calculate based on a single column Lagerplatz. This openen my mind and i used cte in three steps to calculate the quantities. – Java-Jim Dec 30 '17 at 01:56
  • SO what are you still missing? – nimdil Feb 07 '18 at 13:26

1 Answers1

0

This is your query:

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
MtwStark
  • 3,866
  • 1
  • 18
  • 32