Solved

Combining 2 numerical results without doing a sum MS SQL 2005

Posted on 2011-03-14
14
199 Views
Last Modified: 2012-05-11
I want to combine 2 numerical results from different columns into one result that i save in a third column,

the stament i have at the moment is:

update myTable set myCol = 2.55 where something = something

i want to do something like this

update myTable set myCol = 2.55 + / + 19 where something = something
 and get this result
2.55/19
when i try that above, i get an error
0
Comment
Question by:QuinnDester
  • 7
  • 5
  • 2
14 Comments
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127043
try:-

update myTable set myCol = cast(2.55 as NVARCHAR(50)) + '/ '+ cast( 19 as NVARCHAR(50))  where something = something

Open in new window

0
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 500 total points
ID: 35127054
you may also do:-

update myTable set myCol = cast(col1 as NVARCHAR(50)) + '/ '+ cast(col2 as NVARCHAR(50))  where something = something

Open in new window


where col1 and col2 are field names.
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127079
sorry i wrote an extra empty space in '/ '
Correct query:-

update myTable set myCol = cast(2.55 as NVARCHAR(50)) + '/'+ cast( 19 as NVARCHAR(50))  where something = something

update myTable set myCol = cast(col1 as NVARCHAR(50)) + '/'+ cast(col2 as NVARCHAR(50))  where something = something

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35127132
Hi,

if its hard code value then you no need to do cast
this will do

update myTable set myCol = '2.55/19'
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35127147
it is complicated slightly by using dynamic sql, what you have given me works, but putting it into dynamic sql it isnt saving anything after the col1, but is saving the col1 ??


here is what i am using

'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' +
cast(@col as varchar (max))+','+ cast(@updatedcount as varchar (max)) +')  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = cast(@avgstatus as varchar (max))+''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127181
why are you using double quotes(") ?
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35127184
the reason for using dynaimc sql, is the col name on one of the tables is decided dynamicaly, there is no way around that that i know of.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35127185
Hi,

can you post your full code?
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35127186
double quots are so i get a single quote when the dynamic sql is executed
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35127195
that is the full statment conserned, but if you think it will help the full sp is below
USE [MainDB]
GO
/****** Object:  StoredProcedure [dbo].[UpdateSystemData]    Script Date: 03/14/2011 09:40:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[UpdateSystemData](
@name varchar (Max),
@status varchar (Max), 
@player varchar (Max),
@alliance varchar (Max),
@Position int, 
@Galaxy int,
@System int,
@HasMoon bit, 
@date datetime, 
@Coordinates nvarchar (50)
 )
AS
BEGIN
if @player not like 'Joshua Calvert'
begin
 update planets set invalid = 0, coordinates = @Coordinates, timelastupdated = @date, [name] = @name, player=@player, alliance=@alliance, hasmoon=@HasMoon 
 where galaxy=@Galaxy and [system]=@System  and position=@Position

declare @col varchar (max)
declare @sql nchar (500)
declare @thishour int
declare @lastupdated datetime
declare @updatedcount int
declare @avgstatus dec(12,2)

set @thishour = (select top 1 DATEPART(hour, @date))
set @lastupdated = (select lastupdated from activity_history where Coordinates = @Coordinates)
set @col = (select hour_words from convert_hour where hour_numbers = @thishour)


if @lastupdated <= dateadd(hour, -1, getdate()) or @lastupdated is null
begin
	set @updatedcount = (select updatedcount + 1 from activity_history where  Coordinates = @Coordinates)
update activity_history set updatedcount = @updatedcount, LastUpdated = getdate() where  Coordinates = @Coordinates
	set @sql = 'update activity_history set '+ 
	cast(@col as varchar (max))+ ' = '+ cast(@col as varchar (max))+' + '+ cast(@status as varchar (max)) +' 
where Coordinates =  '''+ cast(@Coordinates as varchar (max)) +'''declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' + 
cast(@col as varchar (max))+','+ cast(@updatedcount as varchar (max)) +')  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = cast(@avgstatus as varchar (max))+''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''


exec (@sql)

--declare @sql1 nchar (500)
--set @sql1 = 'update planets set [status] = (select dbo.avg1('+cast(@thishour as varchar (max))+',updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')'
--exec (@sql1)
end
else
begin
set @sql = 'declare @avgstatus decimal(12,2) set @avgstatus = (select dbo.avg1(' + 
cast(@col as varchar (max))+' ,updatedcount)  from activity_history where Coordinates = '''+cast(@Coordinates  as varchar (max))+''')

update planets set [status] = @avgstatus where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''


exec (@sql)

end

end
END

Open in new window

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127215
this should work:-

update planets set [status] = @avgstatus where Coordinates = cast(@Coordinates  as varchar (max))

Open in new window

0
 
LVL 3

Author Comment

by:QuinnDester
ID: 35127241
that part in the in the else statment works fine, its the if statment where i have tried to add the extra info to that statment where it not working...
0
 
LVL 3

Accepted Solution

by:
QuinnDester earned 0 total points
ID: 35127369
got it...

Thanks for your help

this works, though the only difference is a space in a place where i though a space wouldnt matter, obviously it does :)

'update planets set [status] = cast(@avgstatus as varchar (max)) +''/''+'''+ cast(@updatedcount as varchar (max)) +''' where Coordinates = '''+cast(@Coordinates  as varchar (max))+''''
0
 
LVL 3

Author Closing Comment

by:QuinnDester
ID: 35170629
my solution fixed the problem
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now