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


ADVERTISEMENT

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

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

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

How Do You Convert A Number From Negative To Positive?

Jan 18, 2008

I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number? One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive. Does anyone know how this can be achieved?

View 5 Replies View Related

How Do I Delibrately Make A Positive Value A Negative Value

Feb 5, 2007



I want to convert a positive value to a negative value.

How do I do it...

View 3 Replies View Related

T-SQL (SS2K8) :: Summing - Adding A Negative And A Positive

Mar 20, 2014

I have a query that brings in a result from a table that I have to display as a negative, so I use ABS (tbname.fieldname) *-1

This works fine. (Learnt it here from a previous post.)

I have another query that brings in a result from the same table but as a postive. So that works fine too.

Now, I need to sum them together in a new query. If the total value is a positive, fine. If the total value is a negative, it displays as a positive.

My question. Is it actually possible to achieve what I'm trying to do?

Output example below - narrow result for testing.

idpt2 Value2SumQty
N1141307-80970 3.80 2
N1141S2G00009D070 26.16 2

The 3.80 is correct, it is a positive.

The 26.16 is incorrect as it is the output from sum 235.44 -261.6 so I need it to display as -26.6

View 9 Replies View Related

Treat Data Differently For Positive Vs Negative #

Oct 11, 2005

Ok, I have a table with IP addresses stored in decimal format using both positive and negative numbers.
The way that they are stored is:
Positve
1 thru 2147483647 = 0.0.0.1 - 127.255.255.255
Negative
-2147483648 thru -1 = 128.0.0.0 - 255.255.255.255
Conversion
positive
x/2^24 . (x/2^24)/2^16 . etc . etc
negative
(x+2^32)/2^24 . ((x+2^32)/2^24)/2^16 . etc . etc

I have a script which works by using UNION and the WHERE statements are x>0 x<0

My problem is I need to use a 3rd party app to run the script (McAfee ePO). McAfee does not recognize the UNION. My question is, can I acheive the same results as the script below, without using UNION.

SELECT ReportFullPathNode.FullPathName,
cast(cast(IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
UNION ALL
SELECT ReportFullPathNode.FullPathName,
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start+4294967296
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start<0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID

View 2 Replies View Related

Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero

Jul 27, 2007



I've a stacked column chart (vertical) both with positive and negative value.

I want an horizontal line only for the value = 0.

I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.

Can I show the gridlines only for the value 0?
Simona

View 1 Replies View Related

Group By Positive Or Negative Number In Table

May 7, 2008

Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.

I have a report I am creating where I need to group on whether a number is positive or negative. The number will never be zero. This number is a decimal (currency), if that makes a difference.

I have tried creating a formula field with the following expression:





Code Snippet

IIf(Fields!Num.Value > 0, 1, 0)

I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.

If you need any more information, please let me know and I will provide what I can. Thanks for your answers!

View 3 Replies View Related

SQL Server 2012 :: Pattern Matching (positive Across Records - Negative Within A Field)

Jan 6, 2015

I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:

UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance

I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same

Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082

The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.

Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7

good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.

View 9 Replies View Related

Reporting Services :: Make Bar Chart Color Red If Number Is Negative And Green If It Is Positive?

May 27, 2015

I have a 2012 report builder chart that has two series (one area chart and one bar chart) combined into one chart.  The problem I'm having is the bar chart has much smaller numbers than the area chart and the scaling is messed up.

Is there any way to put the bar chart on the right axis and keep the area chart on the left axis?  My goal is to increase the size of the bar chart in relation to the area chart.

Also, is there any way to make the bar chart color red if the number is negative and green if it is positive?

View 4 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

Count Number Of Positive Dollar Values In Query?

Feb 3, 2012

How do I count the number of positive dollar values in a query? I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money

View 4 Replies View Related

Data Warehousing :: DateDiff Function To Return Positive Value Irrespective Of Values Passed

Aug 7, 2015

I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.

Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .

The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.

Any query solution so that always positive value is returned.

View 3 Replies View Related

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

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

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

Positive Value

Nov 6, 2007


I have the following query

OutPut:

ChildName BillPayerName Cost
Mary Fred £50

Total Balance Blank

The problem is when I check for a value > 00.1 in the having clause the total balance column disappears.
But its ok if I use any other comparison operator.
I.e < 00.1.


Any ideas.


create procedure Rpt_pos1 --(@cmb1 as varchar(50)) with ENCRYPTION as

--declare @startdate smalldatetime

AS

set dateformat ymd

set datefirst 7


--loop through weeks

create table #getTable (thesite integer null,thechild integer null,cost money null)

insert #getTable select s.siteid,c.childid,0

from site s,child c

where --s.siteName=@cmb1

c.siteID=s.siteID


and c.active=1

and c.potential=0


insert #getTable

select s.siteid,c.childid,isnull(sum(round(bt.total,2,2)),0)*-1

from site s,child c,billingTotal bt

where --s.siteName=@cmb1

s.siteID=bt.siteid


and c.siteID=s.siteID

and bt.siteID=s.siteID

and bt.childid=c.childid

and c.active=1

and c.potential=0

group by s.siteID,c.childid


insert #getTable select s.siteid,c.childid,isnull(sum(round(p.amount,2,2)),0)

from site s,child c,payment p

where --s.siteName=@cmb1

p.siteID=s.siteid


and c.siteID=s.siteID

and p.childID=c.childID

and c.active=1

and c.potential=0

group by s.siteID,c.childid


create table #results (ChildName varchar(200) null,BillPayerName varchar(200) null, Cost varchar(20) null)

insert

#results

select c.forename + ' ' + c.surname

,case b.telhome when '' then b.Forename + ' ' + b.Surname

else b.Forename + ' ' + b.Surname + ' (' + b.telhome + ')' end

,case when sum(round(g.cost,2,2)) < 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) > 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) = 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15)) end

from

#gettable g

,child c

,billpayer b

,currency cu

,site s

where

cu.currencyid=s.currency


and s.siteID=c.siteID

and c.siteID=g.thesite

and c.childID=g.thechild

and b.billpayerID=c.billPayerID

group by

c.forename,

c.surname,

b.telhome,

b.surname,

b.forename,

c.siteID,

cu.symbol


having sum(round(g.cost,2,2)) > 00.1

order by

c.surname asc

insert #results

select '','',''

insert #results

select

'Total Balance'

,''

,c.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

from

#gettable g

,site s

,currency c

where

g.thesite=s.siteid

and c.currencyID=s.currency



group by

c.symbol


having sum(round(g.cost,2,2)) > 00.1

drop table #gettable

select * from #results

drop table #results

go

View 4 Replies View Related

Check For Positive Value

Oct 31, 2007

I need to be able to check & display people who have a positive value in the bills.
i.e > 1p.
On the billingtotal table i need to sum > 1p.



CREATE PROCEDURE rpt_PositiveAccountBalances

(@cmb1 as varchar(100)) WITH ENCRYPTION

AS

BEGIN

SELECT DISTINCT




Site.siteName,

Child.surname + ', ' + Child.forename AS ChildsName,

billPayer.surname + ', ' + billPayer.forename AS BillPayerName,

BillingTotal.total

FROM

Site

INNER JOIN


Child

ON


Site.siteID = Child.siteID

INNER JOIN


BillingTotal

ON


Site.siteID = BillingTotal.siteID

CROSS JOIN


billPayer

WHERE


site.sitename=@cmb1

END

View 3 Replies View Related

Nagative To Positive, Format

Apr 2, 2008

one value should be always positive in report

I just replace "-" to " " using Replace(Fields!sumamount.Value,"-","") and format is currency like $ 1234.12

but after replace I get 1234.1200 in report



format of cell is currency like $ 1234.12 and if I don't use replace - it's ok



how to remove two last zero and add "$" ? or may be other ideas how to show alway positive value ?

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

How To Round With Negative Numbers?

Jul 23, 2005

I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob

View 4 Replies View Related







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