CREATE VIEW MyView
AS
SELECT  a.company_id, 
        a.payee_id, 
	a.amount,
	a.ready_to_pay_flag,
	a.deduction_type,
	CASE 
                WHEN    b.order_id IS NULL
                THEN    a.transaction_date 
                ELSE    b.delivery_date 
        END AS ActiveDate
FROM    drs_pending_deduct as a 
INNER JOIN payee p ON a.payee_id = p.[id]
LEFT OUTER JOIN settlement as b 
ON      a.order_id   = b.order_id 
WHERE   a.company_id = 'TMS2' 
    AND p.status         = 'A' 
    AND p.non_office_emp = 'Y' 
    AND p.company_id     = 'TMS2'
    AND a.ready_to_pay_flag <> 'V' 
    AND a.amount is not null 

SELECT company_id, payee_id,
sum( 
        CASE 
                WHEN    ready_to_pay_flag = 'Y' 
                    AND deduction_type    = 'E' 
                THEN    amount 
        END) earn_amount_rtp, 
        sum( 
        CASE 
                WHEN    ready_to_pay_flag = 'Y' 
                    AND deduction_type    = 'E' 
                    AND datediff(d,(
                        ActiveDate, '2007-03-12 23:59:59') <= 30 
                THEN    amount 
        END) earn_amount_rtp_current,
...
FROM MyView
GROUP BY company_id, payee_id