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.

<code sql> 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