Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
molden
Starting Member
4 Posts |
 Posted - 2013-06-05 : 05:52:55
|
| Hi all, I am trying to work out a phone bill and the way it is calculated is 1 minute = 3p, 2 minutes = 6p, 3 minutes = 9p and then 4 – 60 minutes it is capped at 10p but every 60 minutes it starts over again i.e. 61 minutes = 13p, 62 minutes = 16p, 63 minutes = 19p and 64 – 120 minutes = 20p I know in excel you could do (Assuming A1 is the minutes, and B1 the rate) =MIN(10,MOD(A1,60)*B1)+(10*INT(A1/60))but am struggling with the SQL.Any help would be great.ThanksMichael |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-05 : 05:57:43
|
| (callduration/60) * 10 + CASE WHEN (callduration%60) < = 3 THEN (callduration%60) * 3 ELSE 10 END------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-05 : 06:01:11
|
| DECLARE @A1 INT = 1 , @B1 INT = 3SELECT CASE WHEN ((@A1%60)*@B1) < 10 THEN ((@A1%60)*@B1) ELSE 10 END + ( 10*CAST(@A1/60 AS INT)) --Chandu |
|
|
|
|
|
|
Capping formula