# The ‘Call Log’ SQL Problem

 0 Once again it is time for a Phil Factor Speed Phreak Challenge and the prize is now a \$100 Amazon voucher, and the privilege of displaying this rather nice trophy jpg on your blog / website / bedroom wall. This time your task is simply to produce a summary report of the cost of international phone calls made within an organization on a week by week basis for both Users and Offices. You are allowed to use any method you see fit, you may also add any indexes, table-functions or views that you wish (though not an index view). Creation of these will not count to the overall execution time. If you are unsure if what you want to do might disqualify you, then please post a comment. The table CallLog contains the log of every phone call, including which user called which number, when the call started and ended, and the office the user was in at the time. You will notice that it is not well normalized, since it is actually a view taken from several tables. Users are never in a fixed office, and can move from office to office at any point. Calls with a CallEnd equal to CallStart were not answered and can be safely ignored. To calculate the cost of the call you need to use a lookup within the PhoneTariff table. The calls are prefixed by an area code corresponding to a country. You must note that many of the call areas have the same starting character sequence , so calls made to numbers starting '35191' must be priced using the tariff of '35191' not '351'. There is a rather elaborate charging system according to the length of the call. This means that you need to calculate the cost of the call as the minute by minute cost changes with the length of the call. These must be summed. Looking at the PhoneTariffCharges table ``````PhoneTariffId UpToXMinutes PricePerMinute ------------- ------------ --------------------- 2865 8 0.4792 2865 31 0.18 2865 59 0.5702 2865 100 1.298 `````` The first 8 minutes of the call will be 0.4792 per minute. 9 to 31 minutes will be 0.18 per minute 32 to 59 minutes will be 0.5702 per minute etc.... All ranges have a UpToXMinutes of 9999 so you dont need to worry about an upper limit. The calllength is rounded upto the nearest whole minute. Here is the DDL to create the tables ``````CREATE TABLE [dbo].[CallLog]( [CallLogId] [int] NOT NULL, [CallStart] [datetime] NOT NULL, [CallEnd] [datetime] NULL, [OfficeName] [varchar](20) NOT NULL, [UserName] [varchar](20) NOT NULL, [NumberDialled] [varchar](255) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PhoneTariff]( [PhoneTariffId] [int] IDENTITY(1,1) NOT NULL, [CallArea] [char](6) NULL, [DateStart] [datetime] NULL, [DateEnd] [datetime] NULL, [HoursStart] [int] NULL, [HoursEnd] [int] NULL, [ConnectionCharge] [money] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PhoneTariffCharges]( [PhoneTariffId] [int] NULL, [UpToXMinutes] [int] NULL, [PricePerMinute] [money] NULL ) ON [PRIMARY] GO `````` and heres the link to the data. Use ``````bcp ..CallLog in CallLog.out -c -E -T bcp ..PhoneTariff in PhoneTariff.out -c -E -T bcp ..PhoneTariffCharges in PhoneTariffCharges.out -c -E -T `````` to load the data in. Heres the solution provided by our mediocre developer Robert Bar, please note a fix for an issue with the week number being taken from @CallEnd. ``````Drop Table #OfficeTotalByWeek go Drop Table #UserTotalByWeek go Create Table #OfficeTotalByWeek ( WeekNo integer, Year integer, Office varchar(255), Total money ) go Create Table #UserTotalByWeek ( WeekNo integer, Year integer, UserName varchar(255), Total money ) go /* Routine : Call Cost Calculate */ /* Developer : R Bar (RB) */ declare @CallStart datetime, @CallEnd datetime, @OfficeName varchar(255), @UserName varchar(255), @NumberDialled varchar(255), @PhoneTariffId integer, @ConnectionCharge money, @RangeStart datetime, @RangeEnd datetime declare CalcCursor Cursor for Select CallStart,CallEnd,OfficeName,UserName,NumberDialled from CallLog -- where OfficeName = 'Marketing' --and UserName = 'Yolanda Haley' open CalcCursor while(0=0) begin /* RB : For Each Call in CallLog */ fetch next from CalcCursor into @CallStart,@CallEnd,@OfficeName,@UserName,@NumberDialled if(@@Fetch_Status <>0) begin break end if(@CallStart = @CallEnd) begin /* RB : If True then the call was not answered so no charge */ continue end /* RB */ /* Find the appropriate Tariff */ /* The number dialled must match the CallArea */ /* But as the length of the CallArea is variable */ /* An index seek cannot be used */ Declare @CallDatech varchar(15) Select @CallDatech = CONVERT(varchar(15),@CallStart,112) select @PhoneTariffId = NULL set rowcount 1 Select @PhoneTariffID = PhoneTariffId, @ConnectionCharge = ConnectionCharge, @RangeStart = DateStart, @RangeEnd = DateEnd from phonetariff where @NumberDialled like rtrim(CallArea)+'%' and @CallDateCh between DateStart and DateEnd and DatePart(hh,@CallStart) between HoursStart and HoursEnd order by len(CallArea) desc /* RB : Order by is used so the longest CallArea is used */ set rowcount 0 if(@PhoneTariffId is null) begin select 'early break out error ',@NumberDialled end Declare @CallLength integer Select @CallLength = ceiling((DATEDIFF(S,@CallStart,@CallEnd))/60.0) Declare @RollingBalance money Declare @UptoXminutes integer Declare @PricePerMinute money Declare @MinsThisIter integer /* RB : Start the CallCost at the Connection Charge */ Select @RollingBalance = @ConnectionCharge --Select @ConnectionCharge as 'ConnectionCharge' declare @PrevUptoXMinutes integer select @PrevUptoXMinutes =0 Declare TimeCalc Cursor for Select UpToXMinutes,PricePerMinute from Phonetariffcharges where PhoneTariffId = @PhoneTariffID order by UpToXMinutes Open timecalc /* RB : Now Loop through the minute by minute cost totalling up as we go */ while(0=0) begin fetch next from timecalc into @UptoXminutes,@PricePerMinute if(@@FETCH_STATUS <>0) begin select 'early break out error',@NumberDialled,@PhoneTariffId break end Declare @MinsToIter integer /* RB : Calculate how many minutes from this portion we need */ Select @MinsThisIter = case when @uptoXminutes > @CallLength then @CallLength -@PrevUptoXMinutes else @uptoXminutes -@PrevUptoXMinutes end Select @RollingBalance = @RollingBalance +(@MinsThisIter*@PricePerMinute) Select @PrevUptoXMinutes = @uptoXminutes if(@uptoXminutes>=@CallLength)begin /* RB : Have we accounted for each minute */ break end end close TimeCalc Deallocate TimeCalc if(@PrevUptoXMinutes = 0) begin select 'Charges Calc Error',@NumberDialled,@PhoneTariffId,@CallLength end Declare @Year integer Declare @WeekNo integer Select @Year=DATEPART(yy,@CallStart), @WeekNo=DATEPART(WEEK,@CallStart) /* RB : Update the Users week-by-week total */ Update #UserTotalByWeek set Total = Total + @RollingBalance where UserName =@UserName and WeekNo = @WeekNo and Year = @Year if(@@ROWCOUNT=0) begin /* RB : Or Create */ insert into #UserTotalByWeek(UserName,WeekNo,Year,Total) values(@UserName,@WeekNo,@Year,@RollingBalance) end /* RB : Update the Office week-by-week total */ Update #OfficeTotalByWeek set Total = Total + @RollingBalance where Office = @OfficeName and WeekNo = @WeekNo and Year = @Year if(@@ROWCOUNT=0) begin /* RB : Or Create */ insert into #OfficeTotalByWeek(Office,WeekNo,Year,Total) values(@OfficeName,@WeekNo,@Year,@RollingBalance) end end close CalcCursor Deallocate CalcCursor go /* RB : Select the totals back */ Select * from #OfficeTotalByWeek Select * from #UserTotalByWeek go Drop Table #OfficeTotalByWeek go Drop Table #UserTotalByWeek go `````` Here are some guidelines for your entries: 1) Include a header in your suggestion. Make sure your name and the current date is present. 2) Include an edition number. First edition is 1. If you later improve your current suggestion post it again as version 2. Example: “Peso 1” and if improved, “Peso 1b”, “Peso 1c” etc. 3) If you are trying a new algorithm, change the edition to “Peso 2”. If you improve this algorithm, change the version to “Peso 2b”, “Peso 2c” etc. This will save Phil hours of work in the test harness! 4) The solution must clear up all its mess (temporary tables, indexes, etc.) so it can be re-run without errors. As ever Phil Factor will be final judge.The closing date will be midnight Thursday 17th December London more ▼ asked Dec 02, 2009 at 01:07 PM in Default dave ballantyne 928 ● 1 ● 1 ● 4 Peso 1.6k ● 5 ● 6 ● 8 Can we be specific about how timings will be done? Average of x runs with a dropcleanbuffers before each? Average of x runs with a dropcleanbuffers before the first run? Dec 02, 2009 at 03:41 PM Matt Whitfield ♦♦ What about Abel Buck? His call was 0.000 seconds and thus no charge for length of call, but is he still going to pay for the connection of 52 cents? Dec 03, 2009 at 09:57 AM Peso I am tryting to find a 100% correct answer but sometimes i doubt if the answer privided by Robert Bar is 100% Correct. Should we take his answer as the final results or should we try to prove that there could be something wrong with it and propose something new ? Dec 03, 2009 at 10:33 AM Gustavo With regard to Abel Buck the call was not answered and therefore no charge. "Calls with a CallEnd equal to CallStart were not answered and can be safely ignored." The Robert Bar solution should be correct. If there are any issues with a calculation please PM me and ill manually double check. Dec 03, 2009 at 03:13 PM dave ballantyne I took a random case, number dialled: 980913853342, according to Robert Bar he used area code 980 and number 91-385-3342 (formated 2,3,4) ["order by len(CallArea) desc"], but you could also get area code 98 and number dialled 091-385-3342 ( formated 3-3-4 ). My point is that phone numbers of 12 digits ( this case ) should have just 2 chars on area code, those with 13 digits, have 3 chars as area code and so on, so the remaning number will still be formated 3-3-4 and not [1..3]-3-4. Meaning that the proposed solution could not be the corret one... Dec 03, 2009 at 06:15 PM Gustavo add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

sort voted first
 0 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... more ▼ answered Dec 04, 2009 at 11:07 AM Matt Whitfield ♦♦ 29.5k ● 61 ● 65 ● 87 Good Stuff. Results are spot on, just change the ALTER PROC to CREATE PROC. By my calculations , youre in front :) Dec 04, 2009 at 11:54 AM dave ballantyne Whoops - sorry about that! One thing that confused me - why is the year number taken from the start date and the week number taken from the end date? Only produces differences on a few rows, but quite interesting! :) Also, let me know if you'd like a copy of the test harness I sent to Phil & Peso, should support everything needed for this challenge... Dec 04, 2009 at 12:38 PM Matt Whitfield ♦♦ That'll be a typo. Ill change it in the question now. Phil has already taken the liberty of forwarding on the harness, thanks. Dec 04, 2009 at 12:53 PM dave ballantyne I get wrong result for Jose Yu. Me and Phil get two records (week 8 and 9). You get both timings on week 9 (1.654 + 1.7612). Dec 05, 2009 at 07:39 PM Peso Yeah - if you have a look at the comments above, Dave has changed it so that the week number now comes from the start date and not the end date, like it was originally. I haven't had time to update it yet because I'm not at home... Dec 06, 2009 at 09:22 AM Matt Whitfield ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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! :) more ▼ answered Dec 07, 2009 at 11:00 AM dave ballantyne 928 ● 1 ● 1 ● 4 Nice formatting! However, I miss thousands separator and right aligned number... :-) Dec 07, 2009 at 02:02 PM Peso 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. Dec 07, 2009 at 04:19 PM Phil Factor I'll add thousand separators and right alignment to the harness... Dec 07, 2009 at 04:37 PM Matt Whitfield ♦♦ Cant take any credit for the formatting :) Dec 07, 2009 at 04:59 PM dave ballantyne There was an issue with my scripts which meant that lmu92's indexes were not created. Dec 09, 2009 at 06:05 AM dave ballantyne add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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)... more ▼ answered Dec 16, 2009 at 06:04 PM lmu92 21 Wow! 3.156 seconds on my machine. this is very impressive Dec 16, 2009 at 06:55 PM Phil Factor I don't get it. The code takes 11 seconds on my machine... Dec 16, 2009 at 07:51 PM Peso I suspect that quite a bit of the variation is the size of the buffer cache on the various machines. Dec 17, 2009 at 05:53 AM Phil Factor add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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, CallWeekDROP TABLE #Output ` more ▼ answered Dec 05, 2009 at 02:09 PM Peso 1.6k ● 5 ● 6 ● 8 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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')) ` more ▼ answered Dec 05, 2009 at 03:19 PM Phil Factor 3.9k ● 8 ● 9 ● 16 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? Dec 05, 2009 at 07:21 PM Phil Factor Phil, I get Abel Buck included in your code. He is not to be reported since the call was not answered (CallStart equals CallEnd). Dec 05, 2009 at 07:37 PM Peso add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

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

By Email:

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

Topics:

x991
x346
x14
x8
x7

asked: Dec 02, 2009 at 01:07 PM

Seen: 7993 times

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