Solved

Combining 2 numerical results without doing a sum MS SQL 2005

Posted on 2011-03-14
14
198 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

708 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

18 Experts available now in Live!

Get 1:1 Help Now