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
ADVERTISEMENT
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
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
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
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
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
Feb 5, 2007
I want to convert a positive value to a negative value.
How do I do it...
View 3 Replies
View Related
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
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
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
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
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
Apr 10, 2015
I have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
zscore = Range("J" & mytel99)
rc.Open "INSERT INTO dbo.QHSE_3rdline_history (RvA_Nr, RvA_Letter, Afdeling, Datum, Organisator, NummerRO, Eenheid, ALC, RO, Interpretatie) " _
& "VALUES ('" & rva1 & "', '" & rva2 & "', '" & Range("B" & mytel99) & "', '" & datum & "', '" & Range("B1") & "', '" & Range("B2") & "', '" & Range("G" & mytel99) & "', '" & Range("H" & mytel99) & "', '" & Range("I" & mytel99) & "', '" & Range("K" & mytel99) & "')", con
rc.Open "UPDATE dbo.QHSE_3rdline_history SET [Matrix] = '" & Range("C" & mytel99) & "', [Component] = '" & Range("D" & mytel99) & "', [Zscore] = '" &
[code]....
how to get negative data in a numeric SQL table?
View 1 Replies
View Related
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
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
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
Sep 28, 1999
Help
Why is the unsed space is negative number? Whould that affect the database? How do you fix the negative number?
If 'out of lock' happen, how do you check at what level the object was/is locked and how many locks is needed to configure?
Thank you ahead of time
View 2 Replies
View Related
Jun 5, 2008
I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number.
Here is my SQL statement:
SELECT
dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime)
AS TimeDiff
FROM dbo.FDC_Trips
For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.
How can I get this query to work with times before and after midnight?
View 2 Replies
View Related
Feb 21, 2008
I am representing negative number within parenthesis , but as I am working on reporting service, it is possible to make the exact alignment of the number and parenthesis.
here is one example ....
when i format number with #,##,0;(#,##,0) i am getting output in a format in subsequent rows like
123,345
(145,567)
How can I make this alignment like
123,345
(145,567)
where the parenthesis will be outside the number space keeping in mind the alignment is in right .
any help will be apriciated.
thanks
Aru
View 1 Replies
View Related
May 20, 2000
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.
Thanks.
View 1 Replies
View Related
Feb 4, 2008
I'm working with a horrible database! The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format. I need to convert the field to a decimal (9,2) but I can't because of the negative sign.
Example:
Balance
00000000
0000-413
0000-913
00-10913
00009526
I don't even know where to start to convert this. Any help is appreciated!
Thanks.
View 10 Replies
View Related
Oct 4, 2013
I have a table with debits and credits columns
Credit Debit
-7600
0080
90-30
I am transferring the debits and the credits in to one column in another table
Type Amount
C-76
D80
C90
D-30
And I am facing two problems one is the negative numbers don't transfer and also when I have debit and credit on the same line they are not transferring.
Select Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then 'D' Else 'C' End,
Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then Round(A.[Debit],2) Else Round(A.[Credit],2) End
FROM Tble A
View 5 Replies
View Related
Oct 13, 2013
I have the following data:
POL# POL_EFF_DATE
123 1-1-2012
123 1-1-2012
123 1-1-2012
123 1-1-2013
123 1-1-2013
456 1-1-2012
456 1-1-2012
456 1-1-2013
456 1-1-2013
I want to add sequence number to each group with same Pol# and Effective Date like this:
POL# POL_EFF_DATE Seq
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2013 2
123 1-1-2013 2
456 1-1-2012 1
456 1-1-2012 1
456 1-1-2013 2
456 1-1-2013 2
is there a set based function to accomplish this in SQL 2008?
View 2 Replies
View Related
Mar 14, 2008
How would I get the rownumber() for a Group Footer in report designer not in my query?
I put the syntax rownumber(nothing), but it is giving me the row numbers for the detail section.
Any help would be appreciated.
View 1 Replies
View Related
Apr 22, 2008
Like many others, I have a large report that groups on invoices, and does a page break between each invoice. From reading other posts in this forum, I was able to reset the page number on the grouping, however, there are some things that I need to display only on the last page of each invoice, but I don't see how I will be able to determine when I'm on the last page for a particular invoice. Can I somehow determine up front the number of pages for a group so I know when I'm at the last page?
Any help is greatly appreciated!
View 1 Replies
View Related
Jun 11, 2008
SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket
DESTINATION TABLE
ID_____SEQ______COMMENT
123_____1_______I am joe
123_____2_______I am programmer
124_____1_______I am wang
124_____2_______I am programmer
124_____3_______I like cricket
can somebody please advise the easiest way to do this in sql 2000?
View 3 Replies
View Related
Aug 4, 2013
I have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
View 3 Replies
View Related
Sep 12, 2006
I have 1 table named BookMe,
with fields
$Date, $Div, $Name
I want to select the top N number of Div's (ie highest Div value) per distinct Date.
i know how to get the distinct date like this:
SELECT DISTINCT $Date FROM BookMe ORDER BY $Date
How do I combine a TOP keyword to get the TOP n rows per distinct date???
Thx!
View 5 Replies
View Related
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Apr 18, 2000
Is there any way of configuring this parameter?
A work around?
Or have I reached the limits of scalablilty on this issue?
Please assist me
Thanks
View 1 Replies
View Related
Aug 30, 2013
I try this sql query:
Code:
SELECT
[NAMES], [NUMBER]
FROM
[CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
GROUP BY
[NAMES], [NUMBER];
The output is:
Code:
NAMESNUMBER
BENCORE S.R.L.44644
BENCORES.R.L. 44644
I need instead this other output:
Code:
NAMESNUMBER
BENCORE S.R.L.44644
View 2 Replies
View Related
Mar 23, 2015
I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)
CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)
[code]...
View 0 Replies
View Related
