This post has been wikified, any user with enough reputation can edit it.
The ‘Call Log’ SQL Problem
Once again it is time for a Phil Factor Speed Phreak Challenge and the prize is now a $100 Amazon voucher, and the privilege of displaying this rather nice trophy jpg on your blog / website / bedroom wall.
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
Can we be specific about how timings will be done? Average of x runs with a dropcleanbuffers before each? Average of x runs with a dropcleanbuffers before the first run?
What about Abel Buck? His call was 0.000 seconds and thus no charge for length of call, but is he still going to pay for the connection of 52 cents?
I am tryting to find a 100% correct answer but sometimes i doubt if the answer privided by Robert Bar is 100% Correct. Should we take his answer as the final results or should we try to prove that there could be something wrong with it and propose something new ?
With regard to Abel Buck the call was not answered and therefore no charge. "Calls with a CallEnd equal to CallStart were not answered and can be safely ignored." The Robert Bar solution should be correct. If there are any issues with a calculation please PM me and ill manually double check.
I took a random case, number dialled: 980913853342, according to Robert Bar he used area code 980 and number 91-385-3342 (formated 2,3,4) ["order by len(CallArea) desc"], but you could also get area code 98 and number dialled 091-385-3342 ( formated 3-3-4 ). My point is that phone numbers of 12 digits ( this case ) should have just 2 chars on area code, those with 13 digits, have 3 chars as area code and so on, so the remaning number will still be formated 3-3-4 and not [1..3]-3-4. Meaning that the proposed solution could not be the corret one...
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...
Good Stuff. Results are spot on, just change the ALTER PROC to CREATE PROC. By my calculations , youre in front :)
Wiki
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! :)
Nice formatting! However, I miss thousands separator and right aligned number... :-)
My timings agree with this pretty closely. My routine is now in the mid five-seconds, but I need to double-check the results before I post the SQL!
Yeah. cute formatting, Dave.
I'll add thousand separators and right alignment to the harness...
There was an issue with my scripts which meant that lmu92's indexes were not created.
Wiki
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)...
Wow! 3.156 seconds on my machine. this is very impressive
I suspect that quite a bit of the variation is the size of the buffer cache on the various machines.
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'))
Oops. the first time I pasted the code in, I use but because I had a < in the code, it got mangled. Is there an easy way to past code into this darned software?
Phil, I get Abel Buck included in your code. He is not to be reported since the call was not answered (CallStart equals CallEnd).
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]
Wiki
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...
Wiki
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();
}
}
};
Wiki
/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'))
*/
Nice! Two things though... 1) Add "WHERE CallStart < CallEnd" to #TempCallLog to make the table smaller and 2) Add a sixth update clause to handle CHAR(6) definition.
Phil, how long time does the other suggestions take on your machine?
How odd. I just checked it again. five and a half seconds. I had tried putting in more indexes (even followed SSMS's suggestions) and everything I did added another second to the results. Can you see what is slowing it down?
Why do I need to add a sixth update clause to handle CHAR(6) definition when there aren't any six-character codes in the phoneTariff table?
Wiki
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
Definitely faster, 3.941 seconds on my box - but only returns 53,703 rows in the second result set?
...can I ask, too... how long did developing this one take? Just interested from a general cost/benefit perspective...
Forgot a CROSS APPLY. This one took about 30 minutes to develop and another 15 minutes looking at the execution plan. However, I did miss a cross apply so debugging the code above took another hour. 2 hour total.
Cool, correct results now, 5.317 seconds... It would have taken me a *lot* longer to develop the SQL the way you have I think. Probably more in the region of 5-6 hours...
