Solved

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

Posted on 2006-10-26
6
206 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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…

760 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

25 Experts available now in Live!

Get 1:1 Help Now