I am working on a MS Access frontend with a SQL Server 2017 as backend storing tables and views.
我正在使用SQL Server 2017作为后端存储表和视图的MS Access前端。
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.
我想根据股票交易表计算(在SQL Server端)仓库(Lood)和现场仓库(Baustelle)的库存数量(Bestand),剩余库存(Restbestand)和预留库存(Reserviert)。
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.
交易可能是盘点(Bestandserfassung),stockcorrection(Bestandskorrektur),转移到库存(Einlagern),库存释放(Auslagern)和重新安置(Umlagern)。大多数交易都有来源和目的地以及物料的数量和类型。
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.
编辑:澄清:Restbestand是ZielTyp ='Lagerplatz'的剩余股票以及QuellTyp ='Lagerplatz'a.k.仓库。它是计算股票+交易。每年从仓库交易Buchungsart ='Bestandserfassung'开始,以设置库存中三种不同材料(标准,rutschhemmend,dick)的起始数量(Bestand)。也许某些材料会丢失,然后可以纠正这些数量。因此,第7行使用Buchungsart ='Bestandskorrektur'(500x标准+ -10x标准= 490x标准)。这两种交易类型“Bestandserfassung / Bestandskorrektur”没有来源,因为它只影响目标仓库。也许某个仓库的区域库存太少,然后可以将物料从源仓库发送到此目的地仓库。这是Buchungsart ='Umbuchung'。添加三个交易可以获得库存数量(Bestand)。在这种情况下,目的地仓库始终是ZielBez。一年多来,材料离开仓库,稍后返回。这是Buchungsart =,Auslagern'或者Einlagern'。如果物料离开数量是负的并且仓库是源(QuellBez),如果它返回它是正的并且仓库是目的地。
I tried several SUM OVER statements, but I was unable to achieve my goal.
我尝试了几个SUM OVER语句,但我无法实现目标。
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?
但是三个Sum colomns没有考虑到相关的源和目的地。我怎样才能实现每个仓库/物料的数量总和在一起,即使仓库在一个交易中是源,另一个是它是目的地?
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:
假设它是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
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.
我很乐意得到你的解释。
1 个解决方案
#1
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
#1
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