Once again it is time for a Phil Factor Speed Phreak Challenge and the prize is now a $100 Amazon voucher, and the privilege of displaying this rather nice trophy jpg on your blog / website / bedroom wall.
This time your task is simply to produce a summary report of the cost of international phone calls made within an organization on a week by week basis for both Users and Offices. You are allowed to use any method you see fit, you may also add any indexes, table-functions or views that you wish (though not an index view). Creation of these will not count to the overall execution time. If you are unsure if what you want to do might disqualify you, then please post a comment.
The table CallLog contains the log of every phone call, including which user called which number, when the call started and ended, and the office the user was in at the time. You will notice that it is not well normalized, since it is actually a view taken from several tables. Users are never in a fixed office, and can move from office to office at any point. Calls with a CallEnd equal to CallStart were not answered and can be safely ignored.
To calculate the cost of the call you need to use a lookup within the PhoneTariff table. The calls are prefixed by an area code corresponding to a country.
You must note that many of the call areas have the same starting character sequence , so calls made to numbers starting '35191' must be priced using the tariff of '35191' not '351'.
There is a rather elaborate charging system according to the length of the call. This means that you need to calculate the cost of the call as the minute by minute cost changes with the length of the call. These must be summed.
Looking at the PhoneTariffCharges table
PhoneTariffId UpToXMinutes PricePerMinute
------------- ------------ ---------------------
2865 8 0.4792
2865 31 0.18
2865 59 0.5702
2865 100 1.298
The first 8 minutes of the call will be 0.4792 per minute. 9 to 31 minutes will be 0.18 per minute 32 to 59 minutes will be 0.5702 per minute etc.... All ranges have a UpToXMinutes of 9999 so you dont need to worry about an upper limit. The calllength is rounded upto the nearest whole minute.
Here is the DDL to create the tables
CREATE TABLE [dbo].[CallLog](
[CallLogId] [int] NOT NULL,
[CallStart] [datetime] NOT NULL,
[CallEnd] [datetime] NULL,
[OfficeName] [varchar](20) NOT NULL,
[UserName] [varchar](20) NOT NULL,
[NumberDialled] [varchar](255) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneTariff](
[PhoneTariffId] [int] IDENTITY(1,1) NOT NULL,
[CallArea] [char](6) NULL,
[DateStart] [datetime] NULL,
[DateEnd] [datetime] NULL,
[HoursStart] [int] NULL,
[HoursEnd] [int] NULL,
[ConnectionCharge] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneTariffCharges](
[PhoneTariffId] [int] NULL,
[UpToXMinutes] [int] NULL,
[PricePerMinute] [money] NULL
) ON [PRIMARY]
GO
and heres the link to the data. Use
bcp <yourdatabase>..CallLog in CallLog.out -c -E -T
bcp <yourdatabase>..PhoneTariff in PhoneTariff.out -c -E -T
bcp <yourdatabase>..PhoneTariffCharges in PhoneTariffCharges.out -c -E -T
to load the data in.
Heres the solution provided by our mediocre developer Robert Bar, please note a fix for an issue with the week number being taken from @CallEnd.
Drop Table #OfficeTotalByWeek
go
Drop Table #UserTotalByWeek
go
Create Table #OfficeTotalByWeek
(
WeekNo integer,
Year integer,
Office varchar(255),
Total money
)
go
Create Table #UserTotalByWeek
(
WeekNo integer,
Year integer,
UserName varchar(255),
Total money
)
go
/* Routine : Call Cost Calculate */
/* Developer : R Bar (RB) */
declare @CallStart datetime,
@CallEnd datetime,
@OfficeName varchar(255),
@UserName varchar(255),
@NumberDialled varchar(255),
@PhoneTariffId integer,
@ConnectionCharge money,
@RangeStart datetime,
@RangeEnd datetime
declare CalcCursor Cursor
for Select CallStart,CallEnd,OfficeName,UserName,NumberDialled
from CallLog
-- where OfficeName = 'Marketing'
--and UserName = 'Yolanda Haley'
open CalcCursor
while(0=0) begin
/* RB : For Each Call in CallLog */
fetch next from CalcCursor into @CallStart,@CallEnd,@OfficeName,@UserName,@NumberDialled
if(@@Fetch_Status <>0) begin
break
end
if(@CallStart = @CallEnd) begin /* RB : If True then the call was not answered so no charge */
continue
end
/* RB */
/* Find the appropriate Tariff */
/* The number dialled must match the CallArea */
/* But as the length of the CallArea is variable */
/* An index seek cannot be used */
Declare @CallDatech varchar(15)
Select @CallDatech = CONVERT(varchar(15),@CallStart,112)
select @PhoneTariffId = NULL
set rowcount 1
Select @PhoneTariffID = PhoneTariffId,
@ConnectionCharge = ConnectionCharge,
@RangeStart = DateStart,
@RangeEnd = DateEnd
from phonetariff where @NumberDialled like rtrim(CallArea)+'%'
and @CallDateCh between DateStart and DateEnd
and DatePart(hh,@CallStart) between HoursStart and HoursEnd
order by len(CallArea) desc /* RB : Order by is used so the longest CallArea is used */
set rowcount 0
if(@PhoneTariffId is null) begin
select 'early break out error ',@NumberDialled
end
Declare @CallLength integer
Select @CallLength = ceiling((DATEDIFF(S,@CallStart,@CallEnd))/60.0)
Declare @RollingBalance money
Declare @UptoXminutes integer
Declare @PricePerMinute money
Declare @MinsThisIter integer
/* RB : Start the CallCost at the Connection Charge */
Select @RollingBalance = @ConnectionCharge
--Select @ConnectionCharge as 'ConnectionCharge'
declare @PrevUptoXMinutes integer
select @PrevUptoXMinutes =0
Declare TimeCalc Cursor for
Select UpToXMinutes,PricePerMinute
from Phonetariffcharges
where PhoneTariffId = @PhoneTariffID
order by UpToXMinutes
Open timecalc
/* RB : Now Loop through the minute by minute cost totalling up as we go */
while(0=0) begin
fetch next from timecalc into @UptoXminutes,@PricePerMinute
if(@@FETCH_STATUS <>0) begin
select 'early break out error',@NumberDialled,@PhoneTariffId
break
end
Declare @MinsToIter integer
/* RB : Calculate how many minutes from this portion we need */
Select @MinsThisIter = case when @uptoXminutes > @CallLength then @CallLength -@PrevUptoXMinutes
else @uptoXminutes -@PrevUptoXMinutes
end
Select @RollingBalance = @RollingBalance +(@MinsThisIter*@PricePerMinute)
Select @PrevUptoXMinutes = @uptoXminutes
if(@uptoXminutes>=@CallLength)begin /* RB : Have we accounted for each minute */
break
end
end
close TimeCalc
Deallocate TimeCalc
if(@PrevUptoXMinutes = 0) begin
select 'Charges Calc Error',@NumberDialled,@PhoneTariffId,@CallLength
end
Declare @Year integer
Declare @WeekNo integer
Select @Year=DATEPART(yy,@CallStart),
@WeekNo=DATEPART(WEEK,@CallStart)
/* RB : Update the Users week-by-week total */
Update #UserTotalByWeek
set Total = Total + @RollingBalance
where UserName =@UserName
and WeekNo = @WeekNo
and Year = @Year
if(@@ROWCOUNT=0) begin
/* RB : Or Create */
insert into #UserTotalByWeek(UserName,WeekNo,Year,Total)
values(@UserName,@WeekNo,@Year,@RollingBalance)
end
/* RB : Update the Office week-by-week total */
Update #OfficeTotalByWeek
set Total = Total + @RollingBalance
where Office = @OfficeName
and WeekNo = @WeekNo
and Year = @Year
if(@@ROWCOUNT=0) begin
/* RB : Or Create */
insert into #OfficeTotalByWeek(Office,WeekNo,Year,Total)
values(@OfficeName,@WeekNo,@Year,@RollingBalance)
end
end
close CalcCursor
Deallocate CalcCursor
go
/* RB : Select the totals back */
Select * from #OfficeTotalByWeek
Select * from #UserTotalByWeek
go
Drop Table #OfficeTotalByWeek
go
Drop Table #UserTotalByWeek
go
Here are some guidelines for your entries:
1) Include a header in your suggestion. Make sure your name and the current date is present.
2) Include an edition number. First edition is 1. If you later improve your current suggestion post it again as version 2. Example: “Peso 1” and if improved, “Peso 1b”, “Peso 1c” etc.
3) If you are trying a new algorithm, change the edition to “Peso 2”. If you improve this algorithm, change the version to “Peso 2b”, “Peso 2c” etc. This will save Phil hours of work in the test harness!
4) The solution must clear up all its mess (temporary tables, indexes, etc.) so it can be re-run without errors.
As ever Phil Factor will be final judge.
The closing date will be midnight Thursday 17th December London
