Solved

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

Posted on 2006-10-26
6
207 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
endX challenge 2 63
Arduino EDI - Programming language 3 80
C Programming - If Statement 8 72
iframe detection of parent window scale 20 60
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now