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