Select based on String - Range issue

I am drawing a total blank on how to pull this one off.

I have two tables. One for item transactions and one for the cost of item based on the date of the transaction.

Table 1
Part - Bin - Qty - Date (String Value - YYMMDD)

Table 2
Part - Bin - Price - Effective_Date (String Value - YYMMDD)

For each record in Table 1 I need to pick the correct price in Table 2. In Table 2 where the 'Date' is greater than or equal to the Effective_Date but less than the next most recent Effective_Date is the correct price.

So in Table 1, I have:
Part = ABC
Bin = PURCH
Date = 161003

Table 2 has:
Part Bin   Price Effective_Date
ABC  PURCH 1.00  160101
ABC  PURCH 2.00  160929
ABC  PURCH 3.00  160926

In this case the correct 'Price' would be 2.00.

The part that is throwing me is normally I see a beginning and ending date for effective pricing or such. In this case it is not boxed in nicely.

Any Suggestions would be greatly appreciated!


Edited by ReportWriter14 - 03 Oct 2016 at 10:18am

If the table is sorted by Effective_Date then you might be able to do this if Date >=Effective_Date and Date <next(Effective_Date).  You may need to put in a check to make sure that there is a next(Effective_Date).  i.e., if isnull(Next(Effective_date)) then if date >= effective_date then (do something).

I hope this helps.

That does actually help but we can't count on the table to sorted. Which lead me to going into the DB and creating a custom view. We had a discussion over how to handle this and similar issues in the future this morning.

We came up with 3 options:
1. Custom SQL View just for the report.
2. Using Crystal Subreports to accomplish what we need to do (Far from ideal as the users like to extract to Excel and manipulate the data and subreports cause issues.)
3. Have the programmer code an end date into the program and store the value in the DB.

We chose number 3 because it in the end will save a lot of time for people running the reports and free up systems resources vs running complex queries.

You can sort the values in Crystal Reports (provided it does not cause an issue within the report).  You also can create a command (kinda your own custom view).  I avoid using sub reports when ever possible.