Solved

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

Posted on 2006-10-26
6
208 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
This is an explanation of a simple data model to help parse a JSON feed
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

808 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