ok, this example, based on your provided data seems to work for me;

i'm assuming the discount rates are added to the same table...but i hope it is obvious that a join to a discount table would give the exact same results.

I'm making the following assumptions; i thought your example provided TWO discount rates for the same period...so i assume the latest rate is the rate to use...I'm getting all the rates that match the binding time period, and using row_number to order them into the set order i think you are expecting.

does this example represent what you are trying, or did i misread the requirement?

declare @Tariffs TABLE(

[id] [int] IDENTITY(1,1),

[Tariff_id] [int],

[Country_ID] [int],

[prefix] [varchar](20),

[destination_name] [varchar](100),

[rate] [numeric](9, 5),

[StartDate] [datetime],

[EndDate] [datetime] NULL

)

INSERT INTO @Tariffs(Tariff_id,Prefix,destination_name,rate,startdate,endDate)

--the default rate due to the 2079 end date

SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-05-22','2079-01-01' UNION ALL

--one specific discount rate.

SELECT '127','3360042','France-Bouygues Mobile','0.14','2010-05-22','2010-06-03' UNION ALL

--the rate AFTER that new discount

SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-06-04','2079-01-01' UNION ALL

--the rate was update to an even lower discoun t rate

SELECT '127','3360042','France-Bouygues Mobile','0.12','2010-05-22','2010-06-03' UNION ALL

--the rate after the discount is over.

SELECT '127','3360042','France-Bouygues Mobile','0.15','2010-06-04','2010-06-14'

--my assumption is all items get inserted, and you get the LATEST rate that is still valid

--see everything

SELECT ROW_NUMBER() OVER( PARTITION BY Tariff_id, Prefix, destination_name ORDER BY CASE WHEN ENDDATE = '2079-01-01' then 2 ELSE 1 END ,enddate DESC,id desc ) AS RW,

* FROM @Tariffs

where startdate <= '2010-06-01' and enddate >= '2010-06-01'

SELECT * FROM

(

SELECT ROW_NUMBER() OVER( PARTITION BY Tariff_id, Prefix, destination_name ORDER BY CASE WHEN ENDDATE = '2079-01-01' then 2 ELSE 1 END ,enddate DESC,id desc ) AS RW,

* FROM @Tariffs

where startdate <= '2010-06-01' and enddate >= '2010-06-01'

) x

WHERE RW = 1