How do i Find a related record matching a date range using MS SQL query

Question

Hi,

I have a table of PricePlan that looks like

Table PricePlan

ID MerchantID NAME       VALIDFROM  VALIDUPTO
1.   M1       Plan A      1-sep-09   30-sep-09
2.   M1       Plan B      7-sep-09   21-sep-09
3.   M2       Plan Da     1-sep-09   30-Sep-09

Given a @FromDate and @ToDate I need to find the matching id and Unique MerchantID. Example

@FromDate = '7-sep-09'
@Todate = '9-sep-09'

The return result should be ID 2- M1-Plan B , ID 3-M2-Plan Da

Can anyone help me on the SQL query

thanks in advance - Thomas

Answer 1

SELECT
 *
FROM
 PricePlan
WHERE
 ValidFrom <= @ToDate
AND
 ValidTo >= @FromDate

EDIT: This will find you all ranges that come under your given range. If you wish to prioritise one over the other in instances where two plans cover your specified dates then you will need to come up with that rule.

Answer 2

It seems like you want to select the plan whose boundary dates are most limiting - in other words, where the difference between @toDate and ValidFrom is minimized, and @FromDate and ValidTo are minimized as well.

If that is a correct restatement of your goal you could add to Robin Day's answer the code to order your result set by the difference (although, to be honest, I'm not sure if SQL supports arithmetic on dates) and select the first result using limit 1.

Answer 3

If I understand correctly, for each MerchantID you are looking for the plan that includes the requested dates, but is the one most recently in effect. To accomplish that, try the following query:

select ID, MerchantID, NAME
from PricePlan pp
inner join (
    select MerchantID, max(VALIDFROM) as VALIDFROM
    from PricePlan
    where VALIDFROM <= '7-sep-09'
     and VALIDUPTO >= '9-sep-09'
    group by MerchantID
) pp2 on pp.MerchantID = pp2.MerchantID
    and pp.VALIDFROM = pp2.VALIDFROM

Answer 4

try this:

DECLARE @YourTable  table (RowID int, Merchant char(2), NameOf varchar(7), ValidFrom datetime, ValidTo datetime)

INSERT INTO @YourTable VALUES (1,'M1','Plan A' ,'1-sep-09','30-sep-09')
INSERT INTO @YourTable VALUES (2,'M1','Plan B' ,'7-sep-09','21-sep-09')
INSERT INTO @YourTable VALUES (3,'M2','Plan Da','1-sep-09','30-Sep-09')

--SELECT * FROM @YourTable

DECLARE @FromD  datetime
DECLARE @ToD    datetime

SET @FromD  ='7-sep-09'
SET @ToD    ='9-sep-09'

SELECT TOP 1
    *
    FROM @YourTable
    WHERE ValidFrom <= @FromD AND ValidTo >= @ToD
    ORDER BY datediff(day,ValidFrom,@FromD)+datediff(day,@ToD,ValidTo) ASC, RowID

OUTPUT:

RowID       Merchant NameOf  ValidFrom               ValidTo
----------- -------- ------- ----------------------- -----------------------
2           M1       Plan B  2009-09-07 00:00:00.000 2009-09-21 00:00:00.000

(1 row(s) affected)