Selecting first and last row of a date

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

Selecting first and last row of a date

Author

Topic

Moonyeen
Starting Member

1 Post

Posted - 2010-05-11 : 03:39:40

I have the following table from an access control system:

2010/03/01 10:00
2010/03/01 11:00
2010/03/01 12:30
2010/03/01 13:00
2010/03/01 14:00
2010/03/01 16:00

I need a report or data export with only the first and last entry per day. How can I do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-11 : 03:45:17

select min(date), ma(date) from your_table
group by dateadd(day,datediff(day,0,date),0)

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-11 : 08:05:19

I think OP wants this -


;with CTE AS
(
	SELECT date, 
	ROW_NUMBER() OVER ( Partition By CONVERT(VARCHAR(20), date, 103) ORDER BY date ) AS Ascen ,
	ROW_NUMBER() OVER ( Partition By CONVERT(VARCHAR(20), date, 103) ORDER BY date Desc ) AS Descen
	FROM Your_table
) 
SELECT date FROM CTE
WHERE Ascen = 1 or Descen = 1

rest all the columns can be taken in select list...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-11 : 08:52:07

quote:
Originally posted by vaibhavktiwari83

I think OP wants this -

;with CTE AS
(
SELECT date,
ROW_NUMBER() OVER ( Partition By CONVERT(VARCHAR(20), date, 103) ORDER BY date ) AS Ascen ,
ROW_NUMBER() OVER ( Partition By CONVERT(VARCHAR(20), date, 103) ORDER BY date Desc ) AS Descen
FROM Your_table
)
SELECT date FROM CTE
WHERE Ascen = 1 or Descen = 1

rest all the columns can be taken in select list...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

It is same as my solution except that both min and max values are in the same row in your method

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-11 : 09:24:07

quote:

It is same as my solution except that both min and max values are in the same row in your method

Madhivanan

Failing to plan is Planning to fail

Yes it is but the thing is vice versa of what you said
Because
in your solution min date
and max date are in same row instead in my solution its in different rows.
and one more thing if OP wants to select all the corresponding columns of those dates
then in your solution its not possible right??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-11 : 10:58:28

<<
if OP wants to select all the corresponding columns of those dates
then in your solution its not possible right??
>>

Yes. But wee need to know what OP wants

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources