Solved

Combining 2 numerical results without doing a sum MS SQL 2005

Posted on 2011-03-14
14
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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
 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 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