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