MS SQL Server
  1. MS SQL Server
  3. TSQL Somme cumulée
Intereting Posts
SQL Server basé sur des files locaux SQL Optimization clause L'ancien joint IN vs. Exists vs Left (où ___ est ou n'est pas nul); Performance Création de tables dans la database SQL SQL Server 2005 sql raiserror spécifiez le paramètre Comment puis-je effacer le cache de requête SQL Server? supprimer des loggings en double spécifiques SQL Server 2008 Ajout du pilote JDBC à PySpark au démarrage sans utiliser le file de configuration SQL sélectionne les lignes dupliquées en fonction de plusieurs colonnes ADO.NET Entity Framework et colonnes d'identité Notification pour la fin de la requête SQL Server La projection Entity Framework 6 génère un SQL équivalent à "Select *" et ne produit pas la clause WHERE SQLException n'a pas été gérée – Windows CE Création et utilisation d'une table temporaire pour renvoyer une ligne spécifique, au lieu d'exécuter la requête à plusieurs resockets SSIS et envoi de requête avec date à Oracle

TSQL Somme cumulée

J'ai besoin de montrer les niveaux d'inventaire pour chaque jour dans une vue. J'ai une table qui énumère l'article courant et la quantité et une autre table qui a toutes les transactions qui sont arrivées à ces articles. J'ai besoin de créer une requête qui répertorie l'élément, la date et le niveau d'inventaire pour cette date pour un rapport de tendance. Voici quelques exemples de données dans les arrays:

Articles: 

ItemNumber QuantityOnHand ---------- -------------- B34233 25.0 B34234 10.0

ItemTransactions: 

 TransDate ItemNumber Quantity ----------- ---------- -------- 1/1/2011 B34233 10.0 1/2/2011 B34234 -15.0 1/2/2011 B34233 -5.0 1/4/2011 B34234 -10.0

Voici le résultat que je veux de la requête: 

 Date ItemNumber Quantity ---- ---------- -------- 12/31/2010 B34233 20.0 12/31/2010 B34234 35.0 1/1/2011 B34233 30.0 1/1/2011 B34234 35.0 1/2/2011 B34233 25.0 1/2/2011 B34234 20.0 1/3/2011 B34233 25.0 1/3/2011 B34234 20.0 1/4/2011 B34233 25.0 1/4/2011 B34234 10.0

Comment est-ce que j'écrirais cette requête? J'ai une assez bonne connaissance de TSQL mais je n'arrive pas à find un moyen d'écrire cette requête.

Dans SQL Server 2005 et versions ultérieures: 

 WITH dates (itemNumber, quantity, currentDate, minDate) AS ( SELECT itemNumber, CAST(quantityOnHand AS DECIMAL(20, 2)), it.* FROM items i CROSS APPLY ( SELECT MAX(transDate) AS currentDate, MIN(transDate) AS minDate FROM itemTransactions it ) it UNION ALL SELECT d.itemNumber, CAST ( d.quantity - COALESCE( ( SELECT it.quantity FROM itemTransactions it WHERE it.transDate = d.currentDate AND it.itemNumber = d.itemNumber ), 0) AS DECIMAL(20, 2)), DATEADD(d, -1, currentDate), minDate FROM dates d WHERE currentDate >= minDate ) SELECT currentDate, itemNumber, quantity FROM dates ORDER BY currentDate, itemNumber

Cela suppose que vous avez une transaction par élément par jour (il s'agit d'une limitation de CTE récursif dans SQL Server ).

Si vous ne le faites pas, vous devez append un autre CTE qui regrouperait les transactions par jour et par post et l'utiliserait à la place des items : 

 WITH itGrouped (transDate, itemNumber, quantity) AS ( SELECT transDate, itemNumber, SUM(quantity) FROM itemTransactions GROUP BY transDate, itemNumber ), dates (itemNumber, quantity, currentDate, minDate) AS ( SELECT itemNumber, CAST(quantityOnHand AS DECIMAL(20, 2)), it.* FROM items i CROSS APPLY ( SELECT MAX(transDate) AS currentDate, MIN(transDate) AS minDate FROM itGrouped it ) it UNION ALL SELECT d.itemNumber, CAST ( d.quantity - COALESCE( ( SELECT it.quantity FROM itGrouped it WHERE it.transDate = d.currentDate AND it.itemNumber = d.itemNumber ), 0) AS DECIMAL(20, 2)), DATEADD(d, -1, currentDate), minDate FROM dates d WHERE currentDate >= minDate ) SELECT currentDate, itemNumber, quantity FROM dates ORDER BY currentDate, itemNumber

Une version simple utilisant la sous-sélection pour get la quantité en main par jour.

Une solution bonne / rapide si vous n'avez pas de transactions 100k et / ou une boîte décent sql.

Excusez le SQL désordonné (encoding midi: P) 

 CREATE TABLE #transactions (ID INT, DTE DATETIME, PROD VARCHAR(25), QTY INT ) CREATE TABLE #products (ID VARCHAR(25)) CREATE TABLE #dates (DTE DATETIME) -- create some dates - you would do this dynamically INSERT INTO #dates values (convert(datetime, '01/01/2011', 103)) INSERT INTO #dates values (convert(datetime, '02/01/2011', 103)) INSERT INTO #dates values (convert(datetime, '03/01/2011', 103)) -- create some products - you would get these from where-ever they live INSERT INTO #products values ('A') INSERT INTO #products values ('B') -- create some transactions - you would get these from where-ever they live INSERT INTO #transactions values (1, convert(datetime, '01/01/2011', 103), 'A', 25) INSERT INTO #transactions values (2, convert(datetime, '01/01/2011', 103), 'A', -5) INSERT INTO #transactions values (3, convert(datetime, '02/01/2011', 103), 'A', 60) INSERT INTO #transactions values (4, convert(datetime, '02/01/2011', 103), 'A', -15) INSERT INTO #transactions values (5, convert(datetime, '03/01/2011', 103), 'A', 100) INSERT INTO #transactions values (6, convert(datetime, '03/01/2011', 103), 'A', -20) INSERT INTO #transactions values (7, convert(datetime, '01/01/2011', 103), 'B', 10) INSERT INTO #transactions values (8, convert(datetime, '01/01/2011', 103), 'B', 5) INSERT INTO #transactions values (9, convert(datetime, '02/01/2011', 103), 'B', -30) INSERT INTO #transactions values (1, convert(datetime, '02/01/2011', 103), 'B', 50) INSERT INTO #transactions values (11, convert(datetime, '03/01/2011', 103), 'B', 10) INSERT INTO #transactions values (12, convert(datetime, '03/01/2011', 103), 'B', 200) -- Join dates and transactions - Do a sub select from 'begining of time' to get qty on hand per day SELECT CONVERT(VARCHAR(25), a.DTE, 103), b.id, (SELECT sum(qty) from #transactions c where b.id = c.prod and c.DTE <= a.DTE) FROM #dates a, #products b -- One benefit to this approach means you can genereate qty_on_hand per days were no transactions have occured (if you needed this) DROP TABLE #transactions DROP TABLE #products DROP TABLE #dates

Voir, la requête est simple, mais quelle est votre exigence avec la date. Je donne une simple requête qui vous donnera l'idée, et j'utilise une table temporaire: 

 Create Table #Transection ( ID int Identity(1,1) not null,Item_Number varchar(20),Transection_Date datetime,Quantity_Available decimal(10,2) ) insert into #Transection (Item_Number) (Select ItemNumber from Items) Declare @Product varchar(20),@Next_Product Cursor, @Item varchar(20),@Quantity decimal(10,2) set @Next_Product= CURSOR FOR Select Item_Number from #Transection open @Next_Product Fetch Next from @Next_Product into @Product While @@Fetch_Status=0 Begin set @Item=(Select ItemNumber from ItemTransection where ItemNumber= @Product) if is not null Begin Set @Quantity=(Select top 1 Items.Quantityonhand -ItemsTrasection.Quant as Quantity from Items Join ItemsTrasection on ItemsTrasection.ItemNumber=Items.ItemNumber where ItemsTrasection.ItemNumber=@Item order by ItemsTrasection.TransDate desc) update #Transection set Transection_Date=(Select top 1 TransDate from ItemsTrasection where ItemNumber=@Item order by TransDate desc), Quantity=@Quantity where Item_Number=@Item End Else Begin update #Transection set Transection_Date=(Select top 1 TransDate from Items where ItemNumber=@Item ), Quantity=(Select top 1 from Items where ItemNumber=@Item ) where Item_Number=@Item End End Select * from #Transection