====== {$page} ======



===== Explanation of SQL =====



This query provides the raw data used in the A/R report.



===== Risk of Data Corruption if Run Improperly =====

**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**.



===== SQL =====



[[code_formatsql]]



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