Frustrated in Ohio - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Frustrated in Ohio

Author

Topic

despec
Starting Member

5 Posts

Posted - 2008-05-14 : 14:30:49

Folks, I've been trying to extract the minimum and maximum date from the following query:

select top 27 calendar_day.date_value from calendar_day where calendar_day.date_value < current_date() and calendar_day.insession = 1 order by calendar_day.date_value desc

When I then turn this into a derived table and try to extract the minimum/maximum dates, I get the actual minimum/maximum dates of the whole table rather than the result set from the derived table, so obviously it's an aggregate problem. I'm at a loss to figure out how to pull these two dates out and would appreciate any help from more seasoned SQL folks than me.

Thanks,

David

P.S. I'm using SYBASE SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 14:32:35

Could you post a data example of your data and what you want the query to display?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 14:38:50

quote:
Originally posted by despec

Folks, I've been trying to extract the minimum and maximum date from the following query:

select top 27 calendar_day.date_value from calendar_day where calendar_day.date_value < current_date() and calendar_day.insession = 1 order by calendar_day.date_value desc

When I then turn this into a derived table and try to extract the minimum/maximum dates, I get the actual minimum/maximum dates of the whole table rather than the result set from the derived table, so obviously it's an aggregate problem. I'm at a loss to figure out how to pull these two dates out and would appreciate any help from more seasoned SQL folks than me.

Thanks,

David

P.S. I'm using SYBASE SQL

This is MS SQL Server forum. Please post in some sybase forums if you need more help on this.

However you may try like this:-

select min(t.date_value),max(t.date_value)
from
(
select top 27 calendar_day.date_value from calendar_day where calendar_day.date_value < current_date() and calendar_day.insession = 1 order by calendar_day.date_value desc)t

despec
Starting Member

5 Posts

Posted - 2008-05-15 : 08:40:36

Yes, I realize that this is a MS SQL 2005 forum, but I'm trying to just learn the logic involved. As for the query you suggested, I've tried that already, but it returns the minimum/maximum date of the entire table, rather than the minimum/maximum of the query in the derived table. The derived query looks like this when run:

05/13/2008
05/12/2008
....and to
04/7/2008

So I want to extract the minimum date of this set (04/7/2008) and the maximum of the set (05/13/2008).

David

despec
Starting Member

5 Posts

Posted - 2008-05-16 : 16:22:05

Has no one ever come across such a problem who may be able to lend me some advice? I would certainly appreciate it.

David

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 16:23:03

See my first post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

despec
Starting Member

5 Posts

Posted - 2008-05-19 : 11:19:29

This query:

select top 27 calendar_day.date_value from calendar_day where calendar_day.date_value < current_date() and calendar_day.insession = 1 order by calendar_day.date_value desc

Extracts the following if run today

05/16/2008
05/15/2008
05/14/2008
05/13/2008
..and so on until
04/10/2008 (let's say)

Extracting the first records using "select top 1" of the derived table works without a hitch, but when I attempt to extract the minimum date of the returned set by using either a MIN on the set or selecting the top 1 against the derived table sorted "ASC", the query returns the actual minimum calendar_day date value in the entire table, rather then the derived table result set. I'm just at a loss as to how to extract that minimum date.

David

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-05-19 : 11:58:23

Are you sure you have more than 27 rows in your original table?

despec
Starting Member

5 Posts

Posted - 2008-05-19 : 12:16:21

There are 205 rows in the original calendar_day table...These represent dates from the start of school to the end of school.

David

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-19 : 13:08:05

Works fine in MS SQL.. Not sure why it wouldn't work in Sybase...

-- Setup
DECLARE @Date TABLE (Date DATETIME)
DECLARE @TempDate DATETIME

SET @TempDate = DATEADD(DAY, DATEDIFF(DAY, 0, (CURRENT_TIMESTAMP - 250)), 0)

-- Load Sample Data
WHILE @TempDate < CURRENT_TIMESTAMP
BEGIN

	INSERT @Date SELECT @TempDate

	SET @TempDate = @TempDate + 1
END

-- Run Min/Max
SELECT MIN(Date), MAX(Date)
FROM @Date

SELECT MIN(Date), MAX(Date)
FROM 
(
	SELECT TOP 27 Date
	FROM @Date
	ORDER BY Date DESC
) AS T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources