ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
...DONE!
That's not opening and closing balance - it's simply the amounts of the first and last transactions in the date range.
SELECTa.AccNo,
t.Total_Adjustment,
t.Total_Topup,
t.Total_Comm,
t.Total_Transfer,
t.Total_RecvTransfer,
t.Total_Maxis,
t.Total_Digi,
t.Total_Celcom,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a
LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = t.Topup_ID
WHERE ti.Transaction_Date >= convert(Date,'2012-09-1', 105)
AND ti.Transaction_Date < convert(Date,'2012-10-01', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Open_Balance = SUM(ti.Amount)
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date < convert(Date,'2012-09-1', 105)
) Option3
ORDER BY a.AccNo
This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??
SELECT
a.AccNo,
isnull(t.Total_Adjustment,0.00),
t.Total_Topup,
t.Total_Comm,
t.Total_Transfer,
t.Total_RecvTransfer,
t.Total_Maxis,
t.Total_Digi,
t.Total_Celcom,
Option3.Open_Balance,
Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance
FROM Tbl_Account a WHERE A.AccNo <> 1 <---- add at here??? ERROR ...
LEFT JOIN (
SELECT
d.Transaction_AccNo,
SUM(d.Amount) AS TransactionsForPeriod,
SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,
SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,
SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,
SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,
SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,
SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_Celcom
FROM (
SELECT
ti.Transaction_AccNo,
ti.TransType_ID,
p.Topup_Company,
Amount = SUM(ti.Amount)
FROM Tbl_Transaction ti
LEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_ID
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company
) d
GROUP BY d.Transaction_AccNo
) t
ON t.Transaction_AccNo = a.AccNo
OUTER APPLY ( -- SUM of all the amounts prior to date range
SELECT
Top 1 Before_Amount as Open_Balance
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
ORDER BY Transaction_Date
) Option3
ORDER BY a.AccNo