# The ‘Call Log’ SQL Problem

 0 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 ..CallLog in CallLog.out -c -E -T bcp ..PhoneTariff in PhoneTariff.out -c -E -T bcp ..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 more ▼ asked Dec 02, 2009 at 01:07 PM in Default dave ballantyne 928 ● 1 ● 1 ● 4 Peso 1.6k ● 5 ● 6 ● 8 Can we be specific about how timings will be done? Average of x runs with a dropcleanbuffers before each? Average of x runs with a dropcleanbuffers before the first run? Dec 02, 2009 at 03:41 PM Matt Whitfield ♦♦ What about Abel Buck? His call was 0.000 seconds and thus no charge for length of call, but is he still going to pay for the connection of 52 cents? Dec 03, 2009 at 09:57 AM Peso I am tryting to find a 100% correct answer but sometimes i doubt if the answer privided by Robert Bar is 100% Correct. Should we take his answer as the final results or should we try to prove that there could be something wrong with it and propose something new ? Dec 03, 2009 at 10:33 AM Gustavo With regard to Abel Buck the call was not answered and therefore no charge. "Calls with a CallEnd equal to CallStart were not answered and can be safely ignored." The Robert Bar solution should be correct. If there are any issues with a calculation please PM me and ill manually double check. Dec 03, 2009 at 03:13 PM dave ballantyne I took a random case, number dialled: 980913853342, according to Robert Bar he used area code 980 and number 91-385-3342 (formated 2,3,4) ["order by len(CallArea) desc"], but you could also get area code 98 and number dialled 091-385-3342 ( formated 3-3-4 ). My point is that phone numbers of 12 digits ( this case ) should have just 2 chars on area code, those with 13 digits, have 3 chars as area code and so on, so the remaning number will still be formated 3-3-4 and not [1..3]-3-4. Meaning that the proposed solution could not be the corret one... Dec 03, 2009 at 06:15 PM Gustavo add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 William, Thanks. more ▼ answered Sep 15, 2010 at 09:24 AM Sampath 1 ● 1 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 -----Author Sampath Natarajan ``````Declare @User_Office table (WeekNo INT, Year INT, OfficeName VARCHAR(100),UserName VARCHAR(50),TOTAL MONEY) ; WITH CTE_callinfo (CallLogID, CallStart, callEND, OfficeName, UserName, NumberDialled, CallArea, MinutesUsed, Week, Year, PhoneTariffID, ConnectionCharge) as (SELECT CallLogID,DateAdd(dd, DateDiff(dd, 0, CallStart), 0)'CallStart',CallEND,OfficeName,UserName,NumberDialled, LEFT(NumberDialled,(len(NumberDialled)-len(right(NumberDialled,10))))'CallArea', CEILING(DateDiff(ss,CallStart,CallEND)/60.0)'MinutesUsed',DATEPART(wk,CallStart)'Week' ,DATEPART(yyyy,CallStart)'Year', PhoneTariffID,ConnectionCharge FROM dbo.CallLog a JOIN dbo.PhONeTariff b ON LEFT(NumberDialled,(len(NumberDialled)-len(right(NumberDialled,10))))=b.CallArea AND DateAdd(dd, DateDiff(dd, 0, CallStart), 0) BETWEEN datestart AND dateEND AND DatePart(hh,CallStart) BETWEEN HoursStart AND HoursEND ), CTE_PhoneTariffCharges (PhoneTariffID,UpToXMinutes,PricePerMinute,RowNumber )As (SELECT PhoneTariffID, UpToXMinutes,PricePerMinute, Row_Number() Over (PartitiON By PhoneTariffID Order By UpToXMinutes) As rn FROM PhONeTariffCharges a ), CTE_MinutesToAccount (PhoneTariffID,BeginMinute,UpToXMinutes,PricePerMinute,RowNumber,MinutesToAccount,Amount) as (SELECT a.PhoneTariffID,ISNULL(b.UpToXMinutes,0) AS BeginMinute,a.UpToXMinutes,a.PricePerMinute,a.RowNumber,a.UpToXMinutes - Coalesce(b.UpToXMinutes, 0), a.PricePerMinute*(a.UpToXMinutes - Coalesce(b.UpToXMinutes, 0)) FROM CTE_PhONeTariffCharges a LEFT JOIN CTE_PhONeTariffCharges b ON a.PhoneTariffID=b.PhoneTariffID AND a.RowNumber=b.RowNumber+1 ) INSERT INTo @User_Office (Weekno, Year, OfficeName,UserName,TOTAL) SELECT Week,Year,OfficeName,UserName, sum(CASE WHEN a.MinutesUsed>=b.uptoxminutes then (uptoxminutes -BeginMinute)*PricePerMinute ELSE (a.MinutesUsed-BeginMinute)*PricePerMinute END)+ConnectionCharge as 'NewAmount' FROM CTE_callinfo a JOIN CTE_MinutesToAccount b ON a.PhoneTariffID=b.PhoneTariffID WHERE RowNumber between 1 AND (SELECT max(RowNumber) from CTE_callinfo a1 JOIN CTE_MinutesToAccount b1 ON a1.PhoneTariffID=b1.PhoneTariffID JOIN CTE_callinfo c1 ON a1.CallLogID=c1.CallLogID AND a1.MinutesUsed between b1.BeginMinute AND b1.uptoxminutes AND a.CallLogID=c1.CallLogID)Group by Week,Year,OfficeName,UserName,ConnectionCharge SELECT Weekno, Year,UserName, TOTAL from @User_Office order by UserName SELECT Weekno, Year,OfficeName, sum(TOTAL) from @User_Office Group by Weekno, Year, OfficeName order by OfficeName `````` more ▼ answered Sep 14, 2010 at 02:57 PM Sampath 1 ● 1 I posted my code and it did not appear neat. How to post it so it appears neat and clear. Thanks Sampath Sep 14, 2010 at 03:02 PM Sampath @Sampath - I have reformated the code for you.Yyou need to highlight the code and either press Ctrl-K or click the code format option in the "Your Answer" box - this is an icon that is "101010" Sep 15, 2010 at 07:23 AM WilliamD add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 I would like to have a stab at this and future challenges also but I would like to know how to time my script.What is the method that is being used? more ▼ answered Feb 09, 2010 at 04:40 PM Ian Roke 1.7k ● 30 ● 33 ● 34 Well, we started off just timing the runs using the default timer you get in any client application - but we found that wasn't sufficient, so I ended up writing a test harness in C# that used a high-resolution timer to determine how long things were taking. Drop me a mail on mattw [at] code9 [dot] net and I'll send you a copy if you like... Feb 09, 2010 at 06:03 PM Matt Whitfield ♦♦ Thanks Matt I would really appreciate that. I will send you an email right away. Feb 09, 2010 at 06:45 PM Ian Roke add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Unfortunately I haven't had time to come up with a solution of my own... seems like it would be hard to compete with the ones provided so far though... some very nice coding. One thing I would like to mention as I don't think it has been, is that some of the solutions (Peso v4 family for example) take advantage of the fact that the CallAreas exist for the duration of the reporting period, so while they do solve the problem at hand, they may not be suitable for a real solution. I believe that if for example CallArea 35191 was not introduced as a separate phone tarrif until after the first call using it was made... rather than the call being charged at the 351 rate, it would not be charged at all. That is the call log would still match to the 35191 code, but when calculating the charge (connection mainly), there would be no record for it in the PhoneTariff matching the date range, causing the call to be 'forgotten' and not charged at all (some versions may just miss the connection charge). Of course this is all irrelevant, if when a new CallArea is introduced that it gets a back-dated copy of the tariff it was derived from (in this case a copy back to the start of the records of the 351 rate), or if indeed the list we were given was a view that ensured all tariffs were valid for the whole of the reporting period. For the problem at hand though, it's a nice trick that I'm sure sped up the results. more ▼ answered Dec 18, 2009 at 12:46 AM add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Figured out the various problems. I believe that some of the other solutions may not be including the ConnectionCharge in totals. Also was excluding about 10,000 UserNames because of the old datetime as a date + 00:00:00 not including if same date but time past midnight problem. Casted the CallStart to date before doing BETWEEN. I used lmu92's same indexes (thanks!). `````` CREATE CLUSTERED INDEX [CX_CallLog_1] ON [dbo].[CallLog] ([CallLogId],[CallStart],[CallEnd]) CREATE INDEX [IX_CallLog_2] ON [dbo].[CallLog] ([NumberDialled]) CREATE CLUSTERED INDEX [CX_PhoneTariff_1] ON [dbo].[PhoneTariff]([PhoneTariffId]) CREATE INDEX [IX_PhoneTariff_2] ON [dbo].[PhoneTariff]([CallArea]) CREATE CLUSTERED INDEX [CX_PhoneTariffCharges_1] ON [dbo].[PhoneTariffCharges] ([PhoneTariffId]) CREATE INDEX [IX_PhoneTariffCharges_2] ON [dbo].[PhoneTariffCharges] ([PhoneTariffId],[UpToXMinutes],[PricePerMinute]) `````` Code: ``````--Bluedog67 v4 - added NOLOCK hints ;WITH cteOffice ([WeekNo], [Year], Office, ConnectionCharge, UpToXMinutes, PricePerMinute, UpToXMinutesPrevious) AS ( select DATEPART(WEEK, C.CallStart) [WeekNo], DATEPART(yy, C.CallStart) [Year], C.OfficeName Office, PT.ConnectionCharge, CASE WHEN PTC1.UpToXMinutes > CAST(CEILING(DATEDIFF(S, C.CallStart, C.CallEnd) / 60.0) AS int) THEN CAST(CEILING(DATEDIFF(S, C.CallStart, C.CallEnd) / 60.0) AS int) ELSE PTC1.UpToXMinutes END [UpToXMinutes], PTC1.PricePerMinute, (SELECT ISNULL(MAX(PTC2.UpToXMinutes), 0) FROM PhoneTariffCharges PTC2 WITH (NOLOCK) WHERE PTC2.PhoneTariffId = PTC1.PhoneTariffId AND PTC2.UpToXMinutes < PTC1.UpToXMinutes) UpToXMinutesPrevious FROM CallLog C WITH (NOLOCK) JOIN PhoneTariff PT WITH (NOLOCK) ON C.NumberDialled LIKE RTRIM(PT.CallArea) + '%' AND CAST(C.CallStart as date) BETWEEN PT.DateStart AND PT.DateEnd AND DATEPART(hh, C.CallStart) BETWEEN PT.HoursStart AND PT.HoursEnd JOIN PhoneTariffCharges PTC1 WITH (NOLOCK) ON PT.PhoneTariffId = PTC1.PhoneTariffId WHERE C.CallStart <> C.CallEnd AND CAST(CEILING(DATEDIFF(S, C.CallStart, C.CallEnd) / 60.0) AS int) >= (SELECT ISNULL(MAX(PTC2.UpToXMinutes), 0) FROM PhoneTariffCharges PTC2 WITH (NOLOCK) WHERE PTC2.PhoneTariffId = PTC1.PhoneTariffId AND PTC2.UpToXMinutes < PTC1.UpToXMinutes) ) SELECT CTE.WeekNo, CTE.[Year], CTE.Office, SUM(CTE.ConnectionCharge + CTE.PricePerMinute * (CTE.UpToXMinutes - CTE.UpToXMinutesPrevious)) Total FROM cteOffice CTE GROUP BY WeekNo, [Year], Office; ;WITH cteUser (CallLogID, [WeekNo], [Year], UserName, ConnectionCharge, UpToXMinutes, PricePerMinute, UpToXMinutesPrevious) AS ( select C.CallLogID, DATEPART(WEEK, C.CallStart) [WeekNo], DATEPART(yy, C.CallStart) [Year], C.UserName, PT.ConnectionCharge, CASE WHEN PTC1.UpToXMinutes > CAST(CEILING(DATEDIFF(S, C.CallStart, C.CallEnd) / 60.0) AS int) THEN CAST(CEILING(DATEDIFF(S, C.CallStart, C.CallEnd) / 60.0) AS int) ELSE PTC1.UpToXMinutes END [UpToXMinutes], PTC1.PricePerMinute, (SELECT ISNULL(MAX(PTC2.UpToXMinutes), 0) FROM PhoneTariffCharges PTC2 WITH (NOLOCK) WHERE PTC2.PhoneTariffId = PTC1.PhoneTariffId AND PTC2.UpToXMinutes < PTC1.UpToXMinutes) UpToXMinutesPrevious FROM CallLog C WITH (NOLOCK) JOIN PhoneTariff PT WITH (NOLOCK) ON C.NumberDialled LIKE RTRIM(PT.CallArea) + '%' AND CAST(C.CallStart as date) BETWEEN PT.DateStart AND PT.DateEnd AND DATEPART(hh, C.CallStart) BETWEEN PT.HoursStart AND PT.HoursEnd JOIN PhoneTariffCharges PTC1 WITH (NOLOCK) ON PT.PhoneTariffId = PTC1.PhoneTariffId WHERE C.CallStart <> C.CallEnd AND CAST(CEILING(DATEDIFF(S, C.CallStart, C.CallEnd) / 60.0) AS int) > (SELECT ISNULL(MAX(PTC2.UpToXMinutes), 0) FROM PhoneTariffCharges PTC2 WITH (NOLOCK) WHERE PTC2.PhoneTariffId = PTC1.PhoneTariffId AND PTC2.UpToXMinutes < PTC1.UpToXMinutes) ) SELECT CTE.WeekNo, CTE.[Year], CTE.UserName, SUM(CTE.ConnectionCharge + CTE.PricePerMinute * (CTE.UpToXMinutes - CTE.UpToXMinutesPrevious)) Total FROM cteUser CTE GROUP BY WeekNo, [Year], UserName `````` Same as lmu92's cleanup (thanks again!): ``DROP INDEX [CX_CallLog_1] ON [dbo].[CallLog] DROP INDEX [IX_CallLog_2] ON [dbo].[CallLog]```DROP INDEX [CX_PhoneTariff_1] ON [dbo].[PhoneTariff] DROP INDEX [IX_PhoneTariff_2] ON [dbo].[PhoneTariff]DROP INDEX [CX_PhoneTariffCharges_1] ON [dbo].[PhoneTariffCharges] DROP INDEX [IX_PhoneTariffCharges_2] ON [dbo].[PhoneTariffCharges] ` more ▼ answered Dec 17, 2009 at 12:36 PM Bluedog67 1 ● 1 Gah. Code is seriously messed up! Will try and fix. Dec 17, 2009 at 12:38 PM Bluedog67 Ah I know why code is flaking out... gt and lt characters. Dec 17, 2009 at 12:44 PM Bluedog67 SELECT DATEPART(week, '2008-01-13 00:00:00') equals 3? In SQL 2008 Dev Edition. Huh? Dec 17, 2009 at 02:39 PM Bluedog67 "DatePart(Week..." is regional dependent. What is your SET LANGUAGE setting? What is your SET DATEFIRST setting? Dec 17, 2009 at 02:51 PM Peso SET DATEFIRST has impact on DATEPART(week, C.CallStart). Which also affects numbers. Dec 17, 2009 at 03:19 PM Bluedog67 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x346
x14
x8
x7

asked: Dec 02, 2009 at 01:07 PM

Seen: 7962 times

Last Updated: Feb 09, 2010 at 10:54 AM