This article demonstrates an example of a Custom Account Balance Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.
This article demonstrates an example of a Custom Account Balance Report.Disclaimer:
This report may need modifications or alterations base on each system setup and configuration.
Report Query:
<?xml version="1.0" encoding="utf-8"?>
<cViews xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<iViewID>677</iViewID>
<iListType>25</iListType>
<dtDateUpdated>2016-06-20T10:04:24</dtDateUpdated>
<iOperatorID>0</iOperatorID>
<sName>ACC BALANCE REPORT</sName>
<sViewXML><?xml version="1.0" encoding="utf-16"?><cCustomReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><sReportName>Accounts Balance Report</sReportName><sSQL>
Select A.AccountID,
Sum(b.Payments) as Payments, Sum(b.charges) as charges
into #TempBals
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where B.DateTimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo}
and (b.charges &lt;&gt;0 or b.payments &lt;&gt; 0)
and b.VenueID like '{Venueid}'
Group by A.Accountid, A.AccNumber
Select * into #tempbe
From(
Select A.AccountID, A.AccNumber, b.balanceend, Row_Number() over (Partition by A.Accountid order by B.datetimeactivity desc) as rn
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where b.DatetimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo}
and b.VenueID like '{Venueid}' ) as sel where rn = 1
Select * into #temppay
From(
Select A.AccountID, A.AccNumber, B.Datetimeactivity, Row_Number() over (Partition by A.Accountid order by B.datetimeactivity desc) as rn
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where b.DatetimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo} and payments &lt;&gt; 0
and b.VenueID like '{Venueid}') as sel where rn = 1
Select * into #tempch
From(
Select A.AccountID, A.AccNumber, B.Datetimeactivity, Row_Number() over (Partition by A.Accountid order by B.datetimeactivity desc) as rn
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where b.DatetimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo} and charges &lt;&gt; 0
and b.VenueID like '{Venueid}') as sel1 where rn = 1
Select a.parentid, A.FirstName, A.LastName, A.Accountid, A.AccNumber,
A.NettTurnover, b.datetimeactivity, d.payments, c.datetimeactivity, d.charges, e.balanceend
From Account A
left outer Join #temppay b on b.accountid = a.accountid
left outer join #tempch c on c.accountid = a.accountid
<cViews xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<iViewID>677</iViewID>
<iListType>25</iListType>
<dtDateUpdated>2016-06-20T10:04:24</dtDateUpdated>
<iOperatorID>0</iOperatorID>
<sName>ACC BALANCE REPORT</sName>
<sViewXML><?xml version="1.0" encoding="utf-16"?><cCustomReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><sReportName>Accounts Balance Report</sReportName><sSQL>
Select A.AccountID,
Sum(b.Payments) as Payments, Sum(b.charges) as charges
into #TempBals
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where B.DateTimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo}
and (b.charges &lt;&gt;0 or b.payments &lt;&gt; 0)
and b.VenueID like '{Venueid}'
Group by A.Accountid, A.AccNumber
Select * into #tempbe
From(
Select A.AccountID, A.AccNumber, b.balanceend, Row_Number() over (Partition by A.Accountid order by B.datetimeactivity desc) as rn
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where b.DatetimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo}
and b.VenueID like '{Venueid}' ) as sel where rn = 1
Select * into #temppay
From(
Select A.AccountID, A.AccNumber, B.Datetimeactivity, Row_Number() over (Partition by A.Accountid order by B.datetimeactivity desc) as rn
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where b.DatetimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo} and payments &lt;&gt; 0
and b.VenueID like '{Venueid}') as sel where rn = 1
Select * into #tempch
From(
Select A.AccountID, A.AccNumber, B.Datetimeactivity, Row_Number() over (Partition by A.Accountid order by B.datetimeactivity desc) as rn
From Account A
Join AccActivity B on b.accountid = a.Accountid
Where b.DatetimeActivity &gt;= {DateFrom} and B.DateTimeActivity &lt;= {DateTo} and charges &lt;&gt; 0
and b.VenueID like '{Venueid}') as sel1 where rn = 1
Select a.parentid, A.FirstName, A.LastName, A.Accountid, A.AccNumber,
A.NettTurnover, b.datetimeactivity, d.payments, c.datetimeactivity, d.charges, e.balanceend
From Account A
left outer Join #temppay b on b.accountid = a.accountid
left outer join #tempch c on c.accountid = a.accountid