472,111 Members | 1,992 Online

# 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 1783
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 discussion thread is closed

Replies have been disabled for this discussion.

### Similar topics

 4 posts views Thread by Skully Matjas | last post: by 5 posts views Thread by Gene | last post: by reply views Thread by Mitch | last post: by 24 posts views Thread by cassetti | last post: by 16 posts views Thread by Claudio Grondi | last post: by 1 post views Thread by nirmalsingh | last post: by 7 posts views Thread by Stephenoja | last post: by 1 post views Thread by eemorris | last post: by reply views Thread by isladogs | last post: by 1 post views Thread by beacampos | last post: by reply views Thread by pddon | last post: by reply views Thread by bakertaylor28 | last post: by reply views Thread by leo001 | last post: by reply views Thread by antdb | last post: by reply views Thread by antdb | last post: by 2 posts views Thread by Bright1Light | last post: by 5 posts views Thread by Bright1Light | last post: by

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.