Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Add Running Total To Query
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Add Running Total To Query
Miscellaneous
Thread ID:
01329967
Message ID:
01329967
Views:
66
I am trying to add a running total to a query.

Here's the query:
SELECT	ab.sAmark_Bank_Desc,
		pm.sPayment_Method_Desc,
		trn.dtInsert_DT,
		trn.iTrading_Partner_ID,
		tp.sTP_Name1,
		trn.iOrder_Hdr_ID,
		t.sTrade_Type_CD,
		t.sTran_Group_CD,
		t.sPayment_Term_CD,
		trn.decTrans_Amt,

		CASE sTrans_Type_CD
			WHEN 'CR' THEN 'Cash Receipt'
			ELSE 'Cash Disbursement'
		END AS GroupDesc,

		CASE sTran_Group_CD
			WHEN 'MAR' THEN trn.decTrans_Amt
			ELSE 0
		END AS iMar,

		CASE sTran_Group_CD
			WHEN 'MAR' THEN 0
			ELSE trn.decTrans_Amt
		END AS iNotMar,

		CASE
			WHEN (t.decTrade_Product_Balance <> 0 AND 
				 ((t.sTrade_Type_Pay_Rec = 'PAY' OR t.sTrade_Type_Pay_Rec = 'REC') AND t.sTran_Group_CD <> 'MAR'))
				THEN trn.decTrans_Amt
			ELSE 0
		END AS iProdBal,

		CASE
			WHEN (t.decTrade_Product_Balance = 0 AND 
				 ((t.sTrade_Type_Pay_Rec = 'PAY' OR t.sTrade_Type_Pay_Rec = 'REC') AND t.sTran_Group_CD <> 'MAR'))
				THEN trn.decTrans_Amt
			ELSE 0
		END AS iNotProdBal,

		CASE
			WHEN (t.sTrade_Type_Pay_Rec = 'LM' OR t.sTrade_Type_Pay_Rec = 'SM') AND t.sTran_Group_CD <> 'MAR'
				THEN trn.decTrans_Amt
			ELSE 0
		END AS iMemo

	FROM Transactions trn
	JOIN Trade t ON t.iTrade_ID = trn.iTrade_ID
	JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = trn.iTrading_Partner_ID
	JOIN Payment_Method pm ON pm.sPayment_Method_CD = trn.sPayment_Method_CD
	LEFT JOIN Amark_Bank ab ON ab.sAmark_Bank_CD = trn.sAmark_Bank_CD
	WHERE trn.sTrans_Type_CD = @sReportType
Here's the running total part. The problem is that when I add execute this running total part, it take a long time to run. And, I'm not sure how to incorporate this into the main query:
SELECT  a.iTrade_ID, 
		SUM(b.decTrans_Amt) AS RunningTotal
	FROM Transactions a 
	CROSS JOIN Transactions b
	WHERE (b.iTrade_ID <= a.iTrade_ID) 
	GROUP BY a.iTrade_ID,a.decTrans_Amt
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people
Next
Reply
Map
View

Click here to load this message in the networking platform