Hello
I have the listed CTE for earning and deduction and I the result in column so I can build the report in report builder. However I am getting duplicate with either join
; With cte_earnings (Fund ,location ,pay , name ,amount )
As ( Select distinct SUBSTRING (e.eh_org ,1 , 3 ) as Fund ,e.eh_location as location , e.a_pay_type as pay , e.a_pay_short_name as name , sum(eh_total_amount) as amount from pr_earn_history e where e.eh_location ='470' and e.eh_check_date ='02/26/2019' Group by e.eh_location, e.a_pay_type, e.a_pay_short_name,SUBSTRING (e.eh_org ,1 , 3 )
)
, cte_deduction (Fund,Location,Deduction, Short, Amount)
As ( Select distinct d.dh_wthold_org as Fund ,d.a_location as Location , d.a_deduction_code as Deduction , d.a_ded_short_name as Short , sum(dh_emp_amount) as Amount from pr_ded_history d where d.a_location ='470' and dh_check_date ='02/26/2019'--and a_deduction_code not in (1000,1100,3000) Group by a_location,a_deduction_code, a_ded_short_name,d.dh_wthold_org
)
Select distinct e.fund, e.location, e.pay, e.name, e.amount, d.Fund,d.Location,d.Deduction, d.Short, d.Amount from "`cte_e`arnings e" right JOIN "cte_deduction d" on d.location =e.location and d.Fund=e.Fund group by e.fund, e.location, e.pay, e.name, e.amount, d.Fund,d.Location,d.Deduction, d.Short, d.Amount
