Need help doing a calculation in SQL update statem

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help doing a calculation in SQL update statem

Author

Topic

mldardy
Starting Member

13 Posts

Posted - 2009-12-21 : 09:41:16

I need help with a calculation in a SQL update statement in a stored procedure. I am doing a insert-select combined statement by inserting into a table while selecting from another table. I then update to the table that I was inserting with fields that are not in the insert-select combined statement(hopefully this makes sense). On the update statement below I am trying to calculate the Mileage rate minus fuel times the total miles ran. So basically I need 1.3748-0.028 X DailyCSV.EMILEAGE. I am not even sure if it is possible but does anyone have any ideas on this.

01.insert into dailycsv(DailyCSV.DEST,     
02. DailyCSV.RGCODE, DailyCSV.RUNSEQ,<STRONG>DailyCSV.E_MILEAGE</STRONG>)    
03. SELECT   Distinct  DOMICILE,  ROUTE, SEQUENCE, RTMILES    
04.FROM         ['Summary - Route Master$']   
05.WHERE     (M = 'Y')   
06.  
07.update dailycsv set LPCODE='CMK', PERIOD=convert(varchar(6), getdate(), 112),   
08.ROUTEDATE=convert(varchar, getdate(), 112),   
09.<STRONG>MILEAGE_RATE='1.3748', E_FUELSURCHARGE='-0.028'</STRONG>, E_FLATCOST='0', E_FLATCOST1='0',   
10.E_FLATCOST2='0'

mldardy
Starting Member

13 Posts

Posted - 2009-12-21 : 10:24:29

Come on. SUrely there has to be someone who can help here. I tried setting a variable in my stored procedure like this and get an incorrect syntax error near '=':

SET y = (DailyCSV.MILEAGE_RATE - DailyCSV.E_FUELSURCHARGE * DailyCSV.E_MILEAGE)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-21 : 10:41:24

You waited 43 minutes until you lost patience that no one took time out of their workday to do your work? You provide no sample data, no explanation of what this means
</STRONG>. Is that an actual name of your column or are you using XML? By the way, why are you setting float values to varchars?

Jim

P.S. This is a MS SQL Server forum, is this what you are using?

Everyday I learn something that somebody else already knew

mldardy
Starting Member

13 Posts

Posted - 2009-12-21 : 11:11:37

Screw you smartass. Why even post if you can't offer to help. That is why I posted on this forum idiot is to seek help and I did offer code. Can you not read, scuba diver? Sounds you don't know anything anyway so it would have been better if you hadn't responded at all. A 46 year old that acts like a 12 year old. Brilliant.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-21 : 13:32:08

Yes that can be done, exactly how you described it on your first post.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-21 : 14:06:29

What you have described is not very difficult, but it wasn't very clear. Jimf was responding to that along with addressing your response of "Come on. SUrely there has to be someone who can help here.", because it is slightly rude (Also for future referance there is no need for your last post as well, it'll just make people not want to assist you).

Post your stored procedure, and I'll take a look at it. You can combine what you wanted to do into 1 sql statement, but it'll be easier if I see your code.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources