Not quite the Max(date) but..........

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

Not quite the Max(date) but..........

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-11-13 : 02:42:35

I need to pull out of my table the highest date found that is 2 weeks below the Max(f_date) held,

I can get:-

Select max(f_date) from table1

but I need the next highest f_date that is at least 2 weeks below the Max(f_date)

any ideas please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 02:52:27

[code]Select max(f_date) from table1 where f_date< (SELECT DATEADD(wk,-2,Date) FROM (SELECT Max(f_date) AS Date FROM table1)t)[/code]

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-11-13 : 02:53:03

This was my first attempt:-


SELECT     TOP (100) PERCENT 'RequestTable' AS Tablename, recno AS MinRecno, f_date AS MinF_Date
FROM         dbo.table1
GROUP BY f_date recno
HAVING      (MAX(f_date) = DATEADD(week, - 2, MAX(f_date)))
ORDER BY MinDate DESC

which gives me no results?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-11-13 : 02:54:13

oops sorry, cross post.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 04:19:44

or

Select max(f_date) from table1
where f_date<(SELECT DATEADD(wk,-2,Max(f_date)) FROM table1)

Madhivanan

Failing to plan is Planning to fail

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-11-13 : 05:00:01

Thanks both.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-13 : 05:01:57

select
max(t.date)
from
(
select
f_date as date
from
table1
where
convert(varchar(11),f_date,101) < convert(varchar(11),dateadd(ww,-2,(select max(convert(varchar(11),f_date,101)) from table1)),101)
) t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 05:37:45

quote:
Originally posted by raky

select
max(t.date)
from
(
select
f_date as date
from
table1
where
convert(varchar(11),f_date,101) < convert(varchar(11),dateadd(ww,-2,(select max(convert(varchar(11),f_date,101)) from table1)),101)
) t

Why do you think it is better to convert dates to varchars?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources