asked
Price for service based on duration and priceinterval
**EDIT** Thanks all for input! To clarify a little: - I want to handle calls that cross tariffs such that a call starting at 17:50 and ending at 18:10 should be charged 10 minutes with day-tariff and 10 minutes with night tariff. - SQL Server 2005 (though that was one of the tags) The reason I ask is not that I at the moment am working on such solution. I came to think of a billing solution I delivered to a phone company more than 10 years ago, which involved cursors and that was painfully slow. They had very few calls in their call-log but billing still took hours. The company has since grown their customer base by at least a factor 100. As you can imagine, it's not a delivery that I feel very proud of, but I can't from the top of my head think of a solution that does NOT involve cursors. **END EDIT** Hi! I have two tables: Calls and Prices Calls contains (these are the interesting columns): CallID int (PK), StartTime datetime, EndTime datetime Some sample values: CallID StartTime EndTime 1 2010-10-01 17:43:10 2010-10-02 08:25:01 2 2010-10-01 17:44:13 2010-10-01 17:59:38 3 2010-10-01 17:50:25 2010-10-01 18:37:52 The Prices table: StartTime varchar(10), EndTime varchar(10), Tariff numeric(8,2) All the rows in Prices table: StartTime EndTime Tariff 00:00 06:00 2,00 06:00 18:00 4,00 18:00 00:00 2,00 This should be understood: The Calls table contains all calls, and their start- and end time (obviously..) Prices contains prices for different time periods during the day. I want to do two things: 1. Get the price per row in the Calls table, based on the Prices table. 2. Change the Prices table so it also takes weekdays into consideration (so that different prices can be applied on sundays for example). Suggestions anyone?
Have you had a quick look at
http://ask.sqlservercentral.com/questions/2295/the-call-log-sql-problem ?
Hi, I didn't before I asked the question (obviously I should have before posting the question..). But that problem is far more detailed than mine. My problem is a rather simple one, but my brain seems to be out of service today. When I was looking through the answers to the Phil Factor-challenge, it (by brain) actually started burning, setting off the fire alarm here at the office... So no, that unfortunately didn't help much. But I've bookmarked it and will look at it when my brain starts to recover from the little fire a moment ago...
Hahah - a sure fire sign that you need to take the afternoon off! :)
@Magnus Ahlkvist Thank you for clarification. This problem might make coming weekend very interesting! The only thing I would like to clarify is this: the request to charge accurate rates for cross-period calls makes sense (though I know that some telecoms choose the origination based decision, it evens out the discrepancies provided high enough volume of calls). The clarification I am seeking is about the following: Usually, there is a rounding factor, which in the simplest case resolves to the next minute, meaning call length of 10 minutes 5 seconds gets 11 minutes charge. Now, for cross-period calls, how should this rounding affect results? For example, consider a call starting on Friday evening at 17:58:59 and ending at 18:05:01. The total calling time is 6 minutes 2 seconds which should ordinarily resolve to 7 minutes charge. With cross-period, the charge should be 1:01 => 2 minutes plus 5:01 => 6, totalling 8 minutes not 7. Please let me know if this assumption is correct. If not then what is the rule? And one more thing: is there any limit on the lenght of the call? If not then additional design considerations are needed for calls lasting more than a week :)
@Oleg as far as I can recall, we used no rounding - we made the calculation on fractions of minutes.
Not sure how your weekday scenario is setup, but you could do it a number of ways. CTE, CASE statement in the join. DROP TABLE #Calls DROP TABLE #Prices GO CREATE TABLE #Calls ( CallID INT , StartTime SMALLDATETIME , EndTime SMALLDATETIME ) INSERT INTO #Calls SELECT 1, '2010-10-01 17:43:10', '2010-10-02 08:25:01' UNION SELECT 2, '2010-10-01 17:44:13', '2010-10-01 17:59:38' UNION SELECT 3, '2010-10-01 17:50:25', '2010-10-01 18:37:52' CREATE TABLE #Prices ( StartTime varchar(10) , EndTime varchar(10) , tariff numeric(8,2) ) INSERT INTO #Prices SELECT '00:00', '06:00', 2.00 UNION SELECT '06:00', '18:00', 4.00 UNION SELECT '18:00', '00:00', 2.00 SELECT * FROM #calls C LEFT JOIN #Prices P ON CONVERT(TIME, C.StartTime) BETWEEN CONVERT(TIME, P.StartTime) AND CONVERT(TIME, P.EndTime) AND CONVERT(TIME, C.EndTime) BETWEEN CONVERT(TIME, P.StartTime) AND CONVERT(TIME, P.EndTime)
@ThomasRushton The answer does not offer a complete solution to Magnus' question, but I believe that calls across the boundaries should not matter. The rules in telecoms usually prescribe to charge a customer using a tariff (price per unit, typically a minute, but could be something else) based on the tariff effective at the ***beginning*** of the call. For example, suppose you have free night calls on your cell phone, and the period is from 21:00 to 06:00. If you initiate a call at 20:59 and talk for an hour, your account will be charged 60 minutes usage :( but if you begin a call at 05:00 in the morning and talk for 2 hours then your account will not be charged at all :) though the end of the call comes well after the end of the free tariff period. Just my 2 cents
@SQLvis There is another twist, which is related to the Prices table design. The end of one period matches exactly the beginning of the next, and therefore, it is inevitable that whatever statement we use, obscure results will **sometimes** pop up whenever the call begins at exactly at midnight or 6:00 AM or 6:00 PM as those calls will result in two matches instead of desired one. Unwanted results which only popup occasionally and are not obviously reproducible are the most lovely ones to debug, aren't they? So, this problem is a bit trickier than it could initially appear :) I will try to come up with a proposal in a little bit. By the way, whenever you include \* in your comments, you should escape it with the backslash character, otherwise, the system assumes that it is a beginning of the *italic text*. In other words, if you need to spell out \*, type \\* instead.
If the references to StartTime and Endtime are made using > and = and < respectively then there shouldnt be any obscure results. This would be a mammoth ask of all developers and I would agree with Oleg that the end of one period should not have the same value as the start of the next. so a period should run from 06:00:00 to 17:59:59.997 for example and the next period should start at 18:00:00 and so forth
That particular statement doesn't work for calls which go across time boundaries for prices...
That's true Oleg. As the constraint for this question weren't quite defined appropriately, I put down an answer I thought was reasonable. If we want to track prices for a call that starts in a period of time, then we need to just look at the C.StartTime only. LEFT JOIN #Prices P ON CONVERT(TIME, C.StartTime) BETWEEN CONVERT(TIME, P.StartTime) AND CONVERT(TIME, P.EndTime) /* AND CONVERT(TIME, C.EndTime) BETWEEN CONVERT(TIME, P.StartTime) AND CONVERT(TIME, P.EndTime) */ Please correct me if I'm wrong!
Here is my take on it. First, I would like to repeat that I believe that it is much easier to subtract 3 milliseconds from the end than to worry about the source of the subtle bugs due to BETWEEN usage. Another point I would like to add is the solution I propose is a bit more complicated than it needs to be because I am not sure whether the database in question is SQL Server 2008 or 2005. In case if it is 2005, TIME data type is not available, and therefore, some manipulation is needed to mimic TIME functionality. One of the ways to disregard the date part is to use the zero date as a base for datetime values, and this is exactly what I chose here. The Prices catalog table has 3 records per day (night, day and evening tariff), and just one record per day for both Saturday and Sunday. While 17 records is more than 4, this design allows some flexibility. For example, weekend days are different in the different parts of the world (customary Saturday and Sunday in Europe or US and Thursday and Friday in Middle East). -- create sample tables: create table dbo.Calls ( CallID int not null constraint PK_Calls primary key clustered, StartTime datetime, EndTime datetime ); go create table dbo.Prices ( DayNumber int not null, Period int not null, constraint PK_Prices primary key clustered (DayNumber, Period), StartTime datetime, EndTime datetime, Tariff smallmoney not null ); go /* insert 17 records in the Prices catalog table based on the following logic: days Monday through Friday have 3 periods, namely night, day and evening and weekend days have only one period. While this design requires few more records than would otherwise be required should the days be segregated based on the weekend / not weekend criteria, but allows a certain flexibility and ease of querying the data. */ declare @seed datetime; set @seed = 0; with records (N) as ( select top 15 (row_number() over(order by [object_id]) - 1) % 3 + 1 from sys.objects ) insert into dbo.Prices select 1, 1, @seed, dateadd(millisecond, 86399997, @seed), 1.50 union all select ntile(5) over (order by (select null)) + 1, N, case N when 1 then @seed -- start of night when 2 then dateadd(hour, 6, @seed) -- start of day else dateadd(hour, 18, @seed) -- start of evening end, case N when 1 then dateadd(millisecond, 21599997, @seed) -- end of night when 2 then dateadd(millisecond, -21600003, @seed + 1) -- end of day else dateadd(millisecond, -3, @seed + 1) -- evening end, case N when 2 then 4.00 else 2.00 end from records union all select 7, 1, @seed, dateadd(millisecond, 86399997, @seed), 1.50; -- insert some records into Calls table -- long call, resolves to day tariff bacause it started -- on Friday before the tariff switched to evening rate. insert into dbo.Calls select 1, '2010-10-01T17:43:10.000', '2010-10-02T08:25:01.000'; -- this call started and ended within day tariff range insert into dbo.Calls select 2, '2010-10-01T17:44:13.000', '2010-10-01T17:59:38.000'; -- this call started within evening tariff range insert into dbo.Calls select 3, '2010-10-01T18:02:25.000', '2010-10-01T18:37:52.000'; -- weekend call insert into dbo.Calls select 4, '2010-10-02T07:50:25.000', '2010-10-02T07:54:52.000'; Now the data can be queried like this: select c.*, p.Tariff, ceiling(datediff(second, c.StartTime, c.EndTime) / 60.0) Duration, p.Tariff * ceiling(datediff(second, c.StartTime, c.EndTime) / 60.0) TotalPrice from dbo.Calls c inner join dbo.Prices p on datepart(weekday, c.StartTime) = p.DayNumber and dateadd(day, datediff(day, c.StartTime, 0), c.StartTime) between p.StartTime and p.EndTime; -- resulting in this: CallID StartTime EndTime Tariff Duration TotalPrice ------ ----------------------- ----------------------- ------ -------- ---------- 1 2010-10-01 17:43:10.000 2010-10-02 08:25:01.000 4.00 882 3528.0000 2 2010-10-01 17:44:13.000 2010-10-01 17:59:38.000 4.00 16 64.0000 3 2010-10-01 18:02:25.000 2010-10-01 18:37:52.000 2.00 36 72.0000 4 2010-10-02 07:50:25.000 2010-10-02 07:54:52.000 1.50 5 7.5000 All of the above assumes EN-US, so Sunday is day 1 and Saturday is day 7. If other language is used then it may be different, for example select datepart(weekday, '2010-10-02T08:25:01.000') N; set language 'Svenska'; select datepart(weekday, '2010-10-02T08:25:01.000') N; returns 7 in first and 6 in second select. This means that the query inserting catalog records might have to be tweaked according to the settings used. Oleg
