need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2011-01-23 : 06:26:41

EmpNo FnType EntryDate EntryTime

2744 2 22/12/2010 04:41
2744 2 22/12/2010 21:19
2744 1 22/12/2010 16:41
2744 1 23/12/2010 13:14
2744 2 23/12/2010 23:23

I have this records in my table

for this particular empno i want to extract those records for fnType 2
where entrytime is minimum for each date

for fnType 1
where entrytime is maximum for each date

here fntype 1 stand for in
fntype 2 stand for out

Ex- for date 22/12/2010 i want record to be displayed

2744 2 22/12/2010 04:41

2744 1 22/12/2010 16:41

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-23 : 07:27:48

SELECT EmpNo,fnType,EntryDate,MAX(EntryTime) as MaxTime
FROM [Table]
Where EmpNo = 2744
GROUP BY EmpNo,fnType,EntryDate
ORDER BY fnType desc

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 10:38:50

it should be


SELECT EmpNo,fnType,EntryDate,EntryTime
FROM
(
SELECT EmpNo,fnType,EntryDate,EntryTime,
ROW_NUMBER() OVER (PARTITION BY EmpNo,fnType,EntryDate ORDER BY CASE WHEN fnType=1 THEN DATEADD(dd,0,EntryTime) ELSE DATEDIFF(dd,EntryTime,2958463)  DESC) AS rn
FROM [Table]
Where EmpNo = 2744
AND fnType IN (1,2)
)t
WHERE rn=1
ORDER BY EmpNo,EntryDate,fnType desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources