This query provides the raw data used in the A/R report.
None. This is a selection query and no data is modified in the running of it.
modifications done through direct SQL are permanent and non-reversable.
declare @AsOfDateTime datetime;
set @AsOfDateTime = '1/1/2100'; – Include all dates
select OrderNumber,
SaleDate,
StatusText,
CompanyName,
ARBalance,
CurrentBalance,
case when CurrentBalance 0 then 0 else Balance0to30 end as Balance0to30,
case when CurrentBalance 0 then 0 else Balance31to60 end as Balance31to60,
case when CurrentBalance 0 then 0 else Balance61to90 end as Balance61to90,
case when CurrentBalance 0 then 0 else BalanceOver90 end as BalanceOver90,
TermsName as Terms,
GracePeriod,
ARAge,
StatusID,
AccountID,
TransactionID
from
(
select TransactionID, ARBalance, OrderNumber, StatusID, StatusText, SaleDate,
AccountID,
Account.CompanyName, PaymentTerms.GracePeriod, PaymentTerms.TermsName,
case when StatusID = 3 then cast(GETDATE() - SaleDate as Float) else 0 end as ARAge,
case when cast(GETDATE() - coalesce(SaleDate, 0) - GracePeriod as Float) < 0 then ARBalance else 0 end as CurrentBalance,
case when cast(GETDATE() - SaleDate - 31 as Float) < 0 then ARBalance else 0 end as Balance0to30,
case when cast(GETDATE() - SaleDate - 61 as Float) < 0 then ARBalance else 0 end as Balance31to60,
case when cast(GETDATE() - SaleDate - 91 as Float) < 0 then ARBalance else 0 end as Balance61to90,
case when cast(GETDATE() - SaleDate - 91 as Float) >= 0 then ARBalance else 0 end as BalanceOver90
from
(
select TransactionID, Sum(Amount) ARBalance
from GL
where GLAccountID = 14
and EntryDateTime