| Author |
Topic |
|
spanki
Starting Member
8 Posts |
 Posted - 2002-05-12 : 08:13:40
|
| hoi.I have a column with the duration of a phone call.. such as 00:05:52. The costs are calculated 5cents/30 seconds for the first 5 minutes and then 5cents/1 minute there after. How would i go about writing a query for that, just need a start because I am stumped! |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-12 : 08:33:47
|
| select case when datediff(mi,@startdate,@enddate) <= 5 then (datediff(s,@startdate,@enddate)/30)*5else 50+((datediff(m,@startdate,@enddate)-5)*5) end--------------------------------------------------------------Edited by - Nazim on 05/12/2002 08:48:47 |
 |
|
|
spanki
Starting Member
8 Posts |
Posted - 2002-05-12 : 09:00:46
|
| what if the duration column is a char not "datetime"?? |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-12 : 09:36:49
|
| use Convert function to change it to dattime.Check BOL for Convert .HTH-------------------------------------------------------------- |
|
|
|
spanki
Starting Member
8 Posts |
Posted - 2002-05-12 : 11:32:10
|
| what would the string be if i had an hours value aswell! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-12 : 11:34:27
|
| If you CONVERT the string to a datetime value, it will preserve all parts of the time value, and be able to perform date/time arithmetic on it (using DateDiff, DateAdd, etc.) You should probably just change your table to store the column as a datetime instead of a string/varchar, then you can use Nazim's formula without needing to use CONVERT.Books Online has lots of documentation on datetime data and functions.Edited by - robvolk on 05/12/2002 11:39:13 |
|
|
|
spanki
Starting Member
8 Posts |
Posted - 2002-05-12 : 12:29:36
|
| ok. the thing is I just have a duration time, no startdate or end date! will this still work? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-12 : 12:53:46
|
Yes, if you modify Nazim's formula to something like this (I'm calling the column "duration"):SELECT CASE WHEN datediff(mi, '00:00:00', duration) <= 5 THEN (datediff(s, '00:00:00', duration) / 30) * 5 ELSE 50 + ((datediff(m, '00:00:00', duration) - 5) * 5) ENDDatetime columns always store a full date/time value, even if you only want the time portion. If you do not specify a date portion, it will default to '1/1/1900', which is fine since all of your call duration values will default to this (unless someone takes a call that lasts more than 24 hours)  In the above formula, since the '00:00:00' is the only portion specified, it will work properly. |
|
|
|
spanki
Starting Member
8 Posts |
Posted - 2002-05-12 : 13:39:26
|
| this works a treat! thankyou very much! |
|
|
|
|
Calculating