多个产品和人员的FIFO收盘价

本文介绍了多个产品和人员的FIFO收盘价的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

对于要解决的问题，我找到了以下解决方案: stackoverflow/a/26778468/1626443

I have found the following solution to a problem I was trying to solve: stackoverflow/a/26778468/1626443

我希望有一个查询，该查询返回给我investor和isin的FIFO价格收盘价.

I want to have a single query that returns me the FIFO price closing price per investor and isin.

当前查询有效，但是我必须按isin和investor进行过滤(顶部是示例数据):

The current query works, but I have to filter by the isin and investor (the top is the sample data):

CREATE TABLE #CustomerOrderTable ( ID INTEGER NOT NULL, [investor] VARCHAR(255) NOT NULL, [isin] VARCHAR(255) NOT NULL, [datetime] DATETIME NOT NULL, [quantity] FLOAT NOT NULL, [price] FLOAT NOT NULL, [buysell] VARCHAR(255) NOT NULL, PRIMARY KEY (ID) ); GO INSERT INTO #CustomerOrderTable(ID, [investor],[isin],[datetime],[quantity],[price],[buysell]) VALUES (1,'GN0161162','DE6592028664','20180913',7,88,'Buy'),(2,'GN0969461','DE6592028664','20180924',4,452,'Buy'),(3,'GN0969461','DE7478496562','20180929',9,721,'Buy'),(4,'GN0429760','CH1486126118','20180929',9,451,'Buy'),(5,'GN0429760','GB9015779955','20181006',9,817,'Buy'),(6,'GN0969461','DE7478496562','20181013',8,433,'Buy'),(7,'GN0969461','GB9015779955','20181025',10,262,'Buy'),(8,'GN0161162','DE7478496562','20181108',7,278,'Buy'),(9,'GN0429760','GB9015779955','20181109',3,986,'Buy'),(10,'GN0161162','DE6592028664','20181114',1,556,'Buy'),(11,'GN0429760','DE7478496562','20181120',4,434,'Buy'),(12,'GN0429760','GB9015779955','20181121',3,764,'Buy'),(13,'GN0369359','GB9015779955','20181123',7,129,'Buy'),(14,'GN0429760','DE7478496562','20181205',2,686,'Buy'),(15,'GN0969461','DE6592028664','20181231',5,309,'Buy'),(16,'GN0161162','DE9805831979','20190108',3,540,'Buy'),(17,'GN0429760','CH1486126118','20190128',4,940,'Buy'),(18,'GN0429760','CH1486126118','20190131',10,652,'Buy'),(19,'GN0969461','DE7478496562','20190210',8,845,'Buy'),(20,'GN0969461','DE6592028664','20190213',2,981,'Buy'),(21,'GN0969461','DE9805831979','20190214',9,90,'Buy'),(22,'GN0369359','DE6592028664','20190227',7,518,'Buy'),(23,'GN0161162','GB9015779955','20190227',7,930,'Buy'),(24,'GN0429760','GB9015779955','20190312',8,301,'Buy'),(25,'GN0161162','GB9015779955','20190315',5,994,'Sell'),(26,'GN0969461','CH1486126118','20190329',8,928,'Buy'),(27,'GN0161162','DE7478496562','20190330',7,354,'Buy'),(28,'GN0369359','DE7478496562','20190423',1,928,'Buy'),(29,'GN0429760','DE6592028664','20190426',7,599,'Buy'),(30,'GN0161162','DE9805831979','20190428',5,283,'Buy'),(31,'GN0429760','DE9805831979','20190509',7,928,'Buy'),(32,'GN0429760','DE9805831979','20190510',9,954,'Buy'),(33,'GN0429760','CH1486126118','20190518',6,112,'Buy'),(34,'GN0369359','DE9805831979','20190520',8,304,'Buy'),(35,'GN0969461','DE6592028664','20190522',5,657,'Buy'),(36,'GN0161162','DE7478496562','20190526',5,127,'Buy'),(37,'GN0969461','CH1486126118','20190616',10,760,'Buy'),(38,'GN0429760','DE9805831979','20190618',10,659,'Buy'),(39,'GN0369359','DE7478496562','20190620',5,61,'Buy'),(40,'GN0369359','CH1486126118','20190704',5,50,'Buy'),(41,'GN0429760','DE9805831979','20190710',9,375,'Buy'),(42,'GN0969461','DE9805831979','20190722',5,999,'Buy'),(43,'GN0969461','DE6592028664','20190723',8,860,'Buy'),(44,'GN0161162','DE9805831979','20190723',8,80,'Buy'),(45,'GN0161162','GB9015779955','20190805',2,318,'Sell'),(46,'GN0429760','DE6592028664','20190809',6,505,'Buy'),(47,'GN0161162','GB9015779955','20190831',10,127,'Buy'),(48,'GN0429760','DE6592028664','20190831',7,9,'Buy'),(49,'GN0369359','CH1486126118','20190903',6,903,'Buy'),(50,'GN0429760','GB9015779955','20190913',6,430,'Buy'),(51,'GN0161162','DE6592028664','20190914',7,777,'Sell'),(52,'GN0429760','DE9805831979','20190915',5,372,'Buy'),(53,'GN0969461','DE6592028664','20190918',1,598,'Buy'),(54,'GN0429760','CH1486126118','20191018',2,894,'Buy'),(55,'GN0161162','CH1486126118','20191021',4,235,'Buy'),(56,'GN0969461','CH1486126118','20191031',1,427,'Buy'),(57,'GN0161162','DE9805831979','20191119',6,412,'Buy'),(58,'GN0969461','DE6592028664','20191205',2,769,'Sell'),(59,'GN0369359','DE9805831979','20191214',8,418,'Sell'),(60,'GN0429760','DE6592028664','20191219',6,420,'Buy'),(61,'GN0429760','DE7478496562','20191223',6,285,'Buy'),(62,'GN0429760','CH1486126118','20200101',5,106,'Buy'),(63,'GN0161162','GB9015779955','20200108',7,528,'Buy'),(64,'GN0429760','CH1486126118','20200116',7,531,'Buy'),(65,'GN0969461','DE6592028664','20200126',1,632,'Sell'),(66,'GN0429760','GB9015779955','20200129',6,21,'Buy'),(67,'GN0369359','GB9015779955','20200131',5,130,'Sell'),(68,'GN0161162','DE7478496562','20200212',10,222,'Sell'),(69,'GN0161162','CH1486126118','20200216',2,686,'Sell'),(70,'GN0969461','CH1486126118','20200219',10,546,'Buy'),(71,'GN0161162','CH1486126118','20200225',2,601,'Sell'),(72,'GN0369359','GB9015779955','20200305',4,722,'Buy'),(73,'GN0429760','CH1486126118','20200311',10,543,'Buy'),(74,'GN0369359','DE6592028664','20200314',4,145,'Buy'),(75,'GN0429760','DE6592028664','20200323',8,919,'Buy'),(76,'GN0429760','DE9805831979','20200401',1,4,'Buy'),(77,'GN0161162','CH1486126118','20200401',9,320,'Buy'),(78,'GN0429760','DE7478496562','20200406',2,46,'Buy'),(79,'GN0429760','CH1486126118','20200421',5,453,'Buy'),(80,'GN0969461','CH1486126118','20200425',8,150,'Buy'),(81,'GN0429760','DE7478496562','20200501',10,426,'Buy'),(82,'GN0369359','GB9015779955','20200511',4,514,'Buy'),(83,'GN0161162','DE6592028664','20200511',8,467,'Buy'),(84,'GN0969461','DE9805831979','20200518',1,517,'Buy'),(85,'GN0369359','DE6592028664','20200520',6,744,'Buy'),(86,'GN0161162','DE7478496562','20200602',7,567,'Sell'),(87,'GN0969461','CH1486126118','20200604',7,973,'Buy'),(88,'GN0369359','DE6592028664','20200605',2,39,'Sell'),(89,'GN0369359','GB9015779955','20200627',1,770,'Buy'),(90,'GN0369359','DE9805831979','20200709',3,918,'Buy'),(91,'GN0969461','CH1486126118','20200714',7,527,'Buy'),(92,'GN0161162','DE9805831979','20200725',9,281,'Buy'),(93,'GN0161162','GB9015779955','20200729',8,205,'Buy'),(94,'GN0969461','DE9805831979','20200730',5,682,'Buy'),(95,'GN0369359','GB9015779955','20200813',1,833,'Buy'),(96,'GN0161162','DE9805831979','20200816',9,914,'Sell'),(97,'GN0429760','DE7478496562','20200816',6,247,'Buy'),(98,'GN0969461','DE7478496562','20200828',8,822,'Buy'),(99,'GN0161162','DE6592028664','20200830',2,937,'Buy'),(100,'GN0161162','GB9015779955','20200905',5,858,'Buy'); DECLARE @isin nvarchar(12); DECLARE @subaccount nvarchar(20); SET @isin = 'GB9015779955'; SET @subaccount = 'GN0161162'; WITH UnitsCTE AS ( -- GET Total Units Left SELECT SUM( CASE buysell WHEN 'Buy' Then quantity When 'Sell' THEN -quantity ELSE quantity END) AS Units FROM #CustomerOrderTable WHERE investor = @subaccount AND isin = @isin ), PurchaseCTE AS ( -- Get only purchases in reverse order SELECT quantity, price, datetime, ROW_NUMBER() OVER (ORDER BY datetime DESC ) AS RN FROM #CustomerOrderTable WHERE buysell <> 'Sale' AND investor = @subaccount AND isin = @isin ), UnitCost AS ( -- Recursive CTE to get number of units left at each price SELECT CASE WHEN quantity > unitsCTE.Units THEN unitsCTE.Units ELSE quantity END As quantity, price FROM PurchaseCTE CROSS APPLY unitsCTE WHERE RN = 1 UNION ALL SELECT CASE WHEN P1.quantity > (unitsCTE.Units - (SELECT SUM(quantity) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN)) THEN CASE WHEN (unitsCTE.Units - (SELECT SUM(quantity) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN)) < 0 THEN 0 ELSE (unitsCTE.Units - (SELECT SUM(quantity) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN)) END ELSE P1.quantity END, P1.price FROM PurchaseCTE P1 INNER JOIN PurchaseCTE P2 ON P1.RN = P2.RN + 1 CROSS APPLY unitsCTE ) SELECT SUM(quantity) Units, SUM(price * quantity) / SUM(quantity) AS UnitCost, SUM(quantity * price) AS TotalCost FROM UnitCost WHERE quantity > 0 DROP TABLE #CustomerOrderTable

上面查询的当前输出为:

The current output from the query above is:

+-------+----------+-----------+ | Units | UnitCost | TotalCost | +-------+----------+-----------+ | 30 | 363.2 | 10896 | +-------+----------+-----------+

我想要以下输出:

+-----------+--------------+-------+-------------+-----------+ | investor | ISIN | Units | UnitCost | TotalCost | +-----------+--------------+-------+-------------+-----------+ | GN0161162 | DE6592028664 | 11 | 580.6363636 | 6387 | | GN0161162 | DE7478496562 | 2 | 567 | 1134 | | GN0161162 | DE9805831979 | 22 | 563.7727273 | 12403 | | GN0161162 | GB9015779955 | 30 | 363.2 | 10896 | | GN0161162 | CH1486126118 | 9 | 320 | 2880 | | GN0369359 | GB9015779955 | 12 | 567.25 | 6807 | | GN0369359 | DE6592028664 | 15 | 445.0666667 | 6676 | | GN0369359 | DE7478496562 | 6 | 205.5 | 1233 | | GN0369359 | DE9805831979 | 3 | 918 | 2754 | | GN0369359 | CH1486126118 | 11 | 515.2727273 | 5668 | | GN0429760 | CH1486126118 | 58 | 495.5344828 | 28741 | | GN0429760 | GB9015779955 | 35 | 506.2 | 17717 | | GN0429760 | DE7478496562 | 30 | 355.0666667 | 10652 | | GN0429760 | DE6592028664 | 34 | 504.6470588 | 17158 | | GN0429760 | DE9805831979 | 41 | 656.3658537 | 26911 | | GN0969461 | DE6592028664 | 22 | 719.1818182 | 15822 | | GN0969461 | DE7478496562 | 33 | 705.7272727 | 23289 | | GN0969461 | GB9015779955 | 10 | 262 | 2620 | | GN0969461 | DE9805831979 | 20 | 486.6 | 9732 | | GN0969461 | CH1486126118 | 51 | 639.4313725 | 32611 | +-----------+--------------+-------+-------------+-----------+

推荐答案

我将回答我自己的问题，因为我已经使它起作用了.我希望它将对以后的人们有所帮助:

I will answer my own question, because I have made it work. I hope it will help people in the future:

CREATE TABLE #CustomerOrderTable ( ID INTEGER NOT NULL, [investor] VARCHAR(255) NOT NULL, [isin] VARCHAR(255) NOT NULL, [datetime] DATETIME NOT NULL, [quantity] FLOAT NOT NULL, [price] FLOAT NOT NULL, [buysell] VARCHAR(255) NOT NULL, PRIMARY KEY (ID) ); GO INSERT INTO #CustomerOrderTable(ID, [investor],[isin],[datetime],[quantity],[price],[buysell]) VALUES (1,'GN0161162','DE6592028664','20180913',7,88,'Buy'),(2,'GN0969461','DE6592028664','20180924',4,452,'Buy'),(3,'GN0969461','DE7478496562','20180929',9,721,'Buy'),(4,'GN0429760','CH1486126118','20180929',9,451,'Buy'),(5,'GN0429760','GB9015779955','20181006',9,817,'Buy'),(6,'GN0969461','DE7478496562','20181013',8,433,'Buy'),(7,'GN0969461','GB9015779955','20181025',10,262,'Buy'),(8,'GN0161162','DE7478496562','20181108',7,278,'Buy'),(9,'GN0429760','GB9015779955','20181109',3,986,'Buy'),(10,'GN0161162','DE6592028664','20181114',1,556,'Buy'),(11,'GN0429760','DE7478496562','20181120',4,434,'Buy'),(12,'GN0429760','GB9015779955','20181121',3,764,'Buy'),(13,'GN0369359','GB9015779955','20181123',7,129,'Buy'),(14,'GN0429760','DE7478496562','20181205',2,686,'Buy'),(15,'GN0969461','DE6592028664','20181231',5,309,'Buy'),(16,'GN0161162','DE9805831979','20190108',3,540,'Buy'),(17,'GN0429760','CH1486126118','20190128',4,940,'Buy'),(18,'GN0429760','CH1486126118','20190131',10,652,'Buy'),(19,'GN0969461','DE7478496562','20190210',8,845,'Buy'),(20,'GN0969461','DE6592028664','20190213',2,981,'Buy'),(21,'GN0969461','DE9805831979','20190214',9,90,'Buy'),(22,'GN0369359','DE6592028664','20190227',7,518,'Buy'),(23,'GN0161162','GB9015779955','20190227',7,930,'Buy'),(24,'GN0429760','GB9015779955','20190312',8,301,'Buy'),(25,'GN0161162','GB9015779955','20190315',5,994,'Sell'),(26,'GN0969461','CH1486126118','20190329',8,928,'Buy'),(27,'GN0161162','DE7478496562','20190330',7,354,'Buy'),(28,'GN0369359','DE7478496562','20190423',1,928,'Buy'),(29,'GN0429760','DE6592028664','20190426',7,599,'Buy'),(30,'GN0161162','DE9805831979','20190428',5,283,'Buy'),(31,'GN0429760','DE9805831979','20190509',7,928,'Buy'),(32,'GN0429760','DE9805831979','20190510',9,954,'Buy'),(33,'GN0429760','CH1486126118','20190518',6,112,'Buy'),(34,'GN0369359','DE9805831979','20190520',8,304,'Buy'),(35,'GN0969461','DE6592028664','20190522',5,657,'Buy'),(36,'GN0161162','DE7478496562','20190526',5,127,'Buy'),(37,'GN0969461','CH1486126118','20190616',10,760,'Buy'),(38,'GN0429760','DE9805831979','20190618',10,659,'Buy'),(39,'GN0369359','DE7478496562','20190620',5,61,'Buy'),(40,'GN0369359','CH1486126118','20190704',5,50,'Buy'),(41,'GN0429760','DE9805831979','20190710',9,375,'Buy'),(42,'GN0969461','DE9805831979','20190722',5,999,'Buy'),(43,'GN0969461','DE6592028664','20190723',8,860,'Buy'),(44,'GN0161162','DE9805831979','20190723',8,80,'Buy'),(45,'GN0161162','GB9015779955','20190805',2,318,'Sell'),(46,'GN0429760','DE6592028664','20190809',6,505,'Buy'),(47,'GN0161162','GB9015779955','20190831',10,127,'Buy'),(48,'GN0429760','DE6592028664','20190831',7,9,'Buy'),(49,'GN0369359','CH1486126118','20190903',6,903,'Buy'),(50,'GN0429760','GB9015779955','20190913',6,430,'Buy'),(51,'GN0161162','DE6592028664','20190914',7,777,'Sell'),(52,'GN0429760','DE9805831979','20190915',5,372,'Buy'),(53,'GN0969461','DE6592028664','20190918',1,598,'Buy'),(54,'GN0429760','CH1486126118','20191018',2,894,'Buy'),(55,'GN0161162','CH1486126118','20191021',4,235,'Buy'),(56,'GN0969461','CH1486126118','20191031',1,427,'Buy'),(57,'GN0161162','DE9805831979','20191119',6,412,'Buy'),(58,'GN0969461','DE6592028664','20191205',2,769,'Sell'),(59,'GN0369359','DE9805831979','20191214',8,418,'Sell'),(60,'GN0429760','DE6592028664','20191219',6,420,'Buy'),(61,'GN0429760','DE7478496562','20191223',6,285,'Buy'),(62,'GN0429760','CH1486126118','20200101',5,106,'Buy'),(63,'GN0161162','GB9015779955','20200108',7,528,'Buy'),(64,'GN0429760','CH1486126118','20200116',7,531,'Buy'),(65,'GN0969461','DE6592028664','20200126',1,632,'Sell'),(66,'GN0429760','GB9015779955','20200129',6,21,'Buy'),(67,'GN0369359','GB9015779955','20200131',5,130,'Sell'),(68,'GN0161162','DE7478496562','20200212',10,222,'Sell'),(69,'GN0161162','CH1486126118','20200216',2,686,'Sell'),(70,'GN0969461','CH1486126118','20200219',10,546,'Buy'),(71,'GN0161162','CH1486126118','20200225',2,601,'Sell'),(72,'GN0369359','GB9015779955','20200305',4,722,'Buy'),(73,'GN0429760','CH1486126118','20200311',10,543,'Buy'),(74,'GN0369359','DE6592028664','20200314',4,145,'Buy'),(75,'GN0429760','DE6592028664','20200323',8,919,'Buy'),(76,'GN0429760','DE9805831979','20200401',1,4,'Buy'),(77,'GN0161162','CH1486126118','20200401',9,320,'Buy'),(78,'GN0429760','DE7478496562','20200406',2,46,'Buy'),(79,'GN0429760','CH1486126118','20200421',5,453,'Buy'),(80,'GN0969461','CH1486126118','20200425',8,150,'Buy'),(81,'GN0429760','DE7478496562','20200501',10,426,'Buy'),(82,'GN0369359','GB9015779955','20200511',4,514,'Buy'),(83,'GN0161162','DE6592028664','20200511',8,467,'Buy'),(84,'GN0969461','DE9805831979','20200518',1,517,'Buy'),(85,'GN0369359','DE6592028664','20200520',6,744,'Buy'),(86,'GN0161162','DE7478496562','20200602',7,567,'Sell'),(87,'GN0969461','CH1486126118','20200604',7,973,'Buy'),(88,'GN0369359','DE6592028664','20200605',2,39,'Sell'),(89,'GN0369359','GB9015779955','20200627',1,770,'Buy'),(90,'GN0369359','DE9805831979','20200709',3,918,'Buy'),(91,'GN0969461','CH1486126118','20200714',7,527,'Buy'),(92,'GN0161162','DE9805831979','20200725',9,281,'Buy'),(93,'GN0161162','GB9015779955','20200729',8,205,'Buy'),(94,'GN0969461','DE9805831979','20200730',5,682,'Buy'),(95,'GN0369359','GB9015779955','20200813',1,833,'Buy'),(96,'GN0161162','DE9805831979','20200816',9,914,'Sell'),(97,'GN0429760','DE7478496562','20200816',6,247,'Buy'),(98,'GN0969461','DE7478496562','20200828',8,822,'Buy'),(99,'GN0161162','DE6592028664','20200830',2,937,'Buy'),(100,'GN0161162','GB9015779955','20200905',5,858,'Buy'); ;WITH UnitsCTE AS ( -- GET Total Units Left SELECT investor, isin, SUM( CASE buysell WHEN 'Buy' Then quantity When 'Sell' THEN -quantity ELSE quantity END) AS Units FROM #CustomerOrderTable GROUP BY investor, isin ), PurchaseCTE AS ( -- Get only purchases in reverse order SELECT quantity, price, investor, isin, datetime, ROW_NUMBER() OVER (PARTITION BY investor, isin ORDER BY datetime DESC ) AS RN FROM #CustomerOrderTable WHERE buysell <> 'Sale' ), UnitCost AS ( -- Recursive CTE to get number of units left at each price SELECT CASE WHEN quantity > unitsCTE.Units THEN unitsCTE.Units ELSE quantity END As quantity, price, unitsCTE.investor, unitsCTE.isin FROM PurchaseCTE CROSS APPLY unitsCTE WHERE RN = 1 AND unitsCTE.investor = PurchaseCTE.investor AND unitsCTE.isin = PurchaseCTE.isin UNION ALL SELECT CASE WHEN P1.quantity > (unitsCTE.Units - (SELECT SUM(quantity) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN AND P1.investor = P3.investor AND P1.isin = P3.isin)) THEN CASE WHEN (unitsCTE.Units - (SELECT SUM(quantity) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN AND P1.investor = P3.investor AND P1.isin = P3.isin)) < 0 THEN 0 ELSE (unitsCTE.Units - (SELECT SUM(quantity) FROM PurchaseCTE P3 WHERE p3.RN < p1.RN AND P1.investor = P3.investor AND P1.isin = P3.isin)) END ELSE P1.quantity END, P1.price, P1.investor, P1.isin FROM PurchaseCTE P1 INNER JOIN PurchaseCTE P2 ON P1.RN = P2.RN + 1 AND P1.investor = P2.investor AND P1.isin = P2.isin CROSS APPLY unitsCTE WHERE P1.investor = unitsCTE.investor AND P1.isin = unitsCTE.isin ) SELECT investor, isin, SUM(quantity) Units, SUM(price * quantity) / SUM(quantity) AS UnitCost, SUM(quantity * price) AS TotalCost FROM UnitCost WHERE quantity > 0 GROUP BY investor, isin DROP TABLE #CustomerOrderTable