T-SQL (SS2K8) :: Display Values Up To 1 Decimal Without Function?

Sep 11, 2014

I am having values as below:

99.87
99.96
8.67

And my output should be as:

99.8
99.9
8.6

How can I do this

View 9 Replies


ADVERTISEMENT

Reporting Services :: Display Only Two Values Before Decimal Points

Sep 24, 2015

In my report I am having one calculation filed, in that field I want to display only two values before decimal point.

For example, if I am having calculation field value as 3456.54, I need to display as 35.54 or 34.54.

View 3 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?

Sep 18, 2015

I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:

OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C

So the logic is as follows:

I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.

Not sure how to create my Select here since the values are in the same table

View 2 Replies View Related

T-SQL (SS2K8) :: How To Pass Array Of Values As Parameter Of Function

Mar 4, 2014

I am trying to find out a way to pass an array of pbaseid's to a function.I have a function defind something like this

Fport(@portfoliobaseid, @reportingcurr, @rowno,@todate)
(
returning a table
)

trying to execute it like this but its giving error related to subquery must return single value

select * from Fport((select pbaseid from dbo.pbaseid),'us',1,'12/31/2013')

I think i need to find a way to pass this pbaseid one by one but i dont know how to do this ...

View 1 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

How Can I Always Display 2 Decimal Places?

Aug 24, 2005

In MS SQL Server 2000 T-SQL, how can I always display 2 decimal places?

e.g.

I wish to display

25 as 25.00
or

43.6789 as 43.68.

Any help would be greatly appeciated.

View 2 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

T-SQL (SS2K8) :: Showing Decimal Places

Apr 2, 2015

I need to find the ones ending in a decimal. So if you look at the list below, I need a script that will return the value 001. ,002., 004. ..

001
001.
002.
003
004
004.

View 5 Replies View Related

T-SQL (SS2K8) :: How To Select Decimal Type Field

Feb 2, 2015

I have a table with fields:

Id int
Pay numeric(19,3)

value records:

id pay
1 1.000
2 2.250
3 3.445
4 6.000

I want select of table to form:

id pay
1 1
2 2.25
3 3.445
4 6

if value decimal pay field Greater of zero then

value select= value field
else
delete value decimal and show

View 3 Replies View Related

T-SQL (SS2K8) :: Rounding Decimal To Nearest Integer Value?

Jun 28, 2015

how to round the nearest value after round of decimal and return integer.

declare @data decimal(18,2)
set @data = 5.55

-- output

select '5'
set @data = 5.58
-- output
select '6'

View 3 Replies View Related

T-SQL (SS2K8) :: Counting Decimal Places In A Float Field

Jul 3, 2014

I have a table with three columns: UniqID, Latitude, and Longitude.

I need to write a query to identify when the latitude has more than 6 decimal places past the decimal. Same with Longitude. Values in these attributes can be a negative number. These fields are FLOAT.

View 7 Replies View Related

T-SQL (SS2K8) :: Getting Undesirable Trailing Zeros After Casting To Decimal

Sep 7, 2014

The following line of SQL returns the first screen shot below.

((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS 'Gross Profit %'

I now want to limit the decimal places to two (2), and one might think that using a CAST operation here is the solution, as follows.

CAST(((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS decimal(15,2)) AS 'Gross Profit %'

However in actuality I am still seeing too many decimal places, but the extra ones have changed to ZEROS!

Ideally I would like to have no extra trailing zeros. How to achieve this?

View 2 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

SQL AVG Function Of A Decimal Column

Jan 13, 2005

Hi,

Trying to get a dataset. My select statement is "SELECT Com_year,Avg(GPA) FROM Pilot_Stats WHERE com_source = 0 GROUP BY Pilot_Stats.com_year ORDER BY Pilot_Stats.com_year"

Where Com_Year is an integer (ie 1984, 1986, etc)
Where GPA is a decimal (ie 3.4, 3.0, etc)
Where com_source is an integer

This returns this error when I try to fill the dataset:

Decimal byte array constructor requires an array of length four containing valid decimal bytes.

For some reason it's hanging up on Averaging this Decimal column for some reason...

Anyone have an idea why? Thanks for any help!

View 6 Replies View Related

How To Get Result In Decimal Using AVG Function

Aug 8, 2007

Hello Sir
I am working on a Teacher Evaluation Project. In my database I store results from 1-5 as evaluation indicators. I apply AVG() function on the result column. The result of the query is in integer values (i.e) 4, 3 2 or 5. I want the resutl up to two decimal places. How can i write the query to get the result in decimal form?

Shahbaz Hassan Wasti

View 3 Replies View Related

T-SQL (SS2K8) :: Aggregate Monthly Values From Interval Values?

May 14, 2014

I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.

Currently it is displaying.....

MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real

-===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL

Output should display as

MeterId Period Reading

4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15

View 4 Replies View Related

T-SQL (SS2K8) :: Display Numbers 0-9

Aug 8, 2014

I am having query where i am calulating datediff value, but i want to display resultser as below:

if datediff = 4 then resultset would be
0
1
2
3
4

if datediff = 9 then
0
1
2
3
4
5
6
7
8
9

how can i implement this?

View 2 Replies View Related

T-SQL (SS2K8) :: Display Dates For A Given Value?

Mar 4, 2015

I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:

Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14

I want to display the following:

Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20

Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.

View 2 Replies View Related

T-SQL (SS2K8) :: Get Script To Output Only 2 Decimal Places For Amount Column From Select Statement?

Jul 22, 2015

I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?

Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',

[code]....

View 2 Replies View Related

Storing Decimal Values

Aug 7, 2007

Hi,

I'm loading millions of rows into a database table. This data contains a lot of dedcimal values of 4 or 6 decimal places somtimes more.

I've found that storing them as floats is inaccurate, and I'm storing as decimals of the appropriate length.

I'm concerned whether this is the right way to do this ?

Can anyone make any suggestions, perhaps an article that I should take a look at ?


Sean

View 2 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:

TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445

OUTPUT

ProductIDSeqNoBalance
111510
121445

View 3 Replies View Related

SQL Server 2008 :: Using Decimal Columns In SUM Function

Jul 21, 2015

Recently I have come across a requirement where i need to design a table.

There are some columns in table like below with DECIMAL Datatype:

BldgLength

BldgHeight

BldgWeight

Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.

Now as per MSDN,

Precision => 1 - 9
Storage bytes => 5

so i can create column as:

BldgLengthDECIMAL(6,2) DEFAULT 0

OR

BldgLengthDECIMAL(9,2) DEFAULT 0

Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.

So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.

So my question is what should be value of DataType for above column ?

View 8 Replies View Related

Decimal Values Get Truncated When Using SqlDataReader In C# .net

Feb 17, 2004

The values that are being fetched from the database are not being read as is , when there are decimal values with 10 or more digits after the point, the values are truncated (approximated) to 8 digits

The approximation is not consistent in all the cases
Some times depending on the numbers for example 0.434000001 is truncated to 0.434.

This is happening when i read the values from the sql database using the SqlDataReader.GetValue method into an ArrayList in C# .NET

View 1 Replies View Related

Check If Varchar Has Non 0 Values In The Decimal?

Apr 25, 2008

Hi All,

I have a table Saleshistory with 89481412 records. I have a field Sales of nvarchar type, all the records in this field are numeric, and the records are with 2 decimal places. I expect all the records to be ending at .00 (for example 5.00,345.00,25.00), but I want to verify if something is for example 5.6 etc.

So in short I want to check if any Salesrecords have more than zero values in the decimal. How can I check this.

Please help.

Thanks,

Zee

View 8 Replies View Related

Decimal Places On Money Values

Sep 6, 2007

Hi

I have an SQL search that is converting two values to type money. I want it to show two digits after the decimal point but am getting inconsistent results. The first value is as follows:

tblInventoryItem.itemcost as originalcost (the column is datatype money)

This displays correctly i.e. 2000.00 or 150.70 etc

The second value is this:

tblInventoryItem.itemcost + tblUpgrades.ItemCost as totalcostincupgr (both columns are datatype money)

But this displays as 2000 or 150.7

How can I get the second value to show two decimal places even when the digits are zeros?

Thanks!

View 3 Replies View Related

Problem Converting Decimal Values

Apr 25, 2007

Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.

Any help on this ? tnks in advance

View 5 Replies View Related

How To Know If A Column In A Table Has Decimal Values

Apr 4, 2008



Hi Guys,

I want to find out if a ceratin column has values with decimal figures populated? for example say column A has values

10.00
20.00
30.00
50.01

in this case the query should let me know that there is one figure (50.01) with a actual decimal value. So the results of the query should be 1 (since one record has decimal figures <>.00)

Cananyone help

Sonny

View 10 Replies View Related

T-SQL (SS2K8) :: Combine Two Tables To Display Iteratively

May 13, 2014

I am looking for a way to combine the following two tables to get a result set that would look like this:

1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red

[Code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Display Result In Required Order

Jun 17, 2014

I have an issue while display the result in the required order. How to get the required output.

Code :

USE tempdb
GO

IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL
DROP TABLE #VersionFormat_tbl
CREATE TABLE #VersionFormat_tbl
(
[FormatID] [smallint] NOT NULL,
[Description] [varchar](50) NULL,

[Code] ....

Present output :
fileExtension FormatID Description fileExtension versionFormatTypeId
txt 1 Text txt 1
html 2 HTML html 1
xml 3 XML xml 1
pdf 4 PDF pdf 1
xls 5 Excel xls 1
doc 6 Word doc 1

Required output:
fileExtensionFormatIDDescriptionfileExtensionversionFormatTypeId
html2HTMLhtml1
xls5Excelxls1
doc6Worddoc1
pdf4PDFpdf1
txt1Texttxt1
xml3XMLxml1

View 5 Replies View Related

T-SQL (SS2K8) :: Query To Display All Data Connected To ID In One Row

Oct 23, 2014

I have a table that I am trying to query to display all data connected to a id in one row. What is the best way to achieve this?

Something like this:

ID| CRS1 | CRS2 | CRS3| CRS4 ....
1 | A101 | A102 | A103 | A104 ....

CREATE TABLE Class
(
id int,
yr int,
trm varchar(2),
crs varchar(30)

[Code] .....

View 3 Replies View Related

T-SQL (SS2K8) :: Find Records In A String And Display

Jul 6, 2015

I am having a column which is ntext and contains below type of data.

{ "running":"true", "all":{ }, "GPAs" : [ {"type":"item", "alias":"i_11111"} ,"GPA":"1.75" }, {"element": {"type":"item", "alias":"i_11111"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_33333"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_44444"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_22222"} ,"GPA":"1.42" }, {"element": {"type":"item", "alias":"i_55555"} ,"GPA":"15" }, {"element": {"type":"item", "alias":"i_66666"} ,"GPA":"10" } ]}

above is a single row. There are many such rows are present in the table. Now I want to find all alias and GPA and display as below.

idGPA

111111.75
111111.43
333331.43
444441.43

In oracle it can be done by using REGEXP_SUBSTR. But how can we do this in SQL?

View 1 Replies View Related







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