Solved

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

Posted on 2006-10-26
6
209 Views
Last Modified: 2012-05-05
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
Comment
Question by:taycuong76
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:stevejel
ID: 17818034
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
 
LVL 3

Author Comment

by:taycuong76
ID: 17839235
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
 
LVL 2

Accepted Solution

by:
stevejel earned 50 total points
ID: 17839776
Yeah,
That does the trick, but it doesn't handle if the endEffDate overlaps with the EffectiveDate of another tariff.



0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Expert Comment

by:stevejel
ID: 17840429
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
 
LVL 3

Author Comment

by:taycuong76
ID: 17840624
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
 
LVL 3

Author Comment

by:taycuong76
ID: 17840652
This stuff works perfectly:

SELECT top 1 * FROM Table1 WHERE EffectiveDate <=  #09/29/2006# ORDER BY EffectiveDate DESC
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
groupNoAdj 7 105
Help to convert powershell script into a gui 9 199
Turning python script into an applet 12 116
What are programming codes and how do they work? 7 103
A short article about a problem I had getting the GPS LocationListener working.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question