Valid date range

[Harddisk]

Hi,

I'm not sure which forum I should post this in, but it has little relevance to math, or when it's under comparison.

I have a sample record as such
[part no] [start_date] [end_date] [return_value]
part123 2006-01-01 2006-12-31 A
part123 2006-02-01 2006-03-31 B
part123 2006-02-20 2006-08-01 C
part123 2006-03-01 2007-01-01 D

I know, if my date is:
1) 2006-01-01, returns A, not B, C, D
2) 2007-01-01, returns D, not A, B, C
3) 2006-02-05, returns A, B, not C, D
4) 2006-06-04, returns A, C, D, not B

Now, the thing is, for return of 1) and 2), there is only a single value returned.

As for return of 3) and 4), it gave me A & B and A, C, & D respectively. How do I further decide which is the VALID date range and return me the proper return_value?

Is there an algorithm to decide this?

[ganeshmoorthy]

what is the return_value that you want to retrieve...so, that we can help you to build the query...you need to write the algorithm to decide this...

[Harddisk]

return_value is actually pricing for the part123 over different period of time based on start_date and end_date.

But there might be records with overlapping dates, so I m not sure if there's a fixed standard on returning/selecting the record with the highest priority when a specific date falls on the overlapping date.

Such as, I want to get the price for part123 on date 2006-06-04.

Hope you can get a better picture with this.

Thank you.

[ganeshmoorthy]

Select * From YourTableName Where DateValue ('04/06/2006') Between Start_Date And End_Date Order By Return_Value

this will give you both the records A and C, but what is the priority that you want to use in the query...you can use order by clause...

[Harddisk]

Hi Ganesh!

Thanks for the syntax. Does it works under T-SQL?

My Query Analyzer returns:

Server: Msg 195, Level 15, State 10, Line 1
'DATEVALUE' is not a recognized function name.

BTW, my concern is really about the priority. Is there a set of rules in the ERP system on selecting the record when overlapping dates occurred?