Selecting greatest value less than a limit

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)

Selecting greatest value less than a limit

Author

Topic

kevincallan
Starting Member

13 Posts

Posted - 2009-01-06 : 13:36:17

Hello all--

I have an application where I want to select the greatest value of a field that is less than a specified limit. Consider a set of documents that have an effectivity date specified as yymmddhhmm (ie. 0901061230 = 2009, Feb 6, 12:30pm). I want to select the record that has a specified document number where the date code is less than now but the greatest in the set. Can SQL do this?

Sample Data:
Document:Effectivity:Name
24:0801011200:How to...
24:0901060600:How to... (this one should be selected)
24:0902041600:How to...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 13:47:37

SELECT t.Document, t.Effectivity, t.Name
FROM YourTable t
INNER JOIN (
SELECT MAX(Effectivity) AS Effectivity
FROM Yourtable
WHERE Document = 24 AND Effectivity < '02/06/2009 12:30pm') dt
ON t.Document = dt.Document AND t.Effectivity = dt.Effectivity

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

Subscribe to my blog

kevincallan
Starting Member

13 Posts

Posted - 2009-01-06 : 16:27:22

Tara--

Thanks for your reply. I am having trouble running it. SQL S2K5 parses the query correctly but fails to run with error "invalid column name, Document". If I change to:

INNER JOIN Document, MAX...

then the command fails stating "Document is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Any ideas what I'm doing wrong?

Thanks,
Kevin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-06 : 16:43:22

you can use

max(Document) as Document, max...

because you know there is only Document 24 in this case.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

kevincallan
Starting Member

13 Posts

Posted - 2009-01-06 : 17:47:01

Thanks, Webfred.

I'm feeling a bit like a bonehead for not describing my problem sufficiently. I'm operating near the margin of my capabilities.

Since there are multiple document numbers (Document = [1..n]) and there are multiple effectivity dates for any one document, I am trying to generate a recordset that includes the latest effective version of each document.

With the preset query which works with the MAX function applied to the document column, I only get one record which is to be expected since the document is checked to be 24. In fact, I need the latest effective document for every document number [1..n]. Does that make sense?

If the date is 0901061700 (2009-Jan-06, 5:00pm), the query on this table:

Doc: Eff Date : Name
1 :0801010000:doc1
1 :0807231300:doc1
1 :0910051200:doc1
2 :0801010000:doc2
2 :0903041200:doc2
2 :0912201600:doc2
2 :1003241000:doc2
3 :0801010000:doc3
3 :0901031000:doc3
3 :0904051230:doc3
...

should yield this recordset:

Doc: Eff Date : Name
1 :0807231300:doc1
2 :0801010000:doc2
3 :0901031000:doc3
...

Thanks,
K

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 23:14:55

declare @table table(Doc int, EffDate datetime, Name varchar(55))
insert into @table
select doc,effdate,name from urtable where dateadd(d,datediff(d,0,effdate),0) < dateadd(d,datediff(d,0,'090106'),0)

select doc,max(effdate),name from @table group by doc,name

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-07 : 01:59:41

Hi Try this

select doc,max(effdate),name from urtable where effdate<'090106' group by doc,name

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 04:17:20

[code]declare @Table table
(
Doc int,
[Eff Date] int,
[Name] varchar(10)
)
insert into @Table
select 1 ,0801010000,'doc1' union all
select 1 ,0807231300,'doc1' union all
select 1 ,0910051200,'doc1' union all
select 2 ,0801010000,'doc2' union all
select 2 ,0903041200,'doc2' union all
select 2 ,0912201600,'doc2' union all
select 2 ,1003241000,'doc2' union all
select 3 ,0801010000,'doc3' union all
select 3 ,0901031000,'doc3' union all
select 3 ,0904051230,'doc3'
declare @date datetime
set @date='2009-01-06 17:00:00'
select @date
select *
from
(
select *,row_number() over (partition by Doc ORDER BY [Eff Date] DESC) as seq
from @table
where [Eff Date]<=CAST(REPLACE(REPLACE(REPLACE(SUBSTRING(CONVERT(varchar(17),@date,120),3,17),'-',''),':',''),' ','') as bigint)
)t
where seq=1

output
--------------------------------
Doc Eff Date Name seq
1 807231300 doc1 1
2 801010000 doc2 1
3 901031000 doc3 1[/code]

kevincallan
Starting Member

13 Posts

Posted - 2009-01-07 : 10:44:30

My problem is now solved and I have learned much from all of you.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 10:52:43

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources