SQL - AR Overdue Transactions

 GO
 
 CREATE OR ALTER VIEW _as_AROverdueTransactions
 AS
 SELECT C.AccountTerms,T.IntervalValue,I.DueDate as InvNumDueDate
	,Calculated_IsOverdue = CASE 
			WHEN Outstanding <= 0 THEN 0
			WHEN (COALESCE(I.DueDate,'1900-01-01') > P.TxDate AND I.DueDate < GETDATE()) THEN 1 /*DUE Date more than inv date and less than today.*/
			WHEN (COALESCE(I.DueDate,'1900-01-01') <= P.TxDate AND DATEADD(day,T.IntervalValue,P.TxDate) < GETDATE()) THEN 1
			ELSE 0 END 
	,CalculatedDueDate = CASE WHEN (COALESCE(I.DueDate,'1900-01-01') > P.TxDate) THEN COALESCE(I.DueDate,'1900-01-01')
			WHEN (COALESCE(I.DueDate,'1900-01-01') <= P.TxDate) THEN DATEADD(day,T.IntervalValue,P.TxDate)
			ELSE P.TxDate END 
	,P.* 
	from PostAR P 
	LEFT JOIN Client C on P.AccountLink = C.DCLink
	--LEFT JOIN _etblTerms T ON C.iAgeingTermID = T.iTermID
	LEFT JOIN _as_ARAP_Terms_Unpivot T ON T.iTermID = C.iAgeingTermID AND C.AccountTerms = T.IntervalNumber 

	LEFT JOIN InvNum I on I.AutoIndex = P.InvNumKey
 WHERE P.Id IN ('OInv','Inv') 
	AND Outstanding > 0 /*handles debit only as well (for AR)*/

CREATE VIEW _as_AROverdueClients
	AS
	SELECT C.DCLink, SUM(t.Outstanding) as OverdueAmount, IsOverdue = CASE WHEN SUM(t.Outstanding) > 1 THEN 1 ELSE 0 END FROM _as_AROverdueTransactions t
		LEFT JOIN Client C ON t.AccountLink = C.DCLink
		GROUP BY C.DCLink

CREATE OR ALTER VIEW _as_ARAP_Terms_Unpivot
 AS
/*TO VERIFY: is 0 based or 1 based.
2nd note: might need to handle Interval 7 if null.*/
/*Usage:
SELECT * FROM Client C
	LEFT JOIN _as_ARAP_Terms_Unpivot T ON T.iTermID = C.iAgeingTermID AND C.AccountTerms = T.IntervalNumber 
*/
SELECT iTermID, iModule, IntervalDay, IntervalValue,CASE IntervalDay
        WHEN 'iInterval1Days' THEN 1-1
        WHEN 'iInterval2Days' THEN 2-1
        WHEN 'iInterval3Days' THEN 3-1
        WHEN 'iInterval4Days' THEN 4-1
        WHEN 'iInterval5Days' THEN 5-1
        WHEN 'iInterval6Days' THEN 6-1
        WHEN 'iInterval7Days' THEN 7-1
    END AS IntervalNumber
    FROM
    (
        SELECT iTermID, iModule, iInterval1Days, iInterval2Days, iInterval3Days, iInterval4Days, iInterval5Days, iInterval6Days, COALESCE(iInterval7Days,iInterval6Days+1) iInterval7Days
        FROM dbo._etblTerms
    ) AS t
    UNPIVOT
    (
        IntervalValue FOR IntervalDay IN (iInterval1Days, iInterval2Days, iInterval3Days, iInterval4Days, iInterval5Days, iInterval6Days, iInterval7Days)
    ) AS unpivoted