How to make this code work correctly

Question

question

haddad asked Apr 18, '10

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

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

Answer 1 · 2010-04-19T12:09:00Z

Jason Cumberland answered Apr 19, '10

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

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

Answer 2 · 2010-04-18T16:45:58Z

Scot Hauder answered Apr 18, '10

Try it without the inner SELECT

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

10 |1200

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

question