SQL Server >> Moving Negative Sign From Right To Left Value

by Sm9lIEsu » Sat, 20 Sep 2008 10:34:01 GMT


I have a SQL Server table (Month_Sales) which has two files (Net_Vales,
Est_Values) the table has numerous money values with the negative sign to the
right of the value.

Please help me create script to move minus from the right to the left side
of the
Net_Values and Est_Values fields.

I need to complete this task on a table that has approximately 14 million
records.

Thanks,

Before
Month_Sales
Net_Values 2845.69-,3,443.67,897.76-
Est_Values 3,435.54.76,876.65-,276.87

After
Month_Sales
Net_Values -2845.69,3,443.67,-897.76
Est_Values 3,435.54.76,-876.65,276.87






SQL Server >> Moving Negative Sign From Right To Left Value

by Roy Harvey (SQL Server MVP) » Sat, 20 Sep 2008 11:09:57 GMT


If you mean the table columns are of the MONEY datatype, then the sign
is not carried in front or behind. The location of the sign for
MONEY, or for the matter DECIMAL and any other numeric type, is simply
where it happens to be displayed by front end program.

However, looking at your sample data I suspect these columns are some
form of character string type (CHAR, VARCHAR, NCHAR, NVARCHAR).
Worse, I am wondering if the examples given:


are intended to show that one column contains three different numbers
separated by commas. The fact that Net_Values and Est_Values are
plural supports that idea. If that is the case it complicates the
process considerably, as this terrible practice always does. There
are some User Defined Functions floating around to assist in parsing
such strings, though I can't lay my hands on one at this moment. Once
parsed the simplest course would be to convert them to DECIMAL of an
appropriate size and precision, and then go through more pain to
re-assemble them into the impractical string again.

Roy Harvey
Beacon Falls, CT

On Fri, 19 Sep 2008 19:34:01 -0700, Joe K. <Joe





SQL Server >> Moving Negative Sign From Right To Left Value

by Sm9lIEsu » Sat, 20 Sep 2008 21:16:01 GMT


The values are presently listed in VARCHAR(100) format and will later be
conformated into MONEY.

Please help me create script to move minus from the right to the left side
of the Net_Values and Est_Values fields.


Thanks,






Moving Negative Sign From Right To Left Value

by Tom Cooper » Sat, 20 Sep 2008 21:49:35 GMT

As always, carefully test any update before running it in production, and
make sure you have a good backup.

Update Month_Sales
Set Net_Values =
Case When Right(RTrim(Net_Values), 1) = '-'
Then '-' + Left(Net_Values, Len(RTrim(Net_Values)) - 1)
Else Net_Values End,
Est_Values =
Case When Right(RTrim(Est_Values), 1) = '-'
Then '-' + Left(Est_Values, Len(RTrim(Est_Values)) - 1)
Else Est_Values End
Where Right(RTrim(Net_Values), 1) = '-'
Or Right(RTrim(Est_Values), 1) = '-';

Tom








Moving Negative Sign From Right To Left Value

by Erland Sommarskog » Sat, 20 Sep 2008 21:59:05 GMT

Joe K. (Joe XXXX@XXXXX.COM ) writes:

And the columns really hold a comma-separated list of values? In that
case it would be easier to convert to decimal directly. (The money
data type is not to recommend.)

Which version of SQL Server do you have?


Please answer the questions above first, so that we can answer the right
question.



--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx



Moving Negative Sign From Right To Left Value

by Roy Harvey (SQL Server MVP) » Sun, 21 Sep 2008 00:07:23 GMT

On Sat, 20 Sep 2008 06:59:05 -0700, Erland Sommarskog



First you have to move the sign, or apply it after the conversion,
since a trailing sign does not convert.


Some people want everything!

Roy Harvey
Beacon Falls, CT


Similar Threads

1. Measure rollup changes sign (positive values sum to a negative val

I am seeing an issue in one of my cubes where the sum returns as a negative 
number (int) but there are no negative values in leaf members.

The case I am seeing is rolling up days to month on an integer type measure. 
 All of the days are positive.  The rolled up sum for the month is negative.  
I can see no reason for this to happen.  There are no custom rollups, this is 
a straightforward calculation.

Any help or suggestions would be greatly appreciated.

2. LEFT + LEFT vs LEFT + RIGHT

3. how to move columns left when column to the right of columns is hi

I am working with a tablix in a matrix report.  When I hide a row group, it 
leaves an undsired white space where the column was that was hidden.  I would 
like all of the columns to the left of the hidden column to "move in" on the 
white space.  Is there a way to do this?

4. inline table-valued function as right side of left outer join take - SQL Server

5. inline table-valued function as right side of left outer join takes a very long time

I have the following scenario:

Stored procedure with 4 tables - using INNER JOINs.  Results come up in 1 
second (4400 records).

Add an additional table-valued function in the FROM clause - using INNER 
JOIN.  Again, the results take less than 2 seconds (2250 records).

Now I modify the last addition to make it a LEFT OUTER JOIN, so that I will 
retrieve the original 4400 records and values from the table-valued function 
where there is a match.  This one runs for over a minute and never gives me 
any results.  I finally cancel the operation in Query Analyzer.

Is there something about table-valued functions that won't work well in 
outer join situations?  Is there a better way to design this?

SELECT E.EncounterID, S.Name AS SubjectName, S.Phone, H.Name AS HelperName, 
U.DateOfContact, U.Notes
FROM tblEncounter AS E INNER JOIN tblSubject AS S ON E.SubjectID = 
S.SubjectID INNER JOIN tblHelper AS H ON E.HelperID = H.HelperID
LEFT OUTER JOIN udf_GetMostRecentHistoryForSubject(@pintSubjectID) AS U ON 
E.SubjectID = U.SubjectID 


6. Using left outer join to only return rows that have right side null values - SQL Server

7. Left Outer Join not showing records for left table when right blan

I asked this in the Access adp section and didn't get a response, so I am 
asking here as it appears to be more of a SQL question than just an ADP 
question.

I need to show the total number of hours entered for employees in a view with 
a group by, sum and where clause.  I need all employees to show up even if 
they have not put any details in for the time period  specified.  If I take 
the where clause out, I get 0 values for emplyees with no time entries, but 
as soon as I put the where clause back in I now only see emplyees that have 
entered time values.  The view is below, if anyone can help it would be 
greatly appreciated.



SELECT     dbo.tblEmployee.FullName, 
SUM(ISNULL(dbo.tblWorkDetail.WorkHours, 0)) AS Hours
FROM         dbo.tblEmployee LEFT OUTER JOIN
                      dbo.tblWorkDetail ON dbo.tblEmployee.LogOn = 
dbo.tblWorkDetail.Employee
WHERE     (dbo.tblWorkDetail.WorkDate BETWEEN CONVERT(DATETIME, '2006-10-30 
00:00:00', 102) AND CONVERT(DATETIME, '2006-10-31 00:00:00', 102)) OR
                      (dbo.tblWorkDetail.WorkDate IS NULL)
GROUP BY dbo.tblEmployee.FullName

Thanks

8. Convert character data with "-" negative sign to numeric - SQL Server DTS