SQL query question - 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 2000 Forums

Transact-SQL (2000)

SQL query question

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-04-14 : 09:53:08

Hi,
I have the following table I wish to query

id, fromdate, untildate, salary

I want to be able to pass a date value in a query to show data from the table at the date value passed. Ie

param: 1/4/09

sounds easy, but its not as easy as I thought as the until date can be null and each id can have multiple fromdates, untildates and salaries.

ie.
1, 1/4/09, 13/4/09,200000
1, 1/1/09, 30/3/09,180000
1, 1/1/07, 31/12/08,150000
2, 1/4/08, NULL, 300000
2, 1/4/07, 30/3/09,200000

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-14 : 10:07:35

So are you saying that if you pass in 1/4/09, you only want to see records entered on that date or do you want that date and earlier?

For that date:


declare @Date datetime
set @Datetime = '20090401'
select id, fromdate, untildate, salary
from Table where fromdate >= @Date and fromdate <= @Date

For anything before that date:


declare @Date datetime
set @Datetime = '20090401'
select id, fromdate, untildate, salary
from Table where fromdate <= @Date

If this isn't correct, please explain what you are after in more detail.

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-04-14 : 10:12:06

I basically want to know someones salary at a specific Date (the date parameter)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-14 : 10:31:28

Ah OK, try this:


declare @Date datetime
set @Datetime = '20090401'
select a.id, b.salary, a.maxdate
from (select id, max(fromdate) as maxdate from Table where fromdate <= @Date group by id) a
inner join Table b
on b.id = a.id
and b.fromdate = a.maxdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources