How to get the total for different values in a column
I would like to group by date range as below example
Date ItemNo Qty
==================================
1/1/2014 101 20
2/1/2014 102 10
3/1/2014 103 5
4/1/2014 104 10
1/1/2014 101 5
2/1/2014 101 10
3/1/2014 102 15
4/1/2014 104 20
I want to receive the balance daily, summing up the amount until this day, grouped by ItemNo, as below
Date ItemNo Qty
==================================
1/1/2014 101 25
2/1/2014 101 35
2/1/2014 102 10
3/1/2014 102 25
3/1/2014 103 5
4/1/2014 104 30
I know I can solve the problem using cursors, but I need a different solution
thank
source to share
so just use SUM
SELECT Date, ItemNo, SUM(Qty)
FROM table
GROUP BY Date, ItemNo
read agregate function and sum
Edit
I took your comment and did the following:
SELECT a.Date, a.ItemNo, tmp.qty + a.ItemNo
FROM table a
JOIN (SELECT TOP 1 * FROM table t WHERE t.date < a.Date ORDER BY t.date DESC) tmp ON a.ItemNo = tmp.ItemNo
I am testing it now, so it might need some tweaking, but I would like to let it go right away so you get the general idea
source to share
Here is an example table
SELECT * INTO #TEMP
FROM
(
SELECT '1/1/2014' [DATE], 101 [ItemNo], 20 QTY
UNION ALL
SELECT '2/1/2014', 102, 10
UNION ALL
SELECT '3/1/2014', 103, 5
UNION ALL
SELECT '4/1/2014', 104, 10
UNION ALL
SELECT '1/1/2014', 101, 5
UNION ALL
SELECT '2/1/2014', 101, 10
UNION ALL
SELECT '3/1/2014', 102, 15
UNION ALL
SELECT '4/1/2014', 104, 20
)TAB
Use Row_Number
to get the number for each date of an item, type sum
insideCTE
;WITH CTE1 AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [ItemNo] ORDER BY CAST([DATE] AS DATE))RNO,
[DATE],[ItemNo],SUM(Qty)Qty
FROM #TEMP
GROUP BY [DATE],[ItemNo]
)
SELECT A.RNO,[DATE],[ItemNo],
CASE WHEN RNO=1 THEN Qty
ELSE (SELECT SUM(b.Qty)
FROM CTE1 b
WHERE A.ItemNo=B.ItemNo AND B.RNO<=A.RNO)
END QTY
FROM CTE1 A
ORDER BY A.itemno,CAST(A.[DATE] AS DATE);
RESULT
source to share
Here is a solution using a recursive common table expression.
Not sure if this will be faster or not than Sarata Avanavu's answer, but you can try!
Sample data:
DECLARE @t TABLE([Date] DATETIME, ItemNo INT, QTY INT)
INSERT @t
( Date, ItemNo, QTY )
SELECT '1/1/2014', 101, 20
UNION ALL SELECT '2/1/2014', 102, 10
UNION ALL SELECT '3/1/2014', 103, 5
UNION ALL SELECT '4/1/2014', 104, 10
UNION ALL SELECT '1/1/2014', 101, 5
UNION ALL SELECT '2/1/2014', 101, 10
UNION ALL SELECT '3/1/2014', 102, 15
UNION ALL SELECT '4/1/2014', 104, 20
Query:
;WITH dSum AS (
SELECT [Date], ItemNo, SUM(QTY) AS QTY
FROM @t AS t
GROUP BY [Date], [ItemNo]
), dSumRN AS (
SELECT [Date], ItemNo, QTY, ROW_NUMBER() OVER(PARTITION BY ItemNo ORDER BY [Date]) AS rn
FROM dSum
), cte AS (
SELECT [Date], ItemNo, QTY, rn
FROM dSumRN
WHERE rn = 1
UNION ALL SELECT
dSumRN.[Date], dSumRN.ItemNo, cte.QTY + dSumRN.QTY AS QTY, cte.rn + 1 AS rn
FROM cte
JOIN dSumRN ON cte.ItemNo = dSumRN.ItemNo AND cte.rn + 1 = dSumRN.rn
)
SELECT [Date], [ItemNo], QTY FROM cte
ORDER BY [Date], [ItemNo]
OPTION (MAXRECURSION 1000) -- maximum this can be set to is 32767
source to share