question

haddad avatar image
haddad asked

How to make this code work correctly

DECLARE @cost smallmoney;

update CallDetails SET @cost =                    
CASE                     
            WHEN  HPno LIKE '017%' then (Select DATEDIFF(SECOND, 0, CallDuration) * 0.01)                    
            WHEN  HPno LIKE '093%' then (Select DATEDIFF(SECOND, 0, CallDuration) * 0.05)                     
            WHEN  HPno LIKE '066%' then (Select DATEDIFF(SECOND, 0, CallDuration) * 0.10)                    
            end                                
INSERT calldetails (DateAndTime,HPno,[Type],CallDuration,Cost,UserName)                    
values                     
('2010-08-09','0170000000','Call','00:00:05',@cost,'Kim');                    

Actually the code is working BUT when I executed it, It takes the Previous CallDuration and count it. plz click here and look at the Pic [CallDuration] and [cost].. note that each one second = 1 cent.

sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jason Cumberland avatar image
Jason Cumberland answered

I'm not clear on what you are trying to do here. It looks like you are trying to calculate the cost of the call BEFORE you insert the record into the database. If this is the case then you will need to change the first statement to not be an update but a SELECT/SET of the @cost variable using the values that you are ABOUT to insert. The statement you are using right now will use the data that is ALREADY in the table to assign the @cost variable.

DECLARE @cost smallmoney;            
            
select @cost = CASE             
                    WHEN  '0170000000' LIKE '017%' then (Select DATEDIFF(SECOND, 0, '00:00:05') * 0.01)            
                    WHEN  '0170000000' LIKE '093%' then (Select DATEDIFF(SECOND, 0, '00:00:05') * 0.05)             
                    WHEN  '0170000000' LIKE '066%' then (Select DATEDIFF(SECOND, 0, '00:00:05') * 0.10)            
                end                        
            
            
INSERT calldetails (DateAndTime,HPno,[Type],CallDuration,Cost,UserName)            
values             
('2010-08-09','0170000000','Call','00:00:05',@cost,'Kim');            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered

Try it without the inner SELECT

WHEN HPno LIKE '017%' then DATEDIFF(SECOND, 0, CallDuration) * 0.01

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.