Select Query - 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)

Select Query

Author

Topic

cvipin
Yak Posting Veteran

51 Posts

Posted - 2010-03-01 : 14:23:55

Hi,

I need to write a query to get mentioned output. The test data is as below: Can you help..

declare @test table (effective_date datetime, expiration_date datetime, product varchar(5), rate decimal(10, 4))

Insert into @test values ('02/26/2010 8:00:00','02/27/2010 7:59:59','P1', 4.0000 )
Insert into @test values ('02/26/2010 8:00:00','02/27/2010 7:59:59','P2', 4.1000 )
Insert into @test values ('02/26/2010 8:00:00','02/27/2010 7:59:59','P3', 4.1500 )
Insert into @test values ('02/26/2010 8:00:00','02/27/2010 7:59:59','P4', 4.1800 )
Insert into @test values ('02/27/2010 8:00:00','02/28/2010 7:59:59','P1', 4.0100 )
Insert into @test values ('02/27/2010 8:00:00','02/28/2010 7:59:59','P2', 4.1200 )
Insert into @test values ('02/27/2010 8:00:00','02/28/2010 7:59:59','P3', 4.1550 )
Insert into @test values ('02/27/2010 8:00:00','02/28/2010 7:59:59','P4', 4.1900 )
Insert into @test values ('02/28/2010 8:00:00','03/01/2010 7:59:59','P1', 4.0100 )
Insert into @test values ('02/28/2010 8:00:00','03/01/2010 7:59:59','P3', 4.1550 )
Insert into @test values ('02/28/2010 8:00:00','03/01/2010 7:59:59','P4', 4.1900 )

I need to select data for a data, '2/28/2010 9:15:25'. This date should be between effective_date and expiration_date
I need ouput as following: (P2 is also selected which is latest available rate)

effective_date expiration_date product rate
2010-02-28 08:00:00.000 2010-03-01 07:59:59.000 P1 4.0100
2010-02-27 08:00:00.000 2010-02-28 07:59:59.000 P2 4.1200
2010-02-28 08:00:00.000 2010-03-01 07:59:59.000 P3 4.1550
2010-02-28 08:00:00.000 2010-03-01 07:59:59.000 P4 4.1900

Thanks
Vipin

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-01 : 14:55:48

Is '2/28/2010 9:15:25' Arbitrary?

Select * 
from @test 
where effective_date < '2/28/2010 9:15:25' 
and expiration_date > '2/28/2010 9:15:25'

You will need to do a Union to join the Max availible rate.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2010-03-01 : 15:03:39

No. This date '2/28/2010 9:15:25' is an input parameter. The input date will have time parameter also..

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-01 : 18:34:18

[code]

declare @myDate datetime

Set @myDate = '2/28/2010 9:15:25'

Select *
from @test
where effective_date < @myDate
and expiration_date > @myDate

[/code]

Make the changes in italics, you will have to find a way to pass the parameter from your front end application. If you are going to run this sql code via dynamic sql be careful of SQL injection. But for needs it may be the only way to go about it. This is a decent start though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 09:00:21

can you explain how you get below in output?

2010-02-27 08:00:00.000 2010-02-28 07:59:59.000 P2 4.1200

as per your reqmnt date should be between effective_date and expiration_date which is not true in above case '2/28/2010 9:15:25' comes after 2010-02-28 07:59:59.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources