Replace Negative Values

Sep 13, 2004

hi,

In the result of a function in my query, there are negative numbers.

How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?

thanks,



maarten

View 1 Replies


ADVERTISEMENT

Negative Values...

Jan 17, 2007

calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....

but i dont know to write this....any ideas???

View 9 Replies View Related

Negative Values

Jan 3, 2008



I have the following query, I would like to know if I can replace negative values with a zero and if a posiive value appears then leave the value alone?

SELECT TimeStamp, Tag1,Tag2,Tag3
FROM dbo.keller8

View 13 Replies View Related

Chart - Negative Values

Jan 10, 2007

Hi,

I´m working with chart type bar (Simple Bar) and i received four values from stored procedure, all values are negative numbers. The Reporting Service shows the values in the chart, but, doesn't show the "bars".... If one of four values will be positive, the report shows the values and the "bars" but if all values will be negative, the report shows just de values.

What do I do for the bars appear with four negative values ?

Thanks.

View 4 Replies View Related

Positive Negative Values In SUM

Apr 25, 2008





Hi,


I have table with a column which has both negative and positive values.

I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

Plase help me.


Ex: My Input Values are

column1
-186.5499257
-141.8985222
-95.3846883
-71.1359768
-69.39071172
30.44750844
40.33666032
53.81527872
98.2967252
112.948667

OutPut goes like This

Column1 Column2
-186.5499257 null
-141.8985222 null
-95.3846883 null
-71.1359768 null
-69.39071172 -564.36
30.44750844 null
40.33666032 null
53.81527872 null
98.2967252 null
112.948667 335.84

View 5 Replies View Related

DTS Import Negative Excel Values

Aug 8, 2007

Hello everybody,

I have a DTS package that imports excel spreadsheets.
The excel spreadsheet cells contain numbers based on vlookups. Now negative values appear in these cells as -999 but when loaded into the table they appear as (999).
Is there anyway way to configure it to just load -999? The tricky part is I don't have control over the format of the spreadsheet cell and the destination table field is varchar. I am hoping there is some way that the DTS interprets and picks up the value as it is displayed in excel.
Or do I have to stick with converting it manually by replacing '(' and ')' with nothing and appending '-'?

Thanks for any help.

View 1 Replies View Related

Negative Values For RowModCtr Col In Sysobjects Tbl

Jul 23, 2005

Could anyone, please, explain to me why I have negative values inRowModCtr column in sysobjects table? I have tested that after I updatestatistics the RowModCtr column is reset to 0. But why do I havenegative values in the first place? Thx.

View 2 Replies View Related

Reporting Services :: Bubble Map With Negative Values

Jul 29, 2015

I have a bubble map which contains negative values for the data field.

If I Preview a bubble map report within Visual Studio, I can get the expected result.

However, once I deploy this report I cannot get any data back from the report (see screenshot below)

Once Deployed and viewed in IE:

Why this is occurring?

Also, the data field options in the Layer Wizard are SUM and COUNT. Can these be changed?

View 3 Replies View Related

Visual Studio GUI Forcing Negative Increment Values

Feb 13, 2008

Hi,

I'm starting out with Visual Studio 2008 and SQL Server CE and have come unstuck at the first hurdle.

I have performed the below simple steps:
1. I have created a table with an identity field in it. The identify increment is a positive value (1). This works fine and I can populate data to the table directly.
2. I drag the table adapter from the dataset to a form to create a datagrid form.

When I run this form, the field for the identify column populates with negative values and when I go to save the record the application errors.

Note: this only happens with SQL Server CE. I do exactly the same with a server based db and get positive increments via the form.

The simple-ugly solution is to just set a negative increment in the table to match what is going on via the form, but this strikes me as a cop out. It is most likely that I am overlooking something obvious and I would be very appreciative if someone could point me in the right direction.

Thanks,
Julian

View 1 Replies View Related

Creating A Blank Row In A Table Between Positive && Negative Values

Oct 29, 2007

I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?

Thanks,
Steven

View 3 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

Reporting Services :: SSRS - Summing Up Positive And Negative Values Alone From Single Column?

Sep 18, 2015

MS SQL Server 2008 R2

I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.

View 8 Replies View Related

To Replace Number Values

Apr 6, 2006

'000000' is a string with 6 charaters

Requirement:

I want to Replace 0 with 1.


Replace 0 to 1 at position 3 output= '001000'

Replace 0 to 1 at position 5 output= '000010'

View 2 Replies View Related

How To Replace NULL Values With 0

Apr 1, 2013

I need to separate the data from one column in to two columns based on the transaction type TRAN_TYPE (C is credit, D is Debit)

TRAN-TYPE| AMOUNT
C 20.00
D 30.00
C 50.00

To do that I have this code:

select
CASE WHEN TRAN_TYPE = 'D'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as DEBIT,
CASE WHEN TRAN_TYPE = 'c'THEN CAST (ISNULL(amount, 0) as varchar (30)) end as CREDIT
FROM HISTORY

And my output is:

DEBIT | CREDIT
----------------
NULL | 20.00
------|-------
30.00 | NULL
------|-------
NULL | 50.00
------|-------

how to replace the null values with 0

View 6 Replies View Related

Find And Replace Values

Dec 7, 2007

I was wondering how to do a find and replace with SQL? Would I use a SET statement?

I need to find a specific value and replace all of the results with a different value.

Thanks for all responses.

View 1 Replies View Related

Replace The NULL Values

Dec 18, 2007

Thank u Chirag....

but i should display all the records of that table not only price column.

consider the column price in titles table in pubs database. If the price of any record is not defined it should be retrieved as -9999.

View 4 Replies View Related

Replace Values Of Null

Mar 4, 2008

select distinct
case when item is null then replace(item,null,'-')
else itemend
end as item
from itemstable

i want to replace all null values to '-'...but still i m getting null values..

syntax is correct still not getting results..

can anyone help?

thanks.

View 3 Replies View Related

How To Replace Values In Different Columns At The Same Time

May 14, 2004

HI,

I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".

NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.

HERE IS HOW THE PROC WRITTENED FOR IT GOES :-


DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR

EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END


THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.

BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'

FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-

CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5


NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"

CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?


AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .

PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.

View 6 Replies View Related

Transact SQL :: Replace Multiple Values Without Looping

Jul 13, 2015

I need one query:

create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>>  Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>>  Text Text Text <<Salary>>  Text Text Text')

[Code] ....

Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>>  Text Text Text <<Salary>>')

I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return

[Code] ....

View 5 Replies View Related

Single String Replace Logic - 0 Needs To Be Ignored For Decimal Values

May 10, 2012

The string column value looks like as below. Each value has a size of 15 withing a string

'2.2020 30 4.0000'

The column value should match with user input as below. The result should show equal when it is compared. Currently, it results not equal since it is a string comparision. The last digit '0' needs to be ignored for decimal values.

'2.202 30 4.0'

I need to handle the decimal values in such a way, if staring value with '.' and last digit is 0 then replace with space ''. So, it should look like

'2 2 2 30 4 ' = '2 2 2 30 4 '

When this string is compared, it results in EQUAL.

I tried the below logic, which even replaces the integer value like 30 to 3 and 3000 to 3 and results in equal which is incorrect.

RTRIM(REPLACE(REPLACE(RT1.rate,'''+@DOT+''','''+@S PACE+'''), '''+@ZERO+''', '''+@SPACE+''')) = '''+REPLACE(REPLACE(@Rate,'.',' '), '0', ' ')+''' '
Ex:'2.2020 300 4.00' = '2.20200 30 4.0'

After replace, string looks like

Ex:'2 2 2 3 4 ' = '2 2 2 3 4 '

It results as EQUAL which is incorrect. I need only decimal value to be replaced not integer.

I am looking for a single string replace logic.

View 3 Replies View Related

SQL Server 2012 :: Replace Multiple Values Without Looping

Jul 13, 2015

I need one query...

create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task

[Code] ....

Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')

I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as

[Code] ....

View 7 Replies View Related

REPLACE Funtion For Multiple Values In SP/Reporting Services

Mar 4, 2008

Running into a problem with trying to pull multiple values in a stored procedure.
Ran Profiler for trace on what variable is coming back as when running in Reporting Services.
Profiler shows this:


exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier'

Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks.
What I have:


Set @Product=

Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))


This is probably a little too much coding but I've tried several ways.

In the SP the code is as follows:


CREATE PROCEDURE [dbo].[apGetCompanysByRep]

@Magazine varchar(6) = null

, @DirectsMagazine varchar(6) = null

, @Category varchar(50) = null

, @SubCategory varchar(50) = null

--, @FirstName varchar(30)

, @LastName varchar(30)= null

, @Product varchar(1100)

AS

declare @SQL varchar(2000)

Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))



set @SQL = '





Thanks for any assistance anyone can give.

View 9 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Characters Based On Table Values

Dec 12, 2014

There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]

[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000

For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400

The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...

DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';

SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]

SET @Total='select '+@formula

EXECUTE sp_executesql @Total;
PRINT @Total;

View 3 Replies View Related

Updating Trigger Problem : Trying To Replace The Values From The Upadate Statement

Apr 25, 2007

I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.



Here's my situation



tst_update

ID int

Col1 varchar(10



I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.



Can someone help.



Thanks,

Ashish

View 6 Replies View Related

SQL Server 2012 :: How To Replace Multiple Values In Single Select Statement

Aug 18, 2015

how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.

DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))

[code]....

View 7 Replies View Related

Integration Services :: Replace Blank Strings Values To NULL And Convert To Integer Data Type

Oct 5, 2015

I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.

View 5 Replies View Related

QA Returning Negative #s As Positive

Mar 30, 2004

Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.

View 4 Replies View Related

Inserting Negative Number?

Sep 30, 1999

Hi

I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?

TIA

JDJ
jdj@gvd.dk

View 2 Replies View Related

Sp_spaceused With Negative Numbers?

Mar 1, 1999

Hello:

I have gotten the following information from sp_spaceused:

----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------

I unfortunately since this is for a test database that I have put the data and log on the same device.

My questions are:

1) Why do I have negative numbers?

2) Should I be concerned about this?

3) for a live database can I drop the log and put the log on another device? What do I need to do?

4) has anybody seen this before?

5)what can I use to get accurate numbers?

THanks. Any information will be greatly appreciated.

DAvid Spaisman

View 1 Replies View Related

Device Sizes Are Negative

Mar 30, 1999

Ok, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?

View 2 Replies View Related

Negative Device Size

Oct 15, 1998

SQL Enterprise manager is reporting my database device size to be a negative number.
Primarily, this is the case for the log files, but also occurs on a few of the .dat files.

Is there a correction? Is this a display error, or an actual size conflict/

View 3 Replies View Related

Negative Unused Space

Mar 12, 2004

I wrote simple script to check space used by tables:

CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"

SELECT * FROM #SpaceUsed

SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed

DROP TABLE #SpaceUsed


and one of results looks strange to me:

TableName NoOfRows Reserved Data Index_Size Unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB



Anyone know reason of such result (negative value of unused space)?

View 3 Replies View Related

Remove Negative Signs

Jul 25, 2007

Hi

I have values in col_A (smallint) as follows.

1
-4
3
6
-7

I want the negative to be remove and result as follows.

1
4
3
6
7

Please advise how?

Thanks
Jawad

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved