The ‘Call Log’ SQL Problem

Ok, here's my first go (now revised to align with question change - week from startdate):

Setup:

/****************************************************
 * Phone Report - MattW v1 (revised)                *
 ****************************************************/

CREATE INDEX [IX_PhoneTariff_1] ON [dbo].[PhoneTariff] ([CallArea])
CREATE INDEX [IX_PhoneTariff_2] ON [dbo].[PhoneTariff] ([DateStart], [DateEnd])
CREATE INDEX [IX_CallLog_1] ON [dbo].[CallLog] ([OfficeName])
CREATE INDEX [IX_CallLog_2] ON [dbo].[CallLog] ([UserName])
GO

/****************************************************
 * Phone Report - MattW v1 (revised)                *
 ****************************************************/

/****************************************************
 * Create procedure                                 *
 ****************************************************/
CREATE PROCEDURE stproc_PhoneReport
AS
/****************************************************
 * Create needed tables and clustered indexes       *
 ****************************************************/
CREATE TABLE #CallAreas (
  CallAreaID   SMALLINT IDENTITY (1, 1),
  Prefix       INT     ,
  PrefixLength TINYINT 
);

CREATE CLUSTERED INDEX IX_CallAreas_1
  ON #CallAreas([PrefixLength], [Prefix]);

CREATE TABLE #PhoneTariff (
  [PhoneTariffId]    INT      NOT NULL,
  [CallAreaID]       SMALLINT NOT NULL,
  [ResultDate]       DATE     NOT NULL,
  [ResultHour]       TINYINT  NOT NULL,
  [ConnectionCharge] MONEY    NULL
);

CREATE CLUSTERED INDEX [IX_PhoneTariff_1]
  ON #PhoneTariff([CallAreaID], [ResultDate], [ResultHour]);

CREATE TABLE [#PhoneTariffCharges] (
  [PhoneTariffId]           INT   NULL,
  [MinMinutes]              INT   NULL,
  [MaxMinutes]              INT   NULL,
  [PricePerMinute]          MONEY NULL,
  [BandTotalPrice]          MONEY NULL,
  [PriceForPreviousMinutes] MONEY NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [IX_PhoneTariffCharges_1]
  ON #PhoneTariffCharges([PhoneTariffId], [MaxMinutes]);

CREATE TABLE Offices (
  ID     INT          IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
  [name] VARCHAR (20) NOT NULL
);

CREATE TABLE Users (
  ID     INT          IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
  [name] VARCHAR (20) NOT NULL
);

CREATE TABLE #CallLogResolved (
  StartDate       DATE    ,
  StartHour       TINYINT ,
  MinutesDuration INT     ,
  OfficeID        INT     ,
  UserID          INT     ,
  CallAreaID      SMALLINT
);

CREATE TABLE #CallLogTariffResolved (
  [Year]   INT    ,
  Week     TINYINT,
  OfficeID INT    ,
  UserID   INT    ,
  [Cost]   MONEY  
);

/****************************************************
 * Create table of call areas                       *
 ****************************************************/
INSERT INTO [#CallAreas] ([Prefix], [PrefixLength])
SELECT   [pt].[CallArea],
         LEN([pt].[CallArea])
FROM     [dbo].[PhoneTariff] AS [pt]
GROUP BY [CallArea];

CREATE INDEX IX_CallAreas_2
  ON #CallAreas([Prefix]);
/****************************************************
 * Extrapolate PhoneTariff                          *
 ****************************************************/
DECLARE @minDate AS DATE, @maxDate AS DATE;

SELECT @minDate = MIN([DateStart]),
       @maxDate = MAX([DateEnd])
FROM   [dbo].[PhoneTariff];

WITH   Dates (ResultDate)
AS     (SELECT ResultDate
        FROM   (SELECT DATEADD(d, ROW_NUMBER() OVER ( ORDER BY [object_id]) - 1, @minDate) AS ResultDate
                FROM   [sys].[all_parameters]) AS idat
        WHERE  [idat].[ResultDate] <= @maxDate),
       Hours (ResultHour)
AS     (SELECT ResultHour
        FROM   (SELECT ROW_NUMBER() OVER ( ORDER BY [object_id]) - 1 AS ResultHour
                FROM   [sys].[all_parameters]) AS idat
        WHERE  [idat].[ResultHour] <= 23)
INSERT INTO [#PhoneTariff] ([PhoneTariffId], [CallAreaID], [ResultDate], [ResultHour], [ConnectionCharge])
SELECT [PhoneTariffId],
       [ca].[CallAreaID],
       [ResultDate],
       [ResultHour],
       [ConnectionCharge]
FROM   [dbo].[PhoneTariff] AS [pt]
       INNER JOIN
       [Dates]
       ON [Dates].[ResultDate] BETWEEN [pt].[DateStart] AND [pt].[DateEnd]
       INNER JOIN
       [Hours]
       ON [Hours].[ResultHour] BETWEEN [pt].[HoursStart] AND [pt].[HoursEnd]
       INNER JOIN
       #CallAreas AS [ca]
       ON [ca].[Prefix] = [pt].[CallArea];

/****************************************************
 * Prepare a much simpler lookup for tariff charges *
 ****************************************************/
INSERT INTO [#PhoneTariffCharges] ([PhoneTariffId], [MaxMinutes], [PricePerMinute])
SELECT [PhoneTariffId],
       [UpToXMinutes],
       [PricePerMinute]
FROM   [dbo].[PhoneTariffCharges];

DECLARE @PrevTariffID AS INT, 
        @PrevMaxMinutes AS INT, 
        @PrevMinMinutes AS INT, 
        @PreviousMinutesPrice AS MONEY,
        @PreviousPricePerMinute AS MONEY,
        @LastPreviousPricePerMinute AS MONEY;

SET @PrevMaxMinutes = -1;
SET @PrevTariffID = -1;
SET @PreviousMinutesPrice = 0;
SET @PreviousPricePerMinute = 0;

UPDATE #PhoneTariffCharges
SET    @PrevMinMinutes             = [MinMinutes]              = CASE WHEN @PrevTariffID != [PhoneTariffId] THEN 1 ELSE @PrevMaxMinutes + 1 END,
       @LastPreviousPricePerMinute = [PriceForPreviousMinutes] = CASE WHEN @PrevTariffID != [PhoneTariffId] THEN 0 ELSE @PreviousMinutesPrice END,
       @PreviousMinutesPrice       = BandTotalPrice            = CASE WHEN @PrevTariffID != [PhoneTariffId] THEN ([MaxMinutes] * [PricePerMinute]) ELSE @PreviousMinutesPrice + (([MaxMinutes] - @PrevMinMinutes + 1) * [PricePerMinute]) END,
       @PreviousPricePerMinute                                 = [PricePerMinute],
       @PrevMaxMinutes                                         = [MaxMinutes],
       @PrevTariffID                                           = PhoneTariffId
FROM   #PhoneTariffCharges AS [ptc] WITH (INDEX (0), TABLOCKX)
OPTION (MAXDOP 1);

/****************************************************
 * Create the offices and users lookups             *
 ****************************************************/
INSERT INTO Offices ([Name])
SELECT   [cl].[OfficeName]
FROM     [dbo].[CallLog] AS [cl]
GROUP BY [cl].[OfficeName];

CREATE UNIQUE INDEX [IX_Offices_1]
  ON Offices([name]);

INSERT INTO Users ([Name])
SELECT   [cl].[UserName]
FROM     [dbo].[CallLog] AS [cl]
GROUP BY [cl].[UserName];

CREATE UNIQUE INDEX [IX_Users_1]
  ON Users([name]);

/********************************************************************
 * Resolve the duration, dates, call areas, user IDs and office IDs *
 ********************************************************************/
WITH   CallLogIntermediate ([StartDate], [StartHour], [MinutesDuration], [OfficeID], [UserID], [1DigitPrefix], [2DigitPrefix], [3DigitPrefix], [4DigitPrefix], [5DigitPrefix], [6DigitPrefix])
AS     (SELECT DATEADD(d, DATEDIFF(d, 0, [cl].[CallStart]), 0),
               DATEPART(hh, [cl].[CallStart]),
               (DATEDIFF(s, [cl].[CallStart], [cl].[CallEnd]) + 59) / 60,
               o.ID,
               u.ID,
               CONVERT (INT, LEFT([NumberDialled], 1)),
               CONVERT (INT, LEFT([NumberDialled], 2)),
               CONVERT (INT, LEFT([NumberDialled], 3)),
               CONVERT (INT, LEFT([NumberDialled], 4)),
               CONVERT (INT, LEFT([NumberDialled], 5)),
               CONVERT (INT, LEFT([NumberDialled], 6))
        FROM   [dbo].[CallLog] AS [cl]
               INNER JOIN
               Offices AS [o]
               ON [o].[name] = [OfficeName]
               INNER JOIN
               Users AS [u]
               ON [u].[name] = [UserName]
        WHERE  [CallStart] != [CallEnd]),
       CallLogResolved ([StartDate], [StartHour], [MinutesDuration], [OfficeID], [UserID], [CallAreaID])
AS     (SELECT [StartDate],
               [StartHour],
               [MinutesDuration],
               [OfficeID],
               [UserID],
               COALESCE ([ca6].[CallAreaID], [ca5].[CallAreaID], [ca4].[CallAreaID], [ca3].[CallAreaID], [ca2].[CallAreaID], [ca1].[CallAreaID]) AS [CallAreaID]
        FROM   CallLogIntermediate AS [cl]
               LEFT OUTER JOIN
               #CallAreas AS [ca1]
               ON [ca1].[Prefix] = [cl].[1DigitPrefix]
                  AND [ca1].[PrefixLength] = 1
               LEFT OUTER JOIN
               #CallAreas AS [ca2]
               ON [ca2].[Prefix] = [cl].[2DigitPrefix]
                  AND [ca2].[PrefixLength] = 2
               LEFT OUTER JOIN
               #CallAreas AS [ca3]
               ON [ca3].[Prefix] = [cl].[3DigitPrefix]
                  AND [ca3].[PrefixLength] = 3
               LEFT OUTER JOIN
               #CallAreas AS [ca4]
               ON [ca4].[Prefix] = [cl].[4DigitPrefix]
                  AND [ca4].[PrefixLength] = 4
               LEFT OUTER JOIN
               #CallAreas AS [ca5]
               ON [ca5].[Prefix] = [cl].[5DigitPrefix]
                  AND [ca5].[PrefixLength] = 5
               LEFT OUTER JOIN
               #CallAreas AS [ca6]
               ON [ca6].[Prefix] = [cl].[6DigitPrefix]
                  AND [ca6].[PrefixLength] = 6)
INSERT INTO [#CallLogResolved] ([StartDate], [StartHour], [MinutesDuration], [OfficeID], [UserID], [CallAreaID])
SELECT [StartDate],
       [StartHour],
       [MinutesDuration],
       [OfficeID],
       [UserID],
       [CallAreaID]
FROM   [CallLogResolved] AS [clr];

/********************************************************************
 * Resolve the prices for each call, with week and year numbers     *
 ********************************************************************/
INSERT INTO [#CallLogTariffResolved] ([Year], [Week], [OfficeID], [UserID], [Cost])
SELECT YEAR([StartDate]),
       DATEPART(week, [StartDate]),
       [OfficeID],
       [UserID],
       [PriceForPreviousMinutes] + ((([MinutesDuration] - [MinMinutes]) + 1) * [PricePerMinute]) + [ConnectionCharge]
FROM   #CallLogResolved AS [clr]
       INNER JOIN
       #PhoneTariff AS [pt]
       ON [pt].[CallAreaID] = [clr].[CallAreaID]
          AND [pt].[ResultDate] = [clr].[StartDate]
          AND [pt].[ResultHour] = [clr].[StartHour]
       INNER JOIN
       #PhoneTariffCharges AS [ptc]
       ON [pt].[PhoneTariffID] = [ptc].[PhoneTariffId]
          AND [clr].[MinutesDuration] BETWEEN [ptc].[MinMinutes] AND [ptc].[MaxMinutes];

/********************************************************************
 * Output the office report                                         *
 ********************************************************************/
SELECT   CONVERT (INT, [cltr].[Week]) AS [WeekNo],
         [cltr].[Year],
         o.[Name] AS [Office],
         SUM([cltr].[Cost]) AS [Total]
FROM     #CallLogTariffResolved AS [cltr]
         INNER JOIN
         Offices AS o
         ON o.ID = [cltr].[OfficeID]
GROUP BY [cltr].[Year], [cltr].[Week], o.[Name];

/********************************************************************
 * Output the user report                                           *
 ********************************************************************/
SELECT   CONVERT (INT, [cltr].[Week]) AS [WeekNo],
         [cltr].[Year],
         u.[Name] AS [UserName],
         SUM([cltr].[Cost]) AS [Total]
FROM     #CallLogTariffResolved AS [cltr]
         INNER JOIN
         Users AS u
         ON u.ID = [cltr].[UserID]
GROUP BY [cltr].[Year], [cltr].[Week], u.[Name];

/********************************************************************
 * Keep it tidy                                                     *
 ********************************************************************/
DROP TABLE Offices;
DROP TABLE Users;
DROP TABLE #PhoneTariffCharges;
DROP TABLE #CallAreas;
DROP TABLE #PhoneTariff;
DROP TABLE #CallLogResolved;
DROP TABLE #CallLogTariffResolved;
GO

Run:

EXEC stproc_PhoneReport

Teardown:

DROP PROCEDURE stproc_PhoneReport
DROP INDEX [IX_PhoneTariff_1] ON [dbo].[PhoneTariff]
DROP INDEX [IX_PhoneTariff_2] ON [dbo].[PhoneTariff]
DROP INDEX [IX_CallLog_1] ON [dbo].[CallLog]
DROP INDEX [IX_CallLog_2] ON [dbo].[CallLog]

This runs in between 10 and 15 seconds on my box, as compared to 35 minutes 25 secs for the Robert Bar (bless him) solution.

If stored procs aren't allowed, let me know and I'll change it to bare definition, it doesn't make a massive difference to the timings anyway...

Here's my second try. (lmu92 1b 20091216) Based on my previous version with the following changes: a) I don't create the result tables anymore, just printing the result sets with the SELECT statement (seems common practice throughout the solutions provided so far) b) replaced a UNION with a faster solution c) Added NOLOCK hints d) Building the intermediate table one step later, saving one update

prepare base tables (create index)

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 block

SET nocount ON

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

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

/* 
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]

;
WITH ctePhoneTariffArea AS
(
    SELECT CallArea AS CallArea
    FROM dbo.PhoneTariff WITH (nolock)
    GROUP BY CallArea
),
cteCallAreaLogID as
(
    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 WITH (nolock)
        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) 
)

INSERT INTO #callAreaLogID (
    [callLogID],
    [callArea],
    [weekNo],
    [Year],
    [CallStartDay], 
    [CallStartHour],
    [CallDuration],
    [ConnectionCharge],
    [PhoneTariffID])
SELECT
    c.[callLogID],
    c.[callArea],
    c.[weekNo],
    c.[Year],
    c.[CallStartDay], 
    c.[CallStartHour],
    c.[CallDuration],
    t.connectionCharge,
    t.PhoneTariffID
FROM cteCallAreaLogID c 
INNER JOIN dbo.PhoneTariff t WITH (nolock)
    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

;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, 
        ISNULL(cte2.uptoxminutes,0) AS Rangestart,
        cte1.UpToXMinutes,
        cte1.PricePerMinute,
        ISNULL(cte1.UpToXMinutes-cte2.UpToXMinutes,cte1.uptoxminutes ) AS MinutesToCharge 
    FROM cte cte1 
        LEFT OUTER JOIN cte cte2
            ON cte1.PhoneTariffId = cte2.PhoneTariffId
            AND cte1.row = cte2.row + 1
),
MaxMinutePerCall AS 
(
    SELECT 
        t.[callLogID],
        t.[CallDuration] AS [CallDuration],
        c.PhoneTariffId,
        min(UpToXMinutes) AS maxMinutes 
    FROM #callAreaLogID t 
        INNER JOIN PhoneTariffCharges c WITH (nolock)
        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

SELECT  
    [weekNo], 
    [year], 
    officename AS Office,
    sum(connectioncharge + callcharge) AS Total
FROM dbo.CallLog c WITH (nolock)
    INNER JOIN  #callAreaLogID a
    ON c.CallLogId = a.CallLogId
GROUP BY [weekNo], [year], officename

SELECT  
    [weekNo], 
    [year], 
    username AS UserName, 
    sum(connectioncharge+callcharge) AS Total
FROM dbo.CallLog c WITH (nolock)
    INNER JOIN  #callAreaLogID a
    ON c.CallLogId = a.CallLogId
GROUP BY [weekNo], [year], username

/*
cleanup internal tables
*/
DROP TABLE #callAreaLogID

cleanup index

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]

On my machine the code runs in the range of Pesos version 4a. It's still not as fast as Matts CLR though (at least on my machine)...

I opted to go no hardwiring at all, because you never know when CallArea changes and suddenly has 6 digits, or more...

First, the Setup part

CREATE
CLUSTERED INDEX CX_PhoneTariff
ON      dbo.PhoneTariff
        (
            CallArea ASC
        )

CREATE
CLUSTERED INDEX CX_PhoneTariffCharges
ON      dbo.PhoneTariffCharges
        (
            PhoneTariffId ASC,
            UpToXMinutes ASC
        )

And then the Teardown part

DROP INDEX CX_PhoneTariff ON dbo.PhoneTariff
DROP INDEX CX_PhoneTariffCharges ON dbo.PhoneTariffCharges

And finally the first version of the query

/*
    Peso 1a, 20091205
*/
DECLARE @PrefixLength INT = (SELECT MAX(LEN(CallArea)) FROM dbo.PhoneTariff)

SELECT      cl.CallLogId,
        v.Number AS PrefixLength,
        cl.OfficeName,
        cl.UserName,
        DATEDIFF(DAY, 0, cl.CallStart) AS CallDate,
        DATEPART(HOUR, cl.CallStart) AS CallHour,
        LEFT(cl.NumberDialled, v.Number) AS Prefix,
        CONVERT(INT, CEILING(DATEDIFF(MILLISECOND, cl.CallStart, cl.CallEnd) / 60000.0)) AS CallMinutes
INTO        #Log
FROM        dbo.CallLog AS cl WITH (NOLOCK)
INNER JOIN  master..spt_values AS v ON v.Type = 'P'
            AND v.Number BETWEEN 1 AND @PrefixLength
WHERE       CONVERT(INT, CEILING(DATEDIFF(MILLISECOND, cl.CallStart, cl.CallEnd) / 60000.0)) > 0
        AND EXISTS (SELECT * FROM dbo.PhoneTariff AS pt WHERE pt.CallArea = LEFT(cl.NumberDialled, v.Number))
        --AND cl.OfficeName = 'Marketing'
        --AND cl.UserName = 'Yolanda Haley'

SELECT  pt.PhoneTariffId,
    DATEDIFF(DAY, 0, pt.DateStart) AS DateStart,
    DATEDIFF(DAY, 0, pt.DateEnd) AS DateEnd,
    pt.HoursStart,
    pt.HoursEnd,
    pt.ConnectionCharge,
    pt.CallArea
INTO    #Tariff
FROM    dbo.PhoneTariff AS pt WITH (NOLOCK)
WHERE   EXISTS(SELECT * FROM #Log AS l WHERE l.Prefix = pt.CallArea AND l.CallHour BETWEEN pt.HoursStart AND pt.HoursEnd)

;WITH Yak(OfficeName, UserName, CallYear, CallWeek, PhoneTariffId, ConnectionCharge, CallMinutes, recID)
AS (
    SELECT      l.OfficeName,
            l.UserName,
            DATEPART(YEAR, l.CallDate) AS CallYear,
            DATEPART(WEEK, l.CallDate) AS CallWeek,
            t.PhoneTariffId,
            t.ConnectionCharge,
            l.CallMinutes,
            ROW_NUMBER() OVER (PARTITION BY l.CallLogId ORDER BY l.PrefixLength DESC) AS recID
    FROM        #Log AS l
    INNER JOIN  #Tariff AS t ON t.CallArea = l.Prefix
    WHERE       l.CallDate BETWEEN t.DateStart AND t.DateEnd
            AND l.CallHour BETWEEN t.HoursStart AND t.HoursEnd
)
SELECT  OfficeName,
    UserName,
    CallYear,
    CallWeek,
    PhoneTariffId,
    ConnectionCharge,
    CallMinutes
INTO    #Calls
FROM    Yak
WHERE   recID = 1

DROP TABLE  #Log,
        #Tariff

SELECT      ptc.PhoneTariffId,
        IDENTITY(INT, 1, 1) AS RowID,
        0 AS FromMinutes,
        ptc.UpToXMinutes AS ToMinutes,
        ptc.PricePerMinute
INTO        #Charges
FROM        dbo.PhoneTariffCharges AS ptc WITH (NOLOCK)
WHERE       EXISTS (SELECT * FROM #Calls AS c WHERE c.PhoneTariffID = ptc.PhoneTariffID)
ORDER BY    ptc.PhoneTariffID,
        ptc.UpToXMinutes

UPDATE      c
SET     c.FromMinutes = w.ToMinutes
FROM        #Charges AS c
INNER JOIN  #Charges AS w ON w.RowID = c.RowID - 1
            AND w.PhoneTariffID = c.PhoneTariffID
WHERE       c.PhoneTariffID = w.PhoneTariffID

;WITH Yak(OfficeName, UserName, CallYear, CallWeek, Total)
AS (
    SELECT      c.OfficeName,
            c.UserName,
            c.CallYear,
            c.CallWeek,
            SUM(CASE WHEN h.FromMinutes = 0 THEN c.ConnectionCharge ELSE 0 END
                +   CASE
                        WHEN c.CallMinutes >= h.ToMinutes THEN h.ToMinutes - h.FromMinutes
                        WHEN c.CallMinutes >= h.FromMinutes THEN c.CallMinutes - h.FromMinutes
                        ELSE 0
                    END * h.PricePerMinute) AS Total
    FROM        #Calls AS c
    INNER JOIN  #Charges AS h ON h.PhoneTariffId = c.PhoneTariffId
    GROUP BY    c.OfficeName,
            c.UserName,
            c.CallYear,
            c.CallWeek
)
SELECT  OfficeName,
    UserName,
    CallYear,
    CallWeek,
    Total
INTO    #Output
FROM    Yak

DROP TABLE  #Calls,
        #Charges

SELECT      OfficeName,
        CallYear,
        CallWeek,
        SUM(Total) AS Total
FROM        #Output
GROUP BY    OfficeName,
        CallYear,
        CallWeek

SELECT      UserName,
        CallYear,
        CallWeek,
        SUM(Total) AS Total
FROM        #Output
GROUP BY    UserName,
        CallYear,
        CallWeek

DROP TABLE  #Output

Phil Factor 1b (1a got mangled by the website because it has < in the code)

This is my first entry, just to show a fairly conventional way of doing it. The call table is redone in a slightly more compute-friendly form. The same is done for the PhoneTariff table. Then the international call prefixes are identified by updating the table progressively, starting with the longest codes first and, at the same time, the PhoneTarrif and the initial connection charge is identified.

Then, the calls are costed out, a charge-band at a time until the longest calls have been costed out.

Once this has been done, then it is a simple matter of aggregating the reports.

I've left my timing harness in place in case you want to tweak the solution, or if you want to see how I generally do it.

On my server, I get times in the 7.5 sec range, for the whole operation. Last time I checked the result was the same as Robert Barr's (bless him)

--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 '#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

--this is just used for fine-tuning purposes
Create table #timer
(
Timer_ID int identity(1,1),
[event] Varchar(20) not null,
Timepoint datetime not null default Getdate()
)
insert into  #timer([event]) select 'Start'
--create a temporary table to do the costings
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
INTO    #TempCallLog
FROM    Calllog

SELECT  *,
    DATEDIFF(day, 0, DateStart) AS DateStartDay,
    DATEDIFF(day, 0, DateEnd) AS DateEndDay
INTO    #phonetariff
FROM    phonetariff
insert into  #timer([event]) select 'Created temp tables'
--this is where we create the indexes. 
CREATE NONCLUSTERED INDEX idxHoursAndDates
ON #TempCallLog ([CallStartDay],[CallStartHour])

CREATE NONCLUSTERED INDEX idxCallHoursAndDates
ON #phonetariff ([DateStartDay],[DateEndDay],[HoursStart],[HoursEnd],[phoneTariffID])

CREATE NONCLUSTERED INDEX idxPhoneTatrrifID 
ON #TempcallLog([phoneTariffID],[NumberDialled],[Connectioncharge],[CallStartday],    [CallStartHour])
insert into  #timer([event]) select 'Created indexes'
--apply the correct five digit area prefix
Update #Tempcalllog set PhoneTariffID=#PhoneTariff.PhoneTariffID,     Connectioncharge=#PhoneTariff.Connectioncharge
from #TempcallLog n inner join #PhoneTariff on CallStartday between DateStartday and DateEndday 
and CallStartHour between hoursStart and HoursEnd
where left(n.numberdialled,5) = callArea and n.PhoneTariffID is null
insert into  #timer([event]) select 'found 5 digit codes'
--apply the correct four digit area prefix
Update #Tempcalllog set PhoneTariffID=#PhoneTariff.PhoneTariffID,     Connectioncharge=#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
insert into  #timer([event]) select 'found 4 digit codes'
--apply the correct three digit area prefix
Update #Tempcalllog set PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#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
insert into  #timer([event]) select 'found 3 digit codes'
--apply the correct two digit area prefix
Update #Tempcalllog set PhoneTariffID=#PhoneTariff.PhoneTariffID, Connectioncharge=#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
insert into  #timer([event]) select 'found 2 digit codes'
--apply the correct one digit area prefix
Update #Tempcalllog set PhoneTariffID=#PhoneTariff.PhoneTariffID,     Connectioncharge=#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
insert into  #timer([event]) select 'found 1 digit codes'

--put in the initial connectioncharge
Update #Tempcalllog set TotalCharge=ConnectionCharge
where CallLengthTotal>0
insert into  #timer([event]) select 'connection charge'

--and cost out the calls iteratively, but small numbers!
DECLARE @MoreToDo INT
SELECT  @MoreToDo=1 
WHILE @MoreToDo>0
BEGIN

UPDATE  #Tempcalllog
    SET     CallLengthCosted=CASE WHEN CallLengthTotal<NextBand
                                  THEN CallLengthTotal
                                  ELSE NextBand
                             END,
            TotalCharge=TotalCharge+(PricePerMinute
                                     *(CASE WHEN CallLengthTotal<NextBand
                                            THEN CallLengthTotal-CallLengthCosted
                                            ELSE NextBand-CallLengthCosted
                                       END))

FROM    #Tempcalllog t
            INNER JOIN (SELECT  CallLogID,
                                MIN(#Tempcalllog.PhoneTariffID) AS PhoneTariffID,
                                MIN(UptoXMinutes) NextBand
                        FROM    #Tempcalllog
                                INNER JOIN PhoneTariffcharges ON PhoneTariffcharges.phonetariffID=#Tempcalllog.phonetariffID
                        WHERE   CallLengthCosted<CallLengthTotal
                                AND UpToXMinutes>CallLengthCosted
                        GROUP BY CallLogID
                       ) ToDo ON todo.CallLogID=t.CallLogID
            INNER JOIN PhoneTariffcharges ON todo.phoneTariffID=PhoneTariffcharges.phoneTariffID
                                             AND     NextBand=PhoneTariffcharges.UptoXMinutes
    SELECT  @MoreToDo=@@Rowcount
    insert into  #timer([event]) select 'Did band '
END

Create Table #OfficeTotalByWeek
(
WeekNo   integer,
Year     integer,
Office   varchar(20),
Total    money
)

insert into #OfficeTotalByWeek
--do the week breakdown by office
SELECT  DATEPART(week, CallStart),
    DATEPART(year, CallStart),
    OfficeName,
    SUM(TotalCharge)
FROM    #Tempcalllog
GROUP BY DATEPART(week, CallStart),
    DATEPART(year, CallStart),
    OfficeName
ORDER BY DATEPART(week, CallStart),
    DATEPART(year, CallStart),
    OfficeName

Create Table #PersonTotalByWeek
(
WeekNo   integer,
Year     integer,
Office   varchar(20),
Total    money
)
insert into  #timer([event]) select 'office grouping '

insert into #PersonTotalByWeek
--do the week breakdown by User
SELECT  DATEPART(week, CallStart),
    DATEPART(year, CallStart),
    UserName,
    SUM(TotalCharge)
FROM    #Tempcalllog
GROUP BY DATEPART(week, CallStart),
    DATEPART(year, CallStart),
    UserName
ORDER BY DATEPART(week, CallStart),
    DATEPART(year, CallStart),
    UserName
insert into  #timer([event]) select 'person grouping '
--SELECT  DATEDIFF(ms, @Timer, GETDATE()) AS [Total time (MS)]

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'))

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] ([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])

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]

Ok, here's matt version 2 - and you knew it was coming - CLR version

Setup is:

CREATE CLUSTERED INDEX [IX_PhoneTariff_1]
ON [dbo].[PhoneTariff](
  [CallArea] ASC, [DateStart] ASC, [HoursStart] ASC
)
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = ON)
ON [PRIMARY];
GO
CREATE CLUSTERED INDEX [IX_PhoneTariffCharges_1]
ON [dbo].[PhoneTariffCharges](
  [PhoneTariffId] ASC, [UpToXMinutes] ASC
)
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = ON)
ON [PRIMARY];
GO
CREATE ASSEMBLY [PhilFactorChallenge3]
AUTHORIZATION [dbo]
FROM
0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300A8A41D4B0000000000000000E00002210B010800001E00000006000000000000BE3D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000643D000057000000004000009003000000000000000000000000000000000000006000000C000000C83C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C41D000000200000001E000000020000000000000000000000000000200000602E7273726300000090030000004000000004000000200000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002400000000000000000000000000004000004200000000000000000000000000000000A03D0000000000004800000002000500F0280000D813000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007A021F10730E00000A7D0200000402280F00000A000002037D01000004002A9E02280F00000A000002037D0300000402047D0400000402057D05000004020E047D06000004002AF2021F10731000000A7D0C00000402280F00000A000002037D0700000402047D0800000402057D09000004020E047D0A000004020E057D0B000004002A6202280F00000A000002037D0D00000402047D0E000004002A0000001B3006000F07000001000011007201000070731200000A0A00066F1300000A0020C8000000731400000A0B160C723100007006731500000A0D00096F1600000A13040020FFC99A3B130515130614130714130814130914130A38E5000000001104166F1700000A130B110B1106FE01132B112B2D3D00110B1306110B08FE0216FE01132B112B2D0500110B0C00110B7301000006130711077B02000004130907110B11076F1800000A00203F420F0013050011041C6F1700000A130C11041D6F1900000A130D110C1105FE0416FE01132B112B2D58001104176F1A00000A130E1104186F1A00000A130F1104196F1B00000A131011041A6F1B00000A131111041B6F1900000A1312110E110F11101111111273030000061308110911086F1C00000A0011087B0C000004130A00110C1305110A110C110D73040000066F1D00000A000011046F1E00000A132B112B3A0BFFFFFF00DE14110414FE01132B112B2D0811046F1F00000A00DC0000DE120914FE01132B112B2D07096F1F00000A00DC0020C8000000732000000A13132080380100732000000A13141202282100000A6F2200000A13151F10732300000A13161F10732300000A131772FC0200701215282100000A72DD040070282400000A06731500000A0D00096F1600000A13040038E9020000001104166F1700000A13181104176F1700000A13191104186F1700000A131A1104196F1700000A131B11041A6F2500000A131C11041B6F2500000A131D11041C6F1700000A131E11041D6F1A00000A132C122C282600000A131F1413202B210007111E12206F2700000A16FE01132B112B2D03002B1500111E1F0A5B131E0000111E16FE02132B112B2DD40011207B020000046F2800000A132D3827020000122D282900000A13210011217B0A000004111A320D11217B09000004111AFE022B0117132B112B3AFA0100000011217B08000004111F282A00000A2C1311217B07000004111F282B00000A16FE012B0117132B112B3ACB0100000011217B0B00000413221613230011217B0C0000046F2C00000A132E2B6C122E282D00000A13240011247B0D00000413251125111BFE0416FE01132B112B2D230011221125112359282E00000A11247B0E000004282F00000A283000000A1322002B22001122111B112359282E00000A11247B0E000004282F00000A283000000A13222B121125132300122E283100000A132B112B2D87DE0F122EFE160600001B6F1F00000A00DC001116166F3200000A001116111C6F3300000A2611161F2E6F3400000A26111611186F3500000A2611161F2E6F3400000A26111611196F3500000A2614132611166F3600000A13271113112712266F3700000A16FE01132B112B2D18001126257B060000041122283000000A7D06000004002B1D00111C111811191122730200000613261113112711266F3800000A00001117166F3200000A001117111D6F3300000A2611171F2E6F3400000A26111711186F3500000A2611171F2E6F3400000A26111711196F3500000A2614132611176F3600000A13271114112712266F3700000A16FE01132B112B2D18001126257B060000041122283000000A7D06000004002B1D00111D111811191122730200000613261114112711266F3800000A00002B120000122D283900000A132B112B3AC9FDFFFFDE0F122DFE160500001B6F1F00000A00DC000011046F1E00000A132B112B3A07FDFFFF00DE14110414FE01132B112B2D0811046F1F00000A00DC0000DE120914FE01132B112B2D07096F1F00000A00DC001A8D1F000001132F112F1672700500701E733A00000AA2112F17727E0500701E733A00000AA2112F1872880500701F161F146A733B00000AA2112F1972960500701F09733A00000AA2112F733C00000A1328283D00000A11286F3E00000A000011136F3F00000A6F4000000A13302B621230284100000A13290011281611297B050000046F4200000A0011281711297B040000046F4200000A0011281811297B03000004284300000A6F4400000A0011281911297B06000004284500000A6F4600000A00283D00000A11286F4700000A00001230284800000A132B112B2D91DE0F1230FE160800001B6F1F00000A00DC00283D00000A6F4900000A001A8D1F000001132F112F1672700500701E733A00000AA2112F17727E0500701E733A00000AA2112F1872A20500701F161F146A733B00000AA2112F1972960500701F09733A00000AA2112F733C00000A132A283D00000A112A6F3E00000A000011146F3F00000A6F4000000A13302B621230284100000A132900112A1611297B050000046F4200000A00112A1711297B040000046F4200000A00112A1811297B03000004284300000A6F4400000A00112A1911297B06000004284500000A6F4600000A00283D00000A112A6F4700000A00001230284800000A132B112B2D91DE0F1230FE160800001B6F1F00000A00DC00283D00000A6F4900000A0000DE120614FE01132B112B2D07066F1F00000A00DC002A0041DC00000200000036000000140100004A0100001400000000000000020000002D0000003501000062010000120000000000000002000000EE0200007D0000006B0300000F0000000000000002000000730200003E020000B10400000F0000000000000002000000D301000002030000D5040000140000000000000002000000CA01000023030000ED0400001200000000000000020000006E05000073000000E10500000F00000000000000020000006A06000073000000DD0600000F00000000000000020000000C000000EF060000FB06000012000000000000001E02280F00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000D0040000237E00003C0500005806000023537472696E677300000000940B0000B40500002355530048110000100000002347554944000000581100008002000023426C6F620000000000000002000001571502080900000000FA0133001600000100000027000000060000000E000000060000000C000000490000000C0000000100000008000000010000000200000000000A00010000000000060085007E000600B00095000600D2007E000600E4007E000600D601C4010600ED01C40106000A02C40106002902C40106004202C40106005B02C40106007602C40106009102C4010600C902AA020600DD02C40106000903F6023F001D03000006004C032C0306006C032C030A00B10396030A00DD03C7030A00FE03EB030600100495000A001D04C7030A002804C7030A004404EB03060082047E00060096047E000600A5047E000600C304B7040B00F70400000A00840596030A0090058A030A009A0596030A00A80596030A00B30596035B00D50500009300F70400000A000E06F9050A002506F90500000000010000000000010001000000100023002C000500010001000000100041002C00050003000200000010004D002C000500070003000100100059002C0005000D000400010010006400000005000F00050006008C000A000600B7000D000600C4001A000600C8000A000600CD000A000600DA001D000600ED002A000600F7002A000600FF002E0006000A012E00060013011D0006002401310006002A010A00060037011D005020000000008618BE00150001006F20000000008618BE00210002009720000000008618BE0039000600D420000000008618BE0045000B00F02000000000960046014C000D00E828000000008618BE0050000D00000001005001000001005901000002005D01000003006201000004006701000001007101000002007B01000003008301000004008E0100000500970100000100A80100000200B5012900BE0054003100BE0054003900BE0054004100BE0054004900BE0054005100BE0054005900BE0054006100BE0054006900BE0059007100BE0054007900BE005E008900BE0015009100BE0050000C00BE0015000900BE0050001400BE0015009900BE005000A100BE005400A9000B0450001C00BE001500B900BE007F00B90036048600C90051048B001C005A049000C9005E049800C90069049E00C9007504A4000C005A04A90014005A04A900C9007D04AF00D1008E0450002400BE001500D9009C04BB00E100AC04BF00E900BE001500E100D104C300C900D804CA002100E204CF001C00EB04D4000C000205DD002C001005ED0021001C05F20021003205F20014000205DD0034001005ED0019004505010119005105070119005D05070134006905AF00E90072051500E9007D051001E9007D051601E9007D051C0109009C04BB002400EB04D40024005A0490002C006905AF00F900BE002201F900BE002A010901BE0033011101BB053A011901C40540012400E50547013C0002055C014400100571010901F0057601310145057C01090118068301390145058B0109012E06930119013A06400144006905AF001901490650002E002B0015022E000B0015022E0013002F022E001B002F022E0023002F022E004B002F022E006B005F022E003B002F022E00330035022E005B004D022E0063005602A0008B0072009B0164006B007700B300E600FA005301680104800000010000002D0ED4060000000000002C000000020000000000000000000000010075000000000002000000000000000000000001008A03000000000000003C4D6F64756C653E005068696C466163746F724368616C6C656E6765332E646C6C0043616C6C41726561005068696C466163746F724368616C6C656E676533005265706F7274456E74727900546172696666426C6F636B00546172696666526174650053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740041726561436F64650053797374656D2E436F6C6C656374696F6E732E47656E65726963004C697374603100426C6F636B73002E63746F72004B65790059656172005765656B00446563696D616C00546F74616C436F7374004461746554696D65004461746553746172740044617465456E6400486F757273537461727400486F757273456E6400436F6E6E656374696F6E436861726765005261746573005570546F584D696E757465730050726963655065724D696E757465004765745265706F72740061726561436F6465006B65790079656172007765656B00746F74616C436F7374006461746553746172740064617465456E6400686F757273537461727400686F757273456E6400636F6E6E656374696F6E436861726765007570546F584D696E757465730070726963655065724D696E7574650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0044696374696F6E61727960320053716C436F6D6D616E640053716C4461746152656164657200457865637574655265616465720044624461746152656164657200476574496E7433320041646400476574446563696D616C004765744461746554696D65004765744279746500526561640049446973706F7361626C6500446973706F736500496E74333200546F537472696E6700537472696E67006765745F4C656E6774680053797374656D2E5465787400537472696E674275696C64657200436F6E63617400476574537472696E67006765745F446174650054727947657456616C756500456E756D657261746F7200476574456E756D657261746F72006765745F43757272656E74006F705F477265617465725468616E4F72457175616C006F705F4C6573735468616E4F72457175616C006F705F496D706C69636974006F705F4D756C7469706C79006F705F4164646974696F6E004D6F76654E657874007365745F4C656E67746800417070656E640053716C4D657461446174610053716C4462547970650053716C446174615265636F72640053716C436F6E746578740053716C50697065006765745F506970650053656E64526573756C747353746172740056616C7565436F6C6C656374696F6E006765745F56616C75657300536574496E7433320053797374656D2E446174612E53716C54797065730053716C537472696E670053657453716C537472696E670053716C4D6F6E65790053657453716C4D6F6E65790053656E64526573756C7473526F770053656E64526573756C7473456E6400002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000082C9530045004C00450043005400200063006F006E007600650072007400280069006E0074002C0020005B00430061006C006C0041007200650061005D0029002C0020005B004400610074006500530074006100720074005D002C0020005B00440061007400650045006E0064005D002C00200063006F006E0076006500720074002800740069006E00790069006E0074002C0020005B0048006F00750072007300530074006100720074005D0029002C00200063006F006E0076006500720074002800740069006E00790069006E0074002C0020005B0048006F0075007200730045006E0064005D0029002C0020005B0043006F006E006E0065006300740069006F006E004300680061007200670065005D002C0020005B005500700054006F0058004D0069006E0075007400650073005D002C0020005B00500072006900630065005000650072004D0069006E007500740065005D002000460052004F004D0020005B00640062006F005D002E005B00500068006F006E0065005400610072006900660066005D0020005B00700074005D00200049004E004E004500520020004A004F0049004E0020005B00640062006F005D002E005B00500068006F006E00650054006100720069006600660043006800610072006700650073005D0020005B007000740063005D0020004F004E0020005B007000740063005D002E005B00500068006F006E006500540061007200690066006600490064005D0020003D0020005B00700074005D002E005B00500068006F006E006500540061007200690066006600490064005D0020004F00520044004500520020004200590020005B00430061006C006C0041007200650061005D002C0020005B004400610074006500530074006100720074005D002C0020005B0048006F00750072007300530074006100720074005D002C0020005B005500700054006F0058004D0069006E0075007400650073005D000081DF530045004C004500430054002000590045004100520028005B00430061006C006C00530074006100720074005D0029002000410053002000590072002C0020004400410054004500500041005200540028007700650065006B002C0020005B00430061006C006C00530074006100720074005D002900200041005300200057006B002C002000440041005400450050004100520054002800680068002C0020005B00430061006C006C00530074006100720074005D0029002000410053002000680072002C002000280044004100540045004400490046004600280073002C0020005B0063006C005D002E005B00430061006C006C00530074006100720074005D002C0020005B0063006C005D002E005B00430061006C006C0045006E0064005D00290020002B00200035003900290020002F0020003600300020004100530020004D0069006E0075007400650073004400750072006100740069006F006E002C0020005B004F00660066006900630065004E0061006D0065005D002C0020005B0055007300650072004E0061006D0065005D002C00200063006F006E007600650072007400280069006E0074002C0020004C0045004600540028005B004E0075006D006200650072004400690061006C006C00650064005D002C002000008091290029002C0020005B00430061006C006C00530074006100720074005D002000460052004F004D0020005B00640062006F005D002E005B00430061006C006C004C006F0067005D0020005B0063006C005D0020005700480045005200450020005B00430061006C006C00530074006100720074005D00200021003D0020005B00430061006C006C0045006E0064005D00000D5700650065006B004E006F0000095900650061007200000D4F0066006600690063006500000B54006F00740061006C00001155007300650072004E0061006D00650000D1808CEFA3EFD040B87D6BA38EC6EBEA0008B77A5C561934E0890206080706151209011210042001010802060E0306110D082004010E0808110D0306111102060507061512090112140B200501111111110505110D0620020108110D0300000103200001042001010E0420010102052001011141061512090112100615120901121404010000000715125902081208062002010E1251042000126104200108080720020113001301052001110D0805200111110804200105080520010113000320000207151259020E120C0320000E032000080600030E0E0E0E0420010E080420001111082002021300101301082000151179011300061511790112100420001300070002021111111106151179011214050001110D08080002110D110D110D05200112750E052001127503052001127508072002010E118081082003010E1180810A062001011D127D05000012808D062001011280850B20001512809102130013010815128091020E120C0B20001511809502130013010815118095020E120C04200013010520020108080600011180990E072002010811809907000111809D110D072002010811809D79073112511512590208120808125D12610808120812101512090112101512090112140808110D111111110505110D151259020E120C151259020E120C0812751275080808080E0E08111112081210110D08121408120C0E128085120C1280850211111511790112101511790112141D127D15118095020E120C190100145068696C466163746F724368616C6C656E676533000005010000000017010012436F7079726967687420C2A920203230303900000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000A8A41D4B000000000200000080000000E43C0000E41E000052534453A6811BF4DCA3C94EA949E38483DC31A201000000433A5C436F64655C41746C616E7469735C5468726F77617761795C5068696C466163746F724368616C6C656E6765335C5068696C466163746F724368616C6C656E6765335C6F626A5C44656275675C5068696C466163746F724368616C6C656E6765332E706462008C3D00000000000000000000AE3D0000002000000000000000000000000000000000000000000000A03D00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000340300000000000000000000340334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100D4062D0E00000100D4062D0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00494020000010053007400720069006E006700460069006C00650049006E0066006F000000700200000100300030003000300030003400620030000000540015000100460069006C0065004400650073006300720069007000740069006F006E00000000005000680069006C0046006100630074006F0072004300680061006C006C0065006E00670065003300000000003C000E000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003600320039002E003100370034003800000054001900010049006E007400650072006E0061006C004E0061006D00650000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650033002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003000390000005C00190001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650033002E0064006C006C00000000004C0015000100500072006F0064007500630074004E0061006D006500000000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650033000000000040000E000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003600320039002E003100370034003800000044000E00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003600320039002E00310037003400380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000C03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [PhilFactorChallenge3]
WITH VISIBILITY = ON
GO
CREATE PROCEDURE [dbo].[GetReport]
AS EXTERNAL NAME [PhilFactorChallenge3].[StoredProcedures].[GetReport]
GO

Exec is:

EXEC [dbo].[GetReport]

Teardown is:

DROP PROCEDURE [dbo].[GetReport]
GO
DROP ASSEMBLY [PhilFactorChallenge3]
GO
DROP INDEX [IX_PhoneTariff_1]
ON [dbo].[PhoneTariff]
GO
DROP INDEX [IX_PhoneTariffCharges_1]
ON [dbo].[PhoneTariffCharges]
GO

Hmmm... well i did include the source code, but I ran out of post space. I'll post that in a separate answer.

This one runs at between 3 and 4 seconds on my box...

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<TariffRate> Rates = new List<TariffRate>(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 < 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<string, ReportEntry> officeReport = new Dictionary<string, ReportEntry>(200);
            Dictionary<string, ReportEntry> usersReport = new Dictionary<string, ReportEntry>(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 > 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 >= hour && tb.HoursStart <= hour)
                            {
                                if (tb.DateEnd >= startDate && tb.DateStart <= 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 < 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();
        }
    }
};

/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<UpToXMinutes
                                      THEN CallLengthTotal
                                      ELSE UpToXMinutes
                                 END,
                TotalCharge=TotalCharge+(PricePerMinute
                                         *(CASE WHEN CallLengthTotal<UpToXMinutes
                                                THEN CallLengthTotal
                                                     -CallLengthCosted
                                                ELSE UpToXMinutes
                                                     -CallLengthCosted
                                           END))
        FROM    #Tempcalllog t
                INNER JOIN #phoneTariffCharges ON #PhoneTariffcharges.phonetariffID=t.phonetariffID
        WHERE   orderOfCharge=@ii
                AND CallLengthCosted<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'))
*/

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