# The ‘Call Log’ SQL Problem

sort voted first
 0 more ▼ answered Dec 07, 2009 at 10:07 PM Matt Whitfield ♦♦ 29.5k ● 61 ● 65 ● 87 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 Peso 4B - 20091216 Same setup and teardown as version 4a. Main CodeCREATE 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.HoursEndCREATE 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.UpToXMinutesUPDATE 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.PhoneTariffIdSELECT cl.OfficeName, DATEPART(YEAR, cl.CallStart) AS CallYear, DATEPART(WEEK, cl.CallStart) AS CallWeek, SUM(l.ConnectionCharge + s.DurationCharge) AS Charge FROM #Logs AS l INNER JOIN dbo.CallLog AS cl WITH (NOLOCK) ON cl.CallLogId = l.CallLogId CROSS APPLY ( SELECT SUM(d.Amount) FROM ( SELECT CASE WHEN l.CallDuration >= c.ToMinute THEN c.ToMinute - c.FromMinute WHEN l.CallDuration BETWEEN c.FromMinute AND c.ToMinute THEN l.CallDuration - c.FromMinute ELSE 0 END * c.PricePerMinute AS Amount FROM #Charges AS c WHERE c.PhoneTariffId = l.PhoneTariffId ) AS d ) AS s(DurationCharge) GROUP BY cl.OfficeName, DATEPART(YEAR, cl.CallStart), DATEPART(WEEK, cl.CallStart)SELECT cl.UserName, DATEPART(YEAR, cl.CallStart) AS CallYear, DATEPART(WEEK, cl.CallStart) AS CallWeek, SUM(l.ConnectionCharge + s.DurationCharge) AS Charge FROM #Logs AS l INNER JOIN dbo.CallLog AS cl WITH (NOLOCK) ON cl.CallLogId = l.CallLogId CROSS APPLY ( SELECT SUM(d.Amount) FROM ( SELECT CASE WHEN l.CallDuration >= c.ToMinute THEN c.ToMinute - c.FromMinute WHEN l.CallDuration BETWEEN c.FromMinute AND c.ToMinute THEN l.CallDuration - c.FromMinute ELSE 0 END * c.PricePerMinute AS Amount FROM #Charges AS c WHERE c.PhoneTariffId = l.PhoneTariffId ) AS d ) AS s(DurationCharge) GROUP BY cl.UserName, DATEPART(YEAR, cl.CallStart), DATEPART(WEEK, cl.CallStart)DROP TABLE #Logs, #Charges more ▼ answered Dec 16, 2009 at 08:52 AM Peso 1.6k ● 5 ● 6 ● 8 Definitely faster, 3.941 seconds on my box - but only returns 53,703 rows in the second result set? Dec 16, 2009 at 10:53 AM Matt Whitfield ♦♦ ...can I ask, too... how long did developing this one take? Just interested from a general cost/benefit perspective... Dec 16, 2009 at 10:54 AM Matt Whitfield ♦♦ Forgot a CROSS APPLY. This one took about 30 minutes to develop and another 15 minutes looking at the execution plan. However, I did miss a cross apply so debugging the code above took another hour. 2 hour total. Dec 16, 2009 at 12:10 PM Peso Cool, correct results now, 5.317 seconds... It would have taken me a lot longer to develop the SQL the way you have I think. Probably more in the region of 5-6 hours... Dec 16, 2009 at 12:31 PM Matt Whitfield ♦♦ This runs in 3 secs on my machine. Mine runs in 5.4. Dec 16, 2009 at 06:46 PM Phil Factor 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:

x990
x346
x14
x8
x7

asked: Dec 02, 2009 at 01:07 PM

Seen: 7948 times

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