Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Get the right price from a table providing the prices that effective for a time periods

Hi experts, my problem is:
To do pbx billing project, I have a table in which provide the price that effective for a time period,
ex:
Price   EffectiveDate      EndEffDate
===   ========      =======    
100      20-01-2006      30-06-2006
150      01-07-2006      30-09-2006
 90       31-09-2006     15-11-2006
.....
(price 100 is effective from 20-01-2006 to 30-06-2006
price 150 is effective from 01-07-2006 to 30-09-2006
price 90 is effective from 31-09-2006 to 15-11-2006
.........)

and I have a call made at 29-9-2006 15:30:00
So, how do I select the right tariff for the call from a table, it means price 150 is the results.

Thank you in advance.
 
0
taycuong76
Asked:
taycuong76
  • 3
  • 3
1 Solution
 
stevejelCommented:
I'm assuming SQL here:

SELECT Price FROM YourTable
WHERE DATE("2006-9-29 15:30:00") >= EffectiveDate
  AND DATE("2006-9-29 15:30:00") <= EndEffDate
ORDER BY EffectiveDate
LIMIT 1;

You need to decide how you want to handle any overlap of dates, in this implementation I have gone with the earliest effective date has priority, you will have to make that design decision though.

Steve
0
 
taycuong76Author Commented:
LIMIT 1; not work on SQL Access.

Does it do the task?
select price from Table1 Where '06/29/2006' between EffectiveDate and EndEffDate

ok, if only I have a table with 2 fields:
Price     EffectiveDate
===     ========
100       20-01-2006
150       01-07-2006
90         31-09-2006
and I have a call made at 29-9-2006 15:30:00
So, how do I select the right tariff for the call from a table, it means price 150 is the results

0
 
stevejelCommented:
Yeah,
That does the trick, but it doesn't handle if the endEffDate overlaps with the EffectiveDate of another tariff.



0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
stevejelCommented:
SELECT Price from Table1 where EffectiveDate IN( Select MAX(EffectiveDate) FROM Table1 WHERE EffectiveDate < "29-9-2006");

Should do it, but again it doesn't handle duplicates.

Steve
0
 
taycuong76Author Commented:
How do you think about it?
EffectiveDate                       Tariff
01/01/2006             1000
02/01/2006              2000
03/01/2006              3000

DTPicker1 - use to selec date
Label1 - use to get time

Private Sub Command1_Click()
Dim rs As New ADODB.Recordset
Dim str
Dim maxdate As Date
str = "SELECT Max(EffectiveDate) FROM Table1 WHERE"
str = str & " EffectiveDate <= # " & Format(DTPicker1, "MM/dd/yyyy") & " #"
rs.Open str, cn, adOpenKeyset, adLockOptimistic, adCmdText
If rs.EOF = False Then
If rs!Expr1000 <> "" Then
maxdate = rs!Expr1000
rs.Close
str = "select Tariff FROM Table1 WHERE EffectiveDate = # " & Format(maxdate, "MM/dd/yyyy") & " #"
rs.Open str, cn, adOpenKeyset, adLockOptimistic, adCmdText
If rs.EOF = False Then
Label1.Caption = rs!Tariff
Else
Label1.Caption = ""
End If
rs.Close
Else
Label1.Caption = ""
End If
Else
Label1.Caption = ""
End If
End Sub
0
 
taycuong76Author Commented:
This stuff works perfectly:

SELECT top 1 * FROM Table1 WHERE EffectiveDate <=  #09/29/2006# ORDER BY EffectiveDate DESC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now