The ‘Call Log’ SQL Problem

Question

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

Answer 1

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...

Answer 2

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

Answer 3

Initial Timings are pretty close ,

+-----------------------+--------+--------+--------+--------+--------+
| Name                  | Min    | Avg    | Max    | Total  | Runs   |
+-----------------------+--------+--------+--------+--------+--------+
| peso4c                | 3018   | 3270   | 3507   | 9811   | 3      |
| matt2a                | 3347   | 3536   | 3781   | 10610  | 3      |
| peso4b                | 3706   | 3870   | 4004   | 11611  | 3      |
| lmu921b               | 3810   | 3918   | 4018   | 11756  | 3      |
| peso4a                | 3838   | 3985   | 4264   | 11956  | 3      |
| lmu92                 | 4948   | 5113   | 5213   | 15340  | 3      |
| peso2b                | 5105   | 5308   | 5625   | 15926  | 3      |
| peso3a                | 5839   | 5973   | 6226   | 17919  | 3      |
| phil2a                | 6543   | 6619   | 6693   | 19859  | 3      |
| Phil2b                | 6568   | 6624   | 6695   | 19873  | 3      |
| phil1d                | 7044   | 7696   | 8446   | 23089  | 3      |
| peso                  | 8409   | 8512   | 8596   | 25536  | 3      |
| phil1c                | 7642   | 8833   | 10935  | 26501  | 3      |
| phil                  | 9368   | 9575   | 9727   | 28725  | 3      |
| matt                  | 9448   | 9637   | 9913   | 28911  | 3      |
+-----------------------+--------+--------+--------+--------+--------+

Just for comparison - timings from Matt W's machine:

+------------------------+----------+----------+----------+----------+----------+
| Name                   | Min      | Avg      | Max      | Total    | Runs     |
+------------------------+----------+----------+----------+----------+----------+
| matt2a                 |    4,221 |    4,440 |    4,816 |   44,407 |       10 |
| peso4c                 |    4,038 |    4,651 |    6,266 |   46,518 |       10 |
| peso4b                 |    5,178 |    6,363 |    8,733 |   63,639 |       10 |
| peso4a                 |    5,638 |    7,142 |   11,670 |   71,424 |       10 |
| lmu921b                |    6,390 |    7,220 |    8,612 |   72,209 |       10 |
| peso2b                 |    7,322 |    8,504 |    9,497 |   85,043 |       10 |
| peso3a                 |    8,246 |    8,838 |    9,808 |   88,380 |       10 |
| bluedog                |    8,542 |    9,254 |   10,169 |   92,545 |       10 |
| Phil2b                 |    9,612 |    9,850 |   10,192 |   98,501 |       10 |
| phil1d                 |   10,825 |   11,138 |   11,725 |  111,383 |       10 |
| phil1c                 |   11,170 |   11,927 |   12,956 |  119,271 |       10 |
| peso                   |   12,188 |   12,720 |   13,364 |  127,203 |       10 |
| phil                   |   12,957 |   13,636 |   15,236 |  136,364 |       10 |
+------------------------+----------+----------+----------+----------+----------+

Note that matt1 and lmu921 didn't run because I changed the collation in my test db and they both failed with collation conflicts. I didn't feel the need to alter my SQL entry! :)

Answer 4

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

Answer 5

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

The ‘Call Log’ SQL Problem

Follow this question

Related Questions