March 17, 2011 at 12:25 am
select a.sEmployeeIDf,a.dtmdocdate,a.curAmount,
cast(a.curamount*100/sum(a.curamount) over (partition by a.semployeeidf) as decimal (10,2)) as Total,b.daccruehours,b.dHourstaken,c.dHourlyRate, b.dAccrueHours - b.dHoursTaken as Change
from tblPRHistEarnDetails a
left join (select * from tblprhistleave where dtmDocDate ='10/22/2010' and sLeaveCodeIDf ='AL')
as b on a.semployeeidf = b.semployeeidf
join tblPREE c on a.sEmployeeIDf = c.sEmployeeID
where a.dtmdocdate = '10/22/2010'
group by a.semployeeidf,a.dtmdocdate,a.curAmount,b.daccruehours,b.dHourstaken,c.dHourlyRate
order by a.sEmployeeIDf
***********************
I have 234 records in the table - tblPRHistEarnDetails, however only 227 records are returned.
There are no errors and/or warnings when running the query. The 7 missing records have values and I can't figure it out why they are not being selected.
Thanks
March 17, 2011 at 12:36 am
Chek if there are NULL records. If there are NULL records compaire it with having some default value to nullable records. for example ISNULL(Id, 0)
March 17, 2011 at 12:46 am
The two most obvious reason are
1) Failed inner join to tblPREE , there should be a foreign key relationship to prevent this.
2) The group by clause is doing its job, try adding a count(*) column , then sum that. you *should* find that that will equal your total rows.
March 17, 2011 at 10:37 am
Thanks for your feedback.
I want all the records from tblPRHISTEARNDetails reported, including Null values.
The strange thing is it is reporting the null values. So I tried to determine what is unique about the records that are not being selected, which so far I have not found anything.
I tried a few other things to troubleshoot it
1/ I removed the tblPRHISTLEAVE and I get the correct records.
2/ I removed the tblPREEE and I "do not" get the correct records.
3/ I modified the original query and added 2 more columns from tblPRHISTEARNDetails and I get all records. These 2 columns are not key fields - so I am not sure why it is returning the correct results.
The 2 additional columns are nOrderID and dPRHISTID
Although I am glad that my query is working but I would like to know why the 2 columns made the difference - see the revised query below.
select a.dPRHistID ,a.sEmployeeIDf,a.norderid,a.dtmdocdate,a.curAmount,
cast(a.curamount*100/sum(a.curamount) over (partition by a.semployeeidf) as decimal (10,2)) as Total,
b.daccruehours,b.dHourstaken,c.dHourlyRate, b.dAccrueHours - b.dHoursTaken as Change
from tblPRHistEarnDetails a
left join (select * from tblprhistleave where dtmDocDate ='10/22/2010' and sLeaveCodeIDf ='AL')
as b on a.semployeeidf = b.semployeeidf
join tblPREE c on a.sEmployeeIDf = c.sEmployeeID
where a.dtmDocDate ='10/22/2010'
group by a.semployeeidf,a.dtmdocdate,a.curAmount,b.daccruehours,b.dHourstaken,c.dHourlyRate,a.nOrderID,
a.dPRHistID
order by a.sEmployeeIDf ,a.dprhistid
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply