Here in this Blog I have shown how to calculate Running totals and compared two ways of caculating it.
I have tried with INNER JOIN and CTE. Both these methods brings the correct result,
But I could clearly see that CTE took only 27% of the total execution time compared to INNER JOIN, INNER JOIN took remaining 73% to bring the same result.
I have provided the Example below....
Here We Go...!!!!
sample table for testing.
CREATE TABLE PRO (ID INT IDENTITY(1,1),PRO_NAME VARCHAR(100),PRO_QTY INT)
INSERT PRO VALUES ('A',10)
INSERT PRO VALUES ('B',20)
INSERT PRO VALUES ('C',30)
INSERT PRO VALUES ('D',20)
INSERT PRO VALUES ('E',10)
INSERT PRO VALUES ('F',50)
INSERT PRO VALUES ('G',5)
using INNER JOIN
SELECT A.ID,A.PRO_NAME,A.PRO_QTY,SUM(B.PRO_QTY) as RUNNING_TOTAL
FROM PRO A
INNER JOIN PRO B
ON (B.ID <= A.ID)
GROUP BY A.ID,A.PRO_NAME,A.PRO_QTY
SELECT ID,PRO_NAME,PRO_QTY,PRO_QTY AS RUNNING_TOTAL FROM PRO
WHERE ID =1
SELECT P.ID,P.PRO_NAME,P.PRO_QTY,P.PRO_QTY+C.RUNNING_TOTAL FROM PRO P
JOIN CTE C ON C.ID+1 = P.ID
SELECT * FROM CTE
DROP TABLE PRO
Results from both methods.
Execution Plan of both Methods . click on the Image to view