x

The ‘Call Log’ SQL Problem

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.

alt text

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

more ▼

asked Dec 02, 2009 at 01:07 PM in Default

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

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
(comments are locked)
10|1200 characters needed characters left

21 answers: sort voted first

Here's what I came up with (so far...) In terms of performance I'm in a range of less than 5sec (including output of the results which takes about 0.6 sec). Just to compare: I'm getting approx. 8sec for Phils solution on my PC (thank you for leaving the timing harness in there!).

/* Phone report lmu92_1a */

/* Step 1: adding index to the original table */ CREATE CLUSTERED INDEX [CX_CallLog_1] ON [dbo].CallLog CREATE INDEX [IX_CallLog_2] ON [dbo].CallLog

CREATE CLUSTERED INDEX [CX_PhoneTariff_1] ON [dbo].PhoneTariff CREATE INDEX [IX_PhoneTariff_2] ON [dbo].PhoneTariff

CREATE CLUSTERED INDEX [CX_PhoneTariffCharges_1] ON [dbo].PhoneTariffCharges CREATE INDEX [IX_PhoneTariffCharges_2] ON [dbo].PhoneTariffCharges

SET nocount ON

/* Step 2: check for temp table to be created and drop if existing */

if object_id('tempdb..#callAreaLogID') is not NULL DROP TABLE #callAreaLogID if object_id('tempdb..#UserTotalByWeek') is not NULL DROP TABLE #UserTotalByWeek if object_id('tempdb..#OfficeTotalByWeek') is not NULL DROP TABLE #OfficeTotalByWeek

/* Step 3: create internal table to hold results based on CallLog.callLogID, but in a format easier to use */

CREATE TABLE [dbo].[#callAreaLogID]( [callLogID] [int] NOT NULL, [callArea] [varchar](6) NOT NULL, [weekNo] INT NOT NULL, [Year] INT NOT NULL, [CallStartDay] SMALLDATETIME NOT NULL, [CallStartHour] INT NOT NULL, [CallDuration] INT NOT NULL, [PhoneTariffID] INT NULL, [ConnectionCharge] money NULL, [CallCharge] money NULL ) ON [PRIMARY]

/* Step 4: create final tables as per sample provided

*/ Create Table #OfficeTotalByWeek ( WeekNo integer, Year integer, Office varchar(255), Total money )

Create Table #UserTotalByWeek ( WeekNo integer, Year integer, UserName varchar(255), Total money )

/* Step 5: assign the correct area code per call a) get the different CallArea entries from PhoneTariff with a CTE using a simple GROUP BY b) to join the "not well normalized" area codes: join on "NumberDialled LIKE RTRIM(callarea) + '%'" together with a MAX aggregation c) to exclude orphaned calls: "WHERE CallStart <> CallEnd" d) the CEILING function is used to round the call duration to the next minute */ ; WITH ctePhoneTariffArea AS ( SELECT CallArea AS CallArea FROM dbo.PhoneTariff GROUP BY CallArea ) INSERT INTO #callAreaLogID ( [callLogID], [callArea], [weekNo], [Year], [CallStartDay], [CallStartHour], [CallDuration]) SELECT callLogID, RTRIM(max(callArea)) AS callArea, DATEPART(wk,CallStart) as [weekNo], DATEPART(yy,CallStart) as [Year], dateadd(dd, datediff(dd, 0, CallStart), 0) AS [CallStartDay], DATEPART(hh,CallStart) AS [CallStartHour], CEILING(DATEDIFF(ss,CallStart,CallEnd)/60.0) AS [CallDuration] FROM CallLog INNER JOIN ctePhoneTariffArea ON NumberDialled LIKE RTRIM(callarea) + '%' WHERE CallStart <> CallEnd GROUP BY calllogid, DATEPART(wk,CallStart), DATEPART(yy,CallStart), DATEADD(dd, DATEDIFF(dd, 0, CallStart), 0) , DATEPART(hh,CallStart), CEILING(DATEDIFF(ss,CallStart,CallEnd)/60.0)

/* Step 6: get the connection charge per call */ UPDATE #callAreaLogID SET
[ConnectionCharge] =t.connectionCharge, [PhoneTariffID] = t.PhoneTariffID FROM #callAreaLogID c INNER JOIN dbo.PhoneTariff t ON c.callArea = t.callarea AND c.[CallStartDay] >= t.DateStart AND c.[CallStartDay] <= t.DateEnd AND c.[CallStartHour] >= t.HoursStart AND c.[CallStartHour] <= t.HoursEnd

/* Step 7: get the call charge per call using CTEs a) cte is used to assign a row number order by UpToXMinutes for each PhoneTariffId b) chargeables calculates the relevant minutes to be charged per [UpToXMinutes]
Note: the UNION operation is added to add the range from 0 minutes to the first given range c) MaxMinutePerCall gets the relevant upper limit for [UpToXMinutes] per call d) preSum does the aggregation of call duration costs Note: As long as the duration per call is larger than or equal to UpToXMinutes, a simple multiplication is done. The last portion of the call is calculated based on the remaining minutes within that range */ ;With cte AS ( SELECT PhoneTariffId, row_number() OVER (partition BY PhoneTariffId ORDER BY UpToXMinutes) AS row, UpToXMinutes, PricePerMinute FROM PhoneTariffCharges ), chargeables AS ( SELECT
cte1.PhoneTariffId, cte2.uptoxminutes AS Rangestart, cte1.UpToXMinutes, cte1.PricePerMinute, cte1.UpToXMinutes-cte2.UpToXMinutes AS MinutesToCharge FROM cte cte1 INNER JOIN cte cte2 ON cte1.PhoneTariffId = cte2.PhoneTariffId AND cte1.row = cte2.row + 1 UNION ALL SELECT cte1.PhoneTariffId, 0, cte1.UpToXMinutes, cte1.PricePerMinute, cte1.UpToXMinutes FROM cte cte1 WHERE cte1.row=1 ), MaxMinutePerCall AS ( SELECT t.[callLogID], t.[CallDuration] AS [CallDuration], c.PhoneTariffId, min(UpToXMinutes) AS maxMinutes FROM #callAreaLogID t INNER JOIN PhoneTariffCharges c ON t.PhoneTariffId = c.PhoneTariffId WHERE c.UpToXMinutes >= t.[CallDuration] GROUP BY t.[callLogID],[CallDuration],c.PhoneTariffId ) , preSum AS ( SELECT m.[callLogID], SUM (CASE WHEN callduration >= UpToXMinutes THEN minutestocharge * PricePerMinute ELSE (Callduration - Rangestart) * PricePerMinute END) AS charge FROM chargeables c INNER JOIN MaxMinutePerCall m ON c.PhoneTariffId = m.PhoneTariffId WHERE c.uptoxminutes <= m.maxMinutes GROUP BY m.[callLogID] ) UPDATE #callAreaLogID SET #callAreaLogID.CallCharge = p.charge FROM preSum p INNER JOIN #callAreaLogID t ON p.callLogID = t.callLogID

/* Step 8: fill result table #OfficeTotalByWeek Note: to insert the data and display it at the same time the OUTPUT clause is used */

INSERT INTO #OfficeTotalByWeek OUTPUT INSERTED.[weekNo], INSERTED.[year], INSERTED.Office, INSERTED.Total SELECT
[weekNo], [year], officename, sum(connectioncharge + callcharge) FROM dbo.CallLog c INNER JOIN #callAreaLogID a ON c.CallLogId = a.CallLogId GROUP BY [weekNo], [year], officename

/* Step 9: fill result table #UserTotalByWeek Note: to insert the data and display it at the same time the OUTPUT clause is used */ INSERT INTO #UserTotalByWeek OUTPUT INSERTED.[weekNo], INSERTED.[year], INSERTED.username, INSERTED.Total SELECT [weekNo], [year], username, sum(connectioncharge+callcharge) FROM dbo.CallLog c INNER JOIN #callAreaLogID a ON c.CallLogId = a.CallLogId GROUP BY [weekNo], [year], username

/* Step 10: cleanup internal tables */ DROP TABLE #callAreaLogID DROP TABLE #UserTotalByWeek DROP TABLE #OfficeTotalByWeek

/* Step 11: cleanup index on base tables */ 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 05, 2009 at 09:44 PM

lmu92 gravatar image

lmu92
21

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Dec 07, 2009 at 10:07 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

Source code to my CLR version (for reference):

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; using PhilFactorChallenge3; using System.Text;

namespace PhilFactorChallenge3 { internal class CallArea { public int AreaCode; public List<TariffBlock> Blocks = new List<TariffBlock>(16); public CallArea(int areaCode) { AreaCode = areaCode; } }

internal class ReportEntry
{
    public string Key;
    public int Year;
    public int Week;
    public Decimal TotalCost;

    public ReportEntry(string key, int year, int week, Decimal totalCost)
    {
        Key = key;
        Year = year;
        Week = week;
        TotalCost = totalCost;
    }
}

internal class TariffBlock
{
    public DateTime DateStart;
    public DateTime DateEnd;
    public byte HoursStart;
    public byte HoursEnd;
    public Decimal ConnectionCharge;
    public List&lt;TariffRate&gt; Rates = new List&lt;TariffRate&gt;(16);
    public TariffBlock(DateTime dateStart, DateTime dateEnd, byte hoursStart, byte hoursEnd, Decimal connectionCharge)
    {
        DateStart = dateStart;
        DateEnd = dateEnd;
        HoursStart = hoursStart;
        HoursEnd = hoursEnd;
        ConnectionCharge = connectionCharge;
    }
}

public class TariffRate
{
    public int UpToXMinutes;
    public Decimal PricePerMinute;
    public TariffRate(int upToXMinutes, Decimal pricePerMinute)
    {
        UpToXMinutes = upToXMinutes;
        PricePerMinute = pricePerMinute;
    }
}

}

public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void GetReport() { using (SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); Dictionary<int, CallArea> callAreas = new Dictionary<int, CallArea>(200); int maxCallArea = 0; using (SqlCommand command = new SqlCommand("SELECT convert(int, [CallArea]), [DateStart], [DateEnd], convert(tinyint, [HoursStart]), convert(tinyint, [HoursEnd]), [ConnectionCharge], [UpToXMinutes], [PricePerMinute] FROM [dbo].[PhoneTariff] [pt] INNER JOIN [dbo].[PhoneTariffCharges] [ptc] ON [ptc].[PhoneTariffId] = [pt].[PhoneTariffId] ORDER BY [CallArea], [DateStart], [HoursStart], [UpToXMinutes]", connection)) { using (SqlDataReader reader = command.ExecuteReader()) { int lastUpToXMinutes = 999999999; int lastCallArea = -1; CallArea currentCallArea = null; TariffBlock currentBlock = null; List<TariffBlock> currentAreaBlocks = null; List<TariffRate> currentBlockRates = null; while (reader.Read()) { // if a new call area then create a new call area instance int callArea = reader.GetInt32(0); if (callArea != lastCallArea) { lastCallArea = callArea; if (callArea > maxCallArea) { maxCallArea = callArea; }

                        currentCallArea = new CallArea(callArea);
                        currentAreaBlocks = currentCallArea.Blocks;
                        callAreas.Add(callArea, currentCallArea);
                        lastUpToXMinutes = 999999;
                    }

                    int upToXMinutes = reader.GetInt32(6);
                    Decimal pricePerMinute = reader.GetDecimal(7);

                    if (upToXMinutes &lt; lastUpToXMinutes)
                    {
                        DateTime dateStart = reader.GetDateTime(1);
                        DateTime dateEnd = reader.GetDateTime(2);
                        byte hoursStart = reader.GetByte(3);
                        byte hoursEnd = reader.GetByte(4);
                        Decimal connectionCharge = reader.GetDecimal(5);

                        // we're on a new block
                        currentBlock = new TariffBlock(dateStart, dateEnd, hoursStart, hoursEnd, connectionCharge);
                        currentAreaBlocks.Add(currentBlock);
                        currentBlockRates = currentBlock.Rates;
                    }
                    lastUpToXMinutes = upToXMinutes;

                    // now add the current row to the current block
                    currentBlockRates.Add(new TariffRate(upToXMinutes, pricePerMinute));                        
                }
            }
        }

        Dictionary&lt;string, ReportEntry&gt; officeReport = new Dictionary&lt;string, ReportEntry&gt;(200);
        Dictionary&lt;string, ReportEntry&gt; usersReport = new Dictionary&lt;string, ReportEntry&gt;(80000);
        int maxDigits = maxCallArea.ToString().Length;
        StringBuilder officeKeyBuilder = new StringBuilder(16);
        StringBuilder userKeyBuilder = new StringBuilder(16);
        using (SqlCommand command = new SqlCommand("SELECT YEAR([CallStart]) AS Yr, DATEPART(week, [CallStart]) AS Wk, DATEPART(hh, [CallStart]) AS hr, (DATEDIFF(s, [cl].[CallStart], [cl].[CallEnd]) + 59) / 60 AS MinutesDuration, [OfficeName], [UserName], convert(int, LEFT([NumberDialled], " + maxDigits.ToString() + ")), [CallStart] FROM [dbo].[CallLog] [cl] WHERE [CallStart] != [CallEnd]", connection))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    int year = reader.GetInt32(0);
                    int week = reader.GetInt32(1);
                    int hour = reader.GetInt32(2);
                    int minutes = reader.GetInt32(3);
                    string officeName = reader.GetString(4);
                    string userName = reader.GetString(5);
                    int number = reader.GetInt32(6);
                    DateTime startDate = reader.GetDateTime(7).Date;

                    // get max possible number
                    CallArea foundArea = null;
                    while (number &gt; 0)
                    {
                        if (callAreas.TryGetValue(number, out foundArea))
                        {
                            break;
                        }
                        else
                        {
                            // remove last digit
                            number = number / 10;
                        }
                    }

                    // now get the right block from the found area
                    foreach (TariffBlock tb in foundArea.Blocks)
                    {
                        if (tb.HoursEnd &gt;= hour &amp;&amp; tb.HoursStart &lt;= hour)
                        {
                            if (tb.DateEnd &gt;= startDate &amp;&amp; tb.DateStart &lt;= startDate)
                            {
                                // now get the call cost
                                Decimal cost = tb.ConnectionCharge;

                                int lastUpToX = 0;
                                foreach (TariffRate tr in tb.Rates)
                                {
                                    int upToX = tr.UpToXMinutes;
                                    if (upToX &lt; minutes)
                                    {
                                        cost += (upToX - lastUpToX) * tr.PricePerMinute;
                                    }
                                    else
                                    {
                                        cost += (minutes - lastUpToX) * tr.PricePerMinute;
                                        break;
                                    }
                                    lastUpToX = upToX;
                                }
                                // now we have the cost - so add to each dictionary
                                officeKeyBuilder.Length = 0;
                                officeKeyBuilder.Append(officeName);
                                officeKeyBuilder.Append('.');
                                officeKeyBuilder.Append(year);
                                officeKeyBuilder.Append('.');
                                officeKeyBuilder.Append(week);
                                ReportEntry entry = null;
                                string key = officeKeyBuilder.ToString();
                                if (officeReport.TryGetValue(key, out entry))
                                {
                                    entry.TotalCost += cost;
                                }
                                else
                                {
                                    entry = new ReportEntry(officeName, year, week, cost);
                                    officeReport.Add(key, entry);
                                }
                                userKeyBuilder.Length = 0;
                                userKeyBuilder.Append(userName);
                                userKeyBuilder.Append('.');
                                userKeyBuilder.Append(year);
                                userKeyBuilder.Append('.');
                                userKeyBuilder.Append(week);
                                entry = null;
                                key = userKeyBuilder.ToString();
                                if (usersReport.TryGetValue(key, out entry))
                                {
                                    entry.TotalCost += cost;
                                }
                                else
                                {
                                    entry = new ReportEntry(userName, year, week, cost);
                                    usersReport.Add(key, entry);
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }

        // Create a record object that represents an individual row, including it's metadata.
        SqlDataRecord record =
            new SqlDataRecord(new SqlMetaData[] {
                new SqlMetaData("WeekNo", SqlDbType.Int),
                new SqlMetaData("Year", SqlDbType.Int),
                new SqlMetaData("Office", SqlDbType.VarChar, 20),
                new SqlMetaData("Total", SqlDbType.Money),
            });

        SqlContext.Pipe.SendResultsStart(record);
        foreach (ReportEntry re in officeReport.Values)
        {
            // Populate the record.
            record.SetInt32(0, re.Week);
            record.SetInt32(1, re.Year);
            record.SetSqlString(2, re.Key);
            record.SetSqlMoney(3, re.TotalCost);

            SqlContext.Pipe.SendResultsRow(record);
        }
        SqlContext.Pipe.SendResultsEnd();

        // Create a record object that represents an individual row, including it's metadata.
        SqlDataRecord record2 =
            new SqlDataRecord(new SqlMetaData[] {
                new SqlMetaData("WeekNo", SqlDbType.Int),
                new SqlMetaData("Year", SqlDbType.Int),
                new SqlMetaData("UserName", SqlDbType.VarChar, 20),
                new SqlMetaData("Total", SqlDbType.Money),
            });

        SqlContext.Pipe.SendResultsStart(record2);
        foreach (ReportEntry re in usersReport.Values)
        {
            // Populate the record.
            record2.SetInt32(0, re.Week);
            record2.SetInt32(1, re.Year);
            record2.SetSqlString(2, re.Key);
            record2.SetSqlMoney(3, re.TotalCost);

            SqlContext.Pipe.SendResultsRow(record2);
        }
        SqlContext.Pipe.SendResultsEnd();
    }
}
};
more ▼

answered Dec 07, 2009 at 10:18 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

/Phil Factor 1d Now running about 5.4 secs on my machine.
start by removing the dead bodies (tables)
/ IF EXISTS ( SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#OfficeTotalByWeek%' ) DROP TABLE #OfficeTotalByWeek

IF EXISTS ( SELECT 1  FROM tempdb.information_schema.tables  WHERE table_name LIKE '#PersonTotalByWeek%' )  DROP TABLE #PersonTotalByWeek

IF EXISTS ( SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#AllTotalByWeek%' ) DROP TABLE #AllTotalByWeek

IF EXISTS ( SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#Timer%' ) DROP TABLE #Timer

IF EXISTS ( SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#TempCallLog%' ) DROP TABLE #TempCalllog

IF EXISTS ( SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#PhoneTariff%' ) DROP TABLE #PhoneTariff

IF EXISTS ( SELECT 1 FROM tempdb.information_schema.tables WHERE table_name LIKE '#phoneTariffCharges%' ) DROP TABLE #phoneTariffCharges

CREATE TABLE [dbo].#PhoneTariffCharges ( [PhoneTariffId] [int] NOT NULL, [UpToXMinutes] [int] NOT NULL, [PricePerMinute] [money] NOT NULL, [orderOfCharge] INT NOT NULL ) ALTER TABLE #phonetariffCharges ADD PRIMARY KEY (PhoneTariffId, UpToXMinutes) ;

INSERT INTO #PhoneTariffCharges SELECT PhoneTariffId, UpToXMinutes, PricePerMinute, 0 FROM PhoneTariffCharges

CREATE TABLE [dbo].[#TempCallLog] ( [CallLogId] [int] NOT NULL, [CallStart] [datetime] NOT NULL, [CallEnd] [datetime] NULL, [OfficeName] [varchar](20) NOT NULL, [UserName] [varchar](20) NOT NULL, [NumberDialled] [varchar](255) NOT NULL, phoneTariffID INT, Connectioncharge MONEY, Totalcharge MONEY, CallLengthCosted INT, CallStartday INT NOT NULL, CallStartHour INT NOT NULL, CallLengthTotal INT, [week] INT, [year] INT ) ALTER TABLE #TempCallLog ADD PRIMARY KEY (CallLogID) ;

INSERT INTO #tempCallLog SELECT *, CONVERT(INT, NULL) AS phoneTariffID, CONVERT(MONEY, 0) AS Connectioncharge, CONVERT(MONEY, 0) AS Totalcharge, CONVERT(INT, 0) AS CallLengthCosted, DATEDIFF(day, 0, CallStart) AS CallStartday, DATEPART(hour, CallStart) AS CallStartHour, CONVERT(INT, CEILING(DATEDIFF(second, CallStart, callend) /60.00)) AS CallLengthTotal, DATEPART(week, CallStart), DATEPART(year, CallStart) FROM Calllog WHERE CallStart<CallEnd

CREATE NONCLUSTERED INDEX idxNumberDialled ON [dbo].#TempCallLog (NumberDialled) INCLUDE (CallLogId, CallStartday, CallStartHour)

GO CREATE TABLE [dbo].#PhoneTariff ( [PhoneTariffId] INT NOT NULL, [CallArea] [char](6) NOT NULL, [DateStart] [datetime] NOT NULL, [DateEnd] [datetime] NOT NULL, [HoursStart] [int] NOT NULL, [HoursEnd] [int] NOT NULL, [ConnectionCharge] [money] NOT NULL, [DateStartDay] [datetime] NOT NULL, [DateEndDay] [datetime] NOT NULL ) INSERT INTO #PhoneTariff SELECT PhoneTariffId, CallArea, DateStart, DateEnd, HoursStart, HoursEnd, ConnectionCharge, DATEDIFF(day, 0, DateStart) AS DateStartDay, DATEDIFF(day, 0, DateEnd) AS DateEndDay FROM phonetariff ALTER TABLE #phonetariff ADD PRIMARY KEY (PhoneTariffId) ;

/* --this is just used for fine-tuning purposes Create table #timer ( Timer_ID int identity(1,1), [event] Varchar(30) not null, Timepoint datetime not null default Getdate() ) insert into #timer([event]) select 'Start'

*/ --apply the correct five digit area prefix UPDATE #Tempcalllog SET PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#PhoneTariff.Connectioncharge, TotalCharge=#PhoneTariff.ConnectionCharge FROM #TempcallLog n INNER JOIN #PhoneTariff ON LEFT(n.numberdialled, 5)=callArea WHERE CallStartday BETWEEN DateStartday AND DateEndday AND CallStartHour BETWEEN hoursStart AND HoursEnd

--apply the correct four digit area prefix UPDATE #Tempcalllog SET PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#PhoneTariff.Connectioncharge, TotalCharge=#PhoneTariff.ConnectionCharge FROM #TempcallLog n INNER JOIN #PhoneTariff ON CallStartday BETWEEN DateStartday AND DateEndday AND CallStartHour BETWEEN hoursStart AND HoursEnd WHERE LEFT(n.numberdialled, 4)=callArea AND n.PhoneTariffID IS NULL

--apply the correct three digit area prefix UPDATE #Tempcalllog SET PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#PhoneTariff.Connectioncharge, TotalCharge=#PhoneTariff.ConnectionCharge FROM #TempcallLog n INNER JOIN #PhoneTariff ON CallStartday BETWEEN DateStartday AND DateEndday AND CallStartHour BETWEEN hoursStart AND HoursEnd WHERE LEFT(n.numberdialled, 3)=callArea AND n.PhoneTariffID IS NULL

--apply the correct two digit area prefix UPDATE #Tempcalllog SET PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#PhoneTariff.Connectioncharge, TotalCharge=#PhoneTariff.ConnectionCharge FROM #TempcallLog n INNER JOIN #PhoneTariff ON CallStartday BETWEEN DateStartday AND DateEndday AND CallStartHour BETWEEN hoursStart AND HoursEnd WHERE LEFT(n.numberdialled, 2)=callArea AND n.PhoneTariffID IS NULL

--apply the correct one digit area prefix UPDATE #Tempcalllog SET PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#PhoneTariff.Connectioncharge, TotalCharge=#PhoneTariff.ConnectionCharge FROM #TempcallLog n INNER JOIN #PhoneTariff ON CallStartday BETWEEN DateStartday AND DateEndday AND CallStartHour BETWEEN hoursStart AND HoursEnd WHERE LEFT(n.numberdialled, 1)=callArea AND n.PhoneTariffID IS NULL

--and cost out the calls iteratively, but small numbers!

DECLARE @orderOfCharge INT SELECT @orderOfCharge=0 DECLARE @PhoneTariffID INT UPDATE #PhoneTariffCharges SET @orderOfCharge=orderOfCharge=CASE WHEN @PhoneTariffID<>PhoneTariffID THEN 1 ELSE @orderOfCharge+1 END, @PhoneTariffID=PhoneTariffID

CREATE NONCLUSTERED INDEX idxOrderOfCharge ON #PhoneTariffCharges (orderOfCharge) INCLUDE ([PhoneTariffId], [UpToXMinutes], [PricePerMinute])

DECLARE @MoreToDo INT DECLARE @ii INT SELECT @MoreToDo=1, @ii=1 WHILE @MoreToDo>0 BEGIN

    UPDATE  #Tempcalllog
    SET     CallLengthCosted=CASE WHEN CallLengthTotal&lt;UpToXMinutes
                      THEN CallLengthTotal
                      ELSE UpToXMinutes
                    END,
         TotalCharge=TotalCharge+(PricePerMinute
                         *(CASE WHEN CallLengthTotal&lt;UpToXMinutes
                             THEN CallLengthTotal
                               -CallLengthCosted
                             ELSE UpToXMinutes
                               -CallLengthCosted
                           END))
    FROM    #Tempcalllog t
         INNER JOIN #phoneTariffCharges ON #PhoneTariffcharges.phonetariffID=t.phonetariffID
    WHERE   orderOfCharge=@ii
         AND CallLengthCosted&lt;CallLengthTotal
    SELECT  @MoreToDo=@@Rowcount
    SELECT  @ii=@ii+1
END

/*Create Table #OfficeTotalByWeek ( WeekNo integer, Year integer, Office varchar(20), Total money ) */ --insert into #OfficeTotalByWeek --do the week breakdown by office SELECT week, year, OfficeName, SUM(TotalCharge) FROM #Tempcalllog GROUP BY week, year, OfficeName

/*Create Table #PersonTotalByWeek ( WeekNo integer, Year integer, Office varchar(20), Total money ) */

--insert into #PersonTotalByWeek --do the week breakdown by User SELECT week, year, UserName, SUM(TotalCharge) FROM #Tempcalllog GROUP BY week, year, UserName /* insert into #timer([event]) select 'End' Select t.[event],DateDiff (ms,l.timepoint,t.timepoint) from #timer t inner join #timer l on t.Timer_ID=l.timer_ID+1 union all Select 'Total routine ', DateDiff (ms, (select Timepoint from #timer where event like 'Start'), (select Timepoint from #timer where event like 'End')) */
more ▼

answered Dec 11, 2009 at 03:41 PM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

Nice! Two things though... 1) Add "WHERE CallStart < CallEnd" to #TempCallLog to make the table smaller and 2) Add a sixth update clause to handle CHAR(6) definition.
Dec 11, 2009 at 04:15 PM Peso
Doing 1) you can remove the "UPDATE ... > 0" clause
Dec 11, 2009 at 04:18 PM Peso
Phil, how long time does the other suggestions take on your machine?
Dec 11, 2009 at 04:19 PM Peso
For comparison, on my laptop this takes 11 seconds.
Dec 11, 2009 at 04:37 PM Peso

How odd. I just checked it again. five and a half seconds. I had tried putting in more indexes (even followed SSMS's suggestions) and everything I did added another second to the results. Can you see what is slowing it down?

Why do I need to add a sixth update clause to handle CHAR(6) definition when there aren't any six-character codes in the phoneTariff table?
Dec 11, 2009 at 06:58 PM Phil Factor
(comments are locked)
10|1200 characters needed characters left

Peso 4B - 20091216
Same setup and teardown as version 4a.

Main Code

CREATE TABLE #Logs  (  CallLogId INT,  CallDuration INT,  PhoneTariffId INT,  ConnectionCharge MONEY  )

;WITH cteCallArea(Prefix, CallArea) AS ( SELECT u.Prefix, MAX(u.CallArea) AS CallArea FROM ( SELECT d.Prefix, d.Prefix AS Prefix6, CAST(LEFT(d.Prefix, 5) AS CHAR(6)) AS Prefix5, CAST(LEFT(d.Prefix, 4) AS CHAR(6)) AS Prefix4, CAST(LEFT(d.Prefix, 3) AS CHAR(6)) AS Prefix3, CAST(LEFT(d.Prefix, 2) AS CHAR(6)) AS Prefix2, CAST(LEFT(d.Prefix, 1) AS CHAR(6)) AS Prefix1 FROM ( SELECT CAST(NumberDialled AS CHAR(6)) AS Prefix FROM dbo.CallLog WITH (NOLOCK) WHERE CallStart < CallEnd GROUP BY CAST(NumberDialled AS CHAR(6)) ) AS d ) AS l UNPIVOT ( CallArea FOR thePrefix IN (l.Prefix6, l.Prefix5, l.Prefix4, l.Prefix3, l.Prefix2, l.Prefix1) ) AS u INNER JOIN ( SELECT CallArea FROM dbo.PhoneTariff WITH (NOLOCK) GROUP BY CallArea ) AS pt ON pt.CallArea = u.CallArea GROUP BY u.Prefix ) INSERT #Logs ( CallLogId, CallDuration, PhoneTariffId, ConnectionCharge ) SELECT cl.CallLogId, cl.CallDuration, pt.PhoneTariffId, pt.ConnectionCharge FROM ( SELECT CallLogId, CEILING(DATEDIFF(SECOND, CallStart, CallEnd) / 60.0) AS CallDuration, DATEDIFF(DAY, 0, CallStart) AS CallDate, DATEPART(HOUR, CallStart) AS CallHour, CONVERT(CHAR(6), NumberDialled) AS Prefix FROM dbo.CallLog WITH (NOLOCK) WHERE CallStart < CallEnd ) AS cl INNER JOIN cteCallArea AS ca ON ca.Prefix = cl.Prefix INNER JOIN dbo.PhoneTariff AS pt WITH (NOLOCK) ON pt.CallArea = ca.CallArea WHERE cl.CallDate BETWEEN pt.DateStart AND pt.DateEnd AND cl.CallHour BETWEEN pt.HoursStart AND pt.HoursEnd

CREATE TABLE #Charges ( LineID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, PhoneTariffId INT, FromMinute INT, ToMinute INT, PricePerMinute MONEY )

INSERT #Charges ( PhoneTariffId, FromMinute, ToMinute, PricePerMinute ) SELECT ptc.PhoneTariffId, 0 AS FromMinute, ptc.UpToXMinutes AS ToMinute, ptc.PricePerMinute FROM dbo.PhoneTariffCharges AS ptc WITH (NOLOCK) INNER JOIN ( SELECT PhoneTariffId, MAX(CallDuration) AS CallDuration FROM #Logs GROUP BY PhoneTariffId ) AS l ON l.PhoneTariffId = ptc.PhoneTariffId CROSS APPLY ( SELECT TOP(1) x.UpToXMinutes FROM dbo.PhoneTariffCharges AS x WHERE x.PhoneTariffId = l.PhoneTariffId AND x.UpToXMinutes >= l.CallDuration ORDER BY x.UpToXMinutes ) AS f(UpToXMinutes) WHERE ptc.UpToXMinutes <= f.UpToXMinutes ORDER BY ptc.PhoneTariffId, ptc.UpToXMinutes

UPDATE c SET c.FromMinute = x.ToMinute FROM #Charges AS c INNER JOIN #Charges AS x ON x.LineID = c.LineID - 1 WHERE c.PhoneTariffId = x.PhoneTariffId

SELECT cl.OfficeName, DATEPART(YEAR, cl.CallStart) AS CallYear, DATEPART(WEEK, cl.CallStart) AS CallWeek, SUM(l.ConnectionCharge + s.DurationCharge) AS Charge FROM #Logs AS l INNER JOIN dbo.CallLog AS cl WITH (NOLOCK) ON cl.CallLogId = l.CallLogId CROSS APPLY ( SELECT SUM(d.Amount) FROM ( SELECT CASE WHEN l.CallDuration >= c.ToMinute THEN c.ToMinute - c.FromMinute WHEN l.CallDuration BETWEEN c.FromMinute AND c.ToMinute THEN l.CallDuration - c.FromMinute ELSE 0 END * c.PricePerMinute AS Amount FROM #Charges AS c WHERE c.PhoneTariffId = l.PhoneTariffId ) AS d ) AS s(DurationCharge) GROUP BY cl.OfficeName, DATEPART(YEAR, cl.CallStart), DATEPART(WEEK, cl.CallStart)

SELECT cl.UserName, DATEPART(YEAR, cl.CallStart) AS CallYear, DATEPART(WEEK, cl.CallStart) AS CallWeek, SUM(l.ConnectionCharge + s.DurationCharge) AS Charge FROM #Logs AS l INNER JOIN dbo.CallLog AS cl WITH (NOLOCK) ON cl.CallLogId = l.CallLogId CROSS APPLY ( SELECT SUM(d.Amount) FROM ( SELECT CASE WHEN l.CallDuration >= c.ToMinute THEN c.ToMinute - c.FromMinute WHEN l.CallDuration BETWEEN c.FromMinute AND c.ToMinute THEN l.CallDuration - c.FromMinute ELSE 0 END * c.PricePerMinute AS Amount FROM #Charges AS c WHERE c.PhoneTariffId = l.PhoneTariffId ) AS d ) AS s(DurationCharge) GROUP BY cl.UserName, DATEPART(YEAR, cl.CallStart), DATEPART(WEEK, cl.CallStart)

DROP TABLE #Logs, #Charges
more ▼

answered Dec 16, 2009 at 08:52 AM

Peso gravatar image

Peso
1.6k 5 6 8

Definitely faster, 3.941 seconds on my box - but only returns 53,703 rows in the second result set?
Dec 16, 2009 at 10:53 AM Matt Whitfield ♦♦
...can I ask, too... how long did developing this one take? Just interested from a general cost/benefit perspective...
Dec 16, 2009 at 10:54 AM Matt Whitfield ♦♦
Forgot a CROSS APPLY. This one took about 30 minutes to develop and another 15 minutes looking at the execution plan. However, I did miss a cross apply so debugging the code above took another hour. 2 hour total.
Dec 16, 2009 at 12:10 PM Peso
Cool, correct results now, 5.317 seconds... It would have taken me a lot longer to develop the SQL the way you have I think. Probably more in the region of 5-6 hours...
Dec 16, 2009 at 12:31 PM Matt Whitfield ♦♦
This runs in 3 secs on my machine. Mine runs in 5.4.
Dec 16, 2009 at 06:46 PM Phil Factor
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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: 7948 times

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