SQL - _etblTerms - Unpivot _as_ARAP_Terms_Unpivot

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

;with Terms as (
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)

SELECT 
	 [OrderUniqueReference] = CONCAT(ap.Reference
                    ,CASE WHEN COALESCE(ap.Order_No,'') != '' THEN ', ' ELSE '' END
                    ,ap.Order_No)

    ,[SupplierUniqueReference] = V.Account
    ,[Supplier] = CONCAT(V.Account,' - ',V.Name)
    --,[DueDate] = FORMAT(AP.TxDate,'yyyy-MM-dd')
    ,[DueDate] = FORMAT(CASE WHEN I.DueDate IS NOT NULL and I.DueDate > AP.TxDate AND AP.Outstanding > 0 THEN I.DueDate
					  WHEN AP.Outstanding < 0 THEN AP.TxDate
					  WHEN AP.Outstanding > 0 and I.DueDate IS NULL THEN DATEADD(DAY,T.IntervalValue,AP.TxDate)
					  ELSE AP.TxDate END,'yyyy-MM-dd')
    ,[Currency] = COALESCE(C.CurrencyCode,'ZAR')
    ,[Amount] = CASE WHEN c.CurrencyCode IS NULL 
		THEN COALESCE(AP.CRedit,0) - COALESCE(AP.Debit,0)
		ELSE COALESCE(AP.fForeignCredit,0) - COALESCE(AP.fForeignDebit,0)
		END
    ,[TermsDescription] = 'TERMS'
    ,[Comment] = CONCAT(COALESCE(B.cBranchCode,'Global')
                    ,CASE WHEN COALESCE(AP.ExtOrderNum,'') != '' THEN ', ' ELSE '' END
                    ,AP.ExtOrderNum)
	
	
	/*Additional fields:*/
	,AP.AutoIdx
	,AP.Id as EvoId
	,T1Type = CASE WHEN AP.Outstanding < 0 THEN 'Prepayment'
					when ap.Outstanding > 0 then 'Unsettled invoice'
					else 'Settled' end
     from PostAP AP
     LEFT JOIN Vendor V on V.DCLink = AP.AccountLink
     LEFT JOIN Currency C on AP.iCurrencyID = C.CurrencyLink
     LEFT JOIN _etblBranch B ON ap.iTxBranchID = B.idBranch
	 LEFT JOIN Terms T ON T.iTermID = V.iAgeingTermID AND V.AccountTerms = T.IntervalNumber
	 LEFT JOIN InvNum I on AP.InvNumKey = I.AutoIndex
     WHERE
		ABS(AP.Outstanding) > 0.01

go