August 7, 2009 at 12:42 am
I have table named employee and contains columns effective date and incomming time.
For a particular employee i can have same effective date and different incoming time
if effective date is same then i have to pick max incoming time and if the effective date
is not same then i have to select that record also.
AND effective_date = (select max(effective_date) from NewSwipecard..Individual_CutOffTime CT where CT.emp_id = ICOT.emp_id)
AND incoming_time = (select max(incoming_time) from NewSwipecard..Individual_CutOffTime CT where CT.emp_id = ICOT.emp_id)
effective_date IncomingTimeUSER_ID
2008-10-16 00:00:00.00010:00:00 10732
2008-10-16 00:00:00.00011:00:00 10732
2008-10-18 00:00:00.00009:30:00 10780
2008-11-17 00:00:00.00009:30:00 10851
August 7, 2009 at 6:49 am
How about trying a GROUP BY on the incoming date and a MAX on the effective date?
SELECT e.IncomingDate
,MAX(e.EffectiveDate)
FROM dbo.Employees e
GROUP BY e.IncomingDate
Wouldn't that do it?
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply