SQL Query to get Min/Max Datewise data

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query to get Min/Max Datewise data

Author

Topic

praveen140
Starting Member

2 Posts

Posted - 2009-04-29 : 05:24:01

I have a table which contains two fields:

RawPunch(Code,PunchTime)

Code PunchTime
213 2009-04-28 18:27:00.000
213 2009-04-28 09:38:00.000
213 2009-04-25 18:19:00.000
213 2009-04-25 16:55:00.000
213 2009-04-25 16:33:00.000
213 2009-04-25 09:38:00.000
213 2009-04-25 09:27:00.000
213 2009-04-24 18:26:00.000
523 2009-04-23 19:11:00.000

I want to get min date and max date for each Code for each day. Expected result should be like this:

Code PunchTime
213 2009-04-28 09:38:00.000
213 2009-04-28 18:27:00.000
213 2009-04-25 09:27:00.000
213 2009-04-25 18:19:00.000
523 2009-04-23 19:11:00.000

How should I write SQL query to get above result from the table?

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 05:46:53

select code,min(PunchTime) as PunchTime from table
group by code,dateadd(day,datediff(day,0,PunchTime),0)
union all
select code,max(PunchTime) as PunchTime from table
group by code,dateadd(day,datediff(day,0,PunchTime),0)
order by code,PunchTime

Madhivanan

Failing to plan is Planning to fail