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 = @sReportTypeHere'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