question

Ysa avatar image
Ysa asked

CTE Duplicate record when join

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

cteduplicate values
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you provide some sample data to reproduce this problem?

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.