July 17, 2017 at 6:13 pm
I am stuck as to why the query is not returning all rows. My CTE1 has 92 rows and CTE2 has 90 rows.
My query should return 92 rows - but I am getting 90.
Thanks
with CTE1 (EmpID,Name,SSN,GrossPay,PayRate,Birthdate,Hiredate,StreetAddress,City,Zip) as
(
select a.semployeeidf,b.slastname + ', '+ b.sfirstname +' '+b.smiddleinitial as Name,b.sssn ,a.curamount,
a.drate,b.dtmBirthday , b.dtmHired ,b.sAddress ,b.sCity,b.sZip from tblprhistearn as a
join tblpree as b on a.sEmployeeIDf = b.sEmployeeId
where a.searncodeidf in ('Salary','PT Hrly Pers')
),
CTE2 (EmpID,DedBeneCode,Amount) as
(
select sEmployeeIDf ,sdeductcodeidf,curamount from tblPRHistDeduct
where sdeductcodeidf in ('rt23','rt26','misc','retire')
union all
select semployeeidf, sbenecodeidf, curamount from tblprhistbene
where sbenecodeidf in ('ert26','ert23','rul','e retire1')
)
select cte1.empid,
RT23 = sum( case when dedbenecode = 'rt23' then amount else 0 end)
from CTE1
left join CTE2 on cte1.empid = cte2.empid
group by cte1.Empid
order by cte1.Empid
July 17, 2017 at 7:31 pm
Please ignore - this I have already resolved the issue.
Thanks
July 18, 2017 at 4:31 am
Why not post what the resolution was in case someone searches this up. You'd be helping others.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 18, 2017 at 2:55 pm
Safe bet it was the GROUP BY.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply