473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Using Access for crunching Phone bills

Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per minute/60.

The problem arises with working out what time of day a call was made because
calls are charged at different rates at different times. The problem with
this is working out when a call crosses time bands & part of it needs to be
rated at one rate & part at another & then the two halves added together.

Specifically there are three time bands & so three possible rates for any
call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as would
Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at 18:10
would have 10 minutes rated at Daytime and 10 minutes rated at evening.

In an extreme example (and they do happen for dial up data etc - I've got a
bill on hand at the moment with many calls over 5 hours long & one as long
as 47 hours (!)) a call that started at 17:30 on a Friday and ended at 00:30
on Saturday morning would have 30 minutes rated as Daytime, 6 hours rated as
Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many seconds
of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out using
queries? In many cases with 100,000 plus calls on a bill Excel etc is not an
option.

rgds

Iain
Nov 13 '05 #1
5 1879
What I would do is decompose the problem. First, make a copy of the new call
data to process into a new table. Next run a query that determines any call
that starts in one time period, and continues it into another, and splits it
into 2 records, one for the minutes in the starting period, and one starting
at the beginning of the subsequrnt period, having the remaining minutes.

Running this query again on the same data will split any of the continuations
that need to be split again, so if you run it enough times, the data will be
fully decomposed. To do this in code, just the .Execute method of the DAO
Database object to run the query, and check the .RecordsAffected count after
each run. When .RecordsAffected is zero, that means there were no more
records to split.

At this point, no single record contains minutes at more than one rate, so you
can simply check the starting date/time, to get the rate for all the minutes
in a record.

On Fri, 23 Jul 2004 17:09:37 GMT, "Iain Miller" <do***@spam.me> wrote:
Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per minute/60.

The problem arises with working out what time of day a call was made because
calls are charged at different rates at different times. The problem with
this is working out when a call crosses time bands & part of it needs to be
rated at one rate & part at another & then the two halves added together.

Specifically there are three time bands & so three possible rates for any
call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as would
Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at 18:10
would have 10 minutes rated at Daytime and 10 minutes rated at evening.

In an extreme example (and they do happen for dial up data etc - I've got a
bill on hand at the moment with many calls over 5 hours long & one as long
as 47 hours (!)) a call that started at 17:30 on a Friday and ended at 00:30
on Saturday morning would have 30 minutes rated as Daytime, 6 hours rated as
Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many seconds
of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out using
queries? In many cases with 100,000 plus calls on a bill Excel etc is not an
option.

rgds

Iain


Nov 13 '05 #2
Have you tried getting your phone bill electronically from your
provider?

I built a database to analyse a phone companies electronic bill to match
the calls up to a call centre log so they knew which calls to bill which
client..... fortunately the bill came with rates/etc done for me:)

"Iain Miller" <do***@spam.me> wrote in message
news:lD***************@newsfe2-gui.ntli.net
Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per
minute/60.

The problem arises with working out what time of day a call was made
because calls are charged at different rates at different times. The
problem with this is working out when a call crosses time bands &
part of it needs to be rated at one rate & part at another & then the
two halves added together.

Specifically there are three time bands & so three possible rates for
any call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as
would Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at
18:10 would have 10 minutes rated at Daytime and 10 minutes rated at
evening.

In an extreme example (and they do happen for dial up data etc - I've
got a bill on hand at the moment with many calls over 5 hours long &
one as long as 47 hours (!)) a call that started at 17:30 on a Friday
and ended at 00:30 on Saturday morning would have 30 minutes rated as
Daytime, 6 hours rated as Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many
seconds of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out
using queries? In many cases with 100,000 plus calls on a bill Excel
etc is not an option.

rgds

Iain


--
regards,

Bradley
Nov 13 '05 #3
Hi Iain,
Give this a go...

I have a table named "tblCallDuration" which just stores just my "StartTime"
and "EndTime" values.
A second table named "tblCallRates" stores the "WkndRate", "EveRate", and
"DayRate" costs in a single record.

Then I have a form "frmCallDuration" which is bound to "tblCallDuration" so
that it displays the StartTime/EndTime for each call. There is also a
(pretty big) unbound textbox "txtCallStats" that is used to display the call
statistics.

Here is what the output looks like (using your example times):
**************************************
Record #1:
This call originated at: 7/23/2004 5:50:00 PM
and ended at: 7/23/2004 6:10:00 PM
The call length was: 20 minutes

Rate: DayTime
Minutes: 10 @ $0.25 = $2.50
---------------------

Rate: Evening
Minutes: 10 @ $0.15 = $1.50
---------------------

Record #2:
This call originated at: 7/23/2004 5:30:00 PM
and ended at: 7/24/2004 12:30:00 AM
The call length was: 420 minutes

Rate: DayTime
Minutes: 30 @ $0.25 = $7.50
---------------------

Rate: Evening
Minutes: 360 @ $0.15 = $54.00
---------------------

Rate: Weekend
Minutes: 30 @ $0.10 = $3.00
---------------------
**************************************
This is the code behind the form.
**********************************
Option Compare Database
Option Explicit

Dim varTime As Date
Dim varMins As Integer
Dim varRate As Currency
Dim VarCost As Currency
Dim strRate As String
Dim Msg As String
Dim CR As String
Dim varMyCheck

'--------------------------------------------------
Private Sub cmdGetCallStats_Click()
sGetTimesRates
End Sub

'--------------------------------------------------
Private Sub Form_Current()
sGetTimesRates
End Sub

'--------------------------------------------------
Public Sub sGetTimesRates()

Dim dteStart As Date
Dim dteEnd As Date

Me.Dirty = False 'Save any changes

If Me.NewRecord Then
Me.txtCallStats = "" 'Clear the unbound TextBox
Exit Sub
End If

CR = vbCrLf

If Not IsDate(Me.StartTime) Or Not IsDate(Me.EndTime) Then
Msg = ""
Msg = Msg & "Both a Start Time and End Time must be entered before
continuing!"
MsgBox (Msg)

Exit Sub
End If

dteStart = Me.StartTime
dteEnd = Me.EndTime
Msg = ""
Msg = Msg & "This call originated at: "
Msg = Msg & dteStart & CR
Msg = Msg & " and ended at: "
Msg = Msg & dteEnd & CR
Msg = Msg & " The call length was: "
Msg = Msg & DateDiff("n", dteStart, dteEnd)
Msg = Msg & " minutes" & CR & CR

varTime = dteStart 'Initialize to the start time of the call
varMins = 0
strRate = ""

Do While varTime < dteEnd
varMyCheck = WeekDay(varTime)
Select Case WeekDay(varTime)

Case 1 Or 7 'Sunday or Saturday -- Weekend Rate applies
Select Case strRate

Case "" 'This is the first minute of the call.
strRate = "Weekend"
varRate = DLookup("WkndRate", "tblCallRates")
varMins = 1
Case "Weekend"
'Do Nothing, except increment the minute counter
varMins = varMins + 1
Case Else 'This means that we have just entered the first
minute at this rate
'and it's time to tally up the charges for the
previous rate
sTallyRate
strRate = "Weekend"
varRate = DLookup("WkndRate", "tblCallRates")
varMins = 1
End Select

Case Else ' Monday to Friday
varMyCheck = Hour(varTime)

Select Case Hour(varTime)
Case 0 To 7, 18 To 23 '"Evening" hours
Select Case strRate
Case "" 'This is the first minute of the call.
strRate = "Evening"
varRate = DLookup("EveRate", "tblCallRates")
varMins = 1
Case "Evening"
'Do Nothing, except increment the minute counter
varMins = varMins + 1
Case Else 'This means that we have just entered the
first minute at this rate
'and it's time to tally up the charges for
the previous rate
sTallyRate
strRate = "Evening"
varRate = DLookup("EveRate", "tblCallRates")
varMins = 1
End Select

Case 8 To 17
Select Case strRate
Case "" 'This is the first minute of the call.
strRate = "DayTime"
varRate = DLookup("DayRate", "tblCallRates")
varMins = 1
Case "DayTime"
'Do Nothing, except increment the minute counter
varMins = varMins + 1
Case Else 'This means that we have just entered the
first minute at this rate
'and it's time to tally up the charges for
the previous rate
sTallyRate
strRate = "DayTime"
varRate = DLookup("DayRate", "tblCallRates")
varMins = 1
End Select
End Select
End Select

varTime = DateAdd("n", 1, varTime)

Loop

sTallyRate
Me.txtCallStats = Msg

End Sub

'--------------------------------------------------
Public Sub sTallyRate()
VarCost = 0
VarCost = (varMins * varRate)
CR = vbCrLf
Msg = Msg & "Rate: "
Msg = Msg & strRate & CR
Msg = Msg & "Minutes: "
Msg = Msg & varMins
Msg = Msg & " @ "
Msg = Msg & Format(varRate, "Currency")
Msg = Msg & " = " & Format(VarCost, "Currency") & CR
Msg = Msg & "---------------------" & CR & CR
Debug.Print Msg
End Sub
**********************************

--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Iain Miller" <do***@spam.me> wrote in message
news:lD***************@newsfe2-gui.ntli.net...
Trying to get my head round building a DB to analyse phone bills.

On the surface its fairly simple - duration in seconds x cost per minute/60.
The problem arises with working out what time of day a call was made because calls are charged at different rates at different times. The problem with
this is working out when a call crosses time bands & part of it needs to be rated at one rate & part at another & then the two halves added together.

Specifically there are three time bands & so three possible rates for any
call to a given destination

8am to 6pm weekdays is a Daytime rate
6pm to 8am is Evening rate
& Midnight on Friday night to Midnight on Sunday night is weekend rate

Within this 6pm on a Friday to Midnight would be rated as evening as would
Midnight on Sunday to 8am on Monday morning.

As an example, a call that started at 17:50 on a weekday and ended at 18:10 would have 10 minutes rated at Daytime and 10 minutes rated at evening.

In an extreme example (and they do happen for dial up data etc - I've got a bill on hand at the moment with many calls over 5 hours long & one as long
as 47 hours (!)) a call that started at 17:30 on a Friday and ended at 00:30 on Saturday morning would have 30 minutes rated as Daytime, 6 hours rated as Evening and 30 minutes rated as Weekend.

Generally I've got the start date and time of each call and the call
duration in seconds & so from that point I need to work out how many seconds of each call fall into each time band.

Anyone have any ideas as to how I could use Access to figure this out using queries? In many cases with 100,000 plus calls on a bill Excel etc is not an option.

rgds

Iain

Nov 13 '05 #4

"Don Leverton" <le****************@telusplanet.net> wrote in message
news:uCGMc.49426$2i3.12856@clgrps12...
Hi Iain,
Give this a go...


<SNIP much useful stuff>

Don,

thanks very much for your help

rgds

Iain
Nov 13 '05 #5

"Bradle)y" <br***********@comcenTHIS.com.au> wrote in message
news:41********@news.melbourne.pipenetworks.com...
Have you tried getting your phone bill electronically from your
provider?
Yep the bills I am looking at are in electronic format...
I built a database to analyse a phone companies electronic bill to match
the calls up to a call centre log so they knew which calls to bill which
client..... fortunately the bill came with rates/etc done for me:)


Yes, they all do that more or less

What I am trying to do is to compare bills using different carrier rates so
I need to take the start time & duration etc & then apply another set of
rates to end up with a comparison of what the final bills would be under 1,2
or more different carriers

thanks

Iain

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
5
by: Gene | last post by:
What can I do if I want to get the result using the sql command? for example, the select command is "select Name from Employee where StaffID=10" How to get the "Name"??? dim Name as string and...
0
by: Mitch | last post by:
Hi everyone, apologies if this is not the right group for this posting, please feel free to send me elsewhere. I currently have a web service that receives very simple data (a comma delimited...
24
by: cassetti | last post by:
Here's the issue: I have roughly 20 MS excel spreadsheets, each row contains a record. These records were hand entered by people in call centers. The problem is, there can and are duplicate...
16
by: Claudio Grondi | last post by:
I have a 250 Gbyte file (occupies the whole hard drive space) and want to change only eight bytes in this file at a given offset of appr. 200 Gbyte (all other data in that file should remain...
1
nirmalsingh
by: nirmalsingh | last post by:
hai all, i am using asp.net-C#. i want to know that is there is any way to control EPABX? using c#. if so kindly any provide me a sample code to do that. EPABX stands for Electronic Private...
7
by: Stephenoja | last post by:
Hello Guys, I have a challenge here and would really appreciate some help. I have customer bills that are all done in excel with a template and stored in directories by month order. At the end...
1
by: eemorris | last post by:
Hi, I'm trying to use an asp page to send data from a basic HTML form to an Access database. I'm using GoDaddy hosting (which apparently many people have issues with when it comes to this) and here...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.