SQL - Customer Quick Enquiry

CREATE OR ALTER VIEW _as_EvoOnline_CustomerQuickEnquiry
AS
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 
	p.AccountLink, p.iCurrencyID,
	[Current] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE()) <= 30 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[30days] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE()) BETWEEN 31 AND 60 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[60days] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE())  BETWEEN 61 AND 90 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[90days] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE())  BETWEEN 91 AND 120 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[120days] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE())  BETWEEN 121 AND 150 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[150days] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE())  BETWEEN 151 AND 180 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[180daysPlus] = SUM(CASE WHEN DATEDIFF(DAY,p.TxDate,GETDATE()) > 180 THEN IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[LastInvoiceDate] = CAST(MAX(CASE WHEN p.Id IN ('Inv','oInv') THEN p.TxDate ELSE '01-01-1900' END) as Date)
	,[LastInvoiceAmount] = (SELECT TOP 1 IIF(p2.iCurrencyID =0,p2.Debit,p2.fForeignDebit) as LastInvoiceAmount FROM PostAR p2 WHERE p2.AccountLink = p.AccountLink ORDER BY p2.TxDate DESC)
	,[LastPaymentDate] = CAST(MAX(CASE WHEN p.Credit > 0 AND p.Id != 'Crn' THEN p.TxDate ELSE '01-01-1900' END) as Date)
	,[LastPaymentAmount] = (SELECT TOP 1 IIF(p2.iCurrencyID =0,p2.Credit,p2.fForeignCredit) as LastPaymentAmount FROM PostAR p2 WHERE p2.AccountLink = p.AccountLink AND p2.Credit > 0 AND p2.Id != 'Crn' ORDER BY p2.TxDate DESC)
	,[UnallocatedPayment] = SUM(CASE WHEN p.Outstanding < 0 THEN  IIF(c.bForCurAcc = 0, p.Outstanding, p.fForeignOutstanding) ELSE 0 END)
	,[CreditLimit] = c.Credit_Limit
	,[Terms] = IIF(T.IntervalNumber = 0,'Current',CONCAT(IntervalValue,' days'))
	FROM PostAR p
	LEFT JOIN Client c ON p.AccountLink = c.DCLink
	LEFT JOIN TrCodes TR ON p.TrCodeID = TR.idTrCodes
	LEFT JOIN Terms T ON T.iTermID = C.iAgeingTermID AND C.AccountTerms = T.IntervalNumber
	GROUP BY p.AccountLink, p.iCurrencyID
			,c.Credit_Limit,T.IntervalNumber, T.IntervalValue