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 
You could leave a comment if you were logged in.