SQL Query Needs Decimal Place

Nov 12, 2005

Hello all,I have an SQL query which retrieves a COUNT number from 2 different tables, and i want to do a division with botht he COUNT data retrieved. Trouble is I can't get it in the format that I want, my SQL query is as below :-SELECT ROUND( ((T1.Present/T2.Total ) * 100), 2) FROM(Select Count(Date) as Present from Attendance WHERE Month(Date)=12 AND Status=1) T1,(Select Count(Date) as Total from Attendance WHERE Month(Date)=12) T2The trouble here is that the result should be as below: T1.Present = 3T2.Total = 5  T1.Present/T2.Total = 3/5 = 0.6The final should be 60 after divided by 100But i am getting a zero as my result, even when I don't multiply the number by 100, the division result is still zero. I am guessing it is a conversion problem. Could anyone please offer me any advise on how to get the final result in the format I want?

View 2 Replies


ADVERTISEMENT

Delete Commas And Decimal Place

Dec 19, 2007

How can I convert a field with commas, cents and decimal to a numeric without commas and decimals and cents...?

Sample nvarchar values to desired output (integer whole value, truncated or rounded doesn't matter):
0.00 to 0
14,000.04to 14000
139,234.31to 139234
14,017,300.00to 14017300

View 2 Replies View Related

Data Type For Number With 1 Decimal Place

Apr 16, 2008

I have just loaded my db table from an excel file using the import wizard. Prior to the import, I set my data types and in the field that I need a number with 1 decimal place I chose a decimal data type.

Made since to me

However, now my numbers do not have the decimals.
Please help.

View 3 Replies View Related

Transact SQL :: How To Convert Numeric Data Type To A Varchar With No Decimal Place

Aug 31, 2015

So my data is delivered as numeric(9,2)...like 100.00. I have to extract that data and store it as a varchar 0 filled without the decimal place...like 0000010000///I tried the following and it did not work...

RIGHT('000000000'+CONVERT(VARCHAR,[EODPosting].[Amount]),10),

View 8 Replies View Related

Is There A Place Where I Can Find The Events That Takes Place In Sql Server?

Jul 20, 2007

Is there a place where i can find events that takes place in the sql server? Like adding data to a database or something like that....



Regards

Karen

View 4 Replies View Related

In-Place Upgrade Query

May 10, 2007

Hi,

While we choose in-place upgrade for upgrading to SQL Server 2005 from SQL Server 2000, does it actually change the code in Stored Procedures/functions also?



e.g. if I have *= in some old procedure does it change it to INNER JOIN while migrating?



Please help me on this ASAP.



Regards,

Rahul

View 1 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

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

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!



I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

Show The Decimal Part In Query

Jun 19, 2008

Hi,In the following query the calculator gives value 3.3  but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks 

View 3 Replies View Related

Set Up Query To Return Only 2 Decimal Places?

Mar 3, 2005

Hi. I have a query that returns several averages and percentages. Is there some way to set up the query so the results only go to 2 decimal places? Here is a sample of the query I am using:
Code:

$query = "SELECT COUNT(deal_id), SUM(vs), SUM(vs)/COUNT(deal_id)*100, AVG(fin) FROM sales GROUP BY salesperson";



It works great,except the results are several decimal places long, and I need it to be only 2.

Any help appreciated.
Cori

Never mind, I found it muhself....

FORMAT(AVG(fin),2) works perfectly!!

View 5 Replies View Related

Formatting Decimal Places In A Query In MS SQL

Jan 14, 2004

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.

View 5 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007



I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007





I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.



It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???



Thank you for your help!

Anna





View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,




I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101




Please help!



Thanks

Mirudhu

View 4 Replies View Related

Float Type Steals My Decimal Points And Money Type Kills My Query

Mar 28, 2008

Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1

If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.

anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?

View 2 Replies View Related

Best Place To Put TempDB

Nov 16, 2001

Hi,

Can anyone give me any input on this. Recently TempDB one of my production server came down because tempDB got so big that it chewed up all the space in it's drive. My TempDB was in drive C:, where the Operating system and the rest of the SQL systems databases are(msdb,model,master). The actual production data are located in another logical RAID 5(Drive E:) Drive. I want to prevent the problem from happening again. Is it wise or does it degrade performance if i move TEMPDB from drive C: to drive E:? Is this going to cause a major bottom neck in drive E:, where the data are located?

Thank You for any help!


Eric

View 1 Replies View Related

Sql Box Moved From One Place To Other

Jan 5, 2006

what happens if the physical location of a box(which had sql server 2000 on it) is chaned.
what happens to the replication and distributed queries.
Thanks.

View 1 Replies View Related

No Place On Disk

May 20, 2004

I have one big db and i heve no place on disk
the log file is big too. how can i delete the log

View 7 Replies View Related

Best Place To Start

Apr 17, 2007

I am fairly versed with SQL 2005 as I have been using it since it came out. Now I have need to learn about Cubes.



Does anybody have any suggestions of where I need to begin to learn about what cubes are, how to create them and use them?



Ultimately we will be incorporating them into Reporting Services.



Thanks for the information.

View 3 Replies View Related

Where To Place An Order By

Dec 26, 2007

Where would i place an orderby my DateCreated field...everywhere i try to place it i get this error...
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Any help with this issue would be greatly appreciated....



CREATE PROCEDURE GetTimeCard

( @LoginID nvarchar(50),

@DateRangeFrom datetime,

@DateRangeTo datetime

)

AS

BEGIN

IF ( @DateRangeFrom = '1/1/1753' ) AND ( @DateRangeTo = '1/1/1753' )

select x.*, x1.TotalExpenses, x2.WorkedCount

from (

select tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName

from OPS_TimeCards tc

join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID

group by tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName



union

select tc.TimeCardID, tc.DateCreated,tc.DateEntered,oe.FirstName, oe.LastName

from OPS_TimeCards tc

join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID

group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x

left outer join (

select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses

from OPS_TimeCards tc

join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID

group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered

left outer join (

select tc.TimeCardID, tc.DateCreated,tc.DateEntered, count(*) WorkedCount

from OPS_TimeCards tc

join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID

group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered



ELSE



select x.*, x1.TotalExpenses, x2.WorkedCount

from (

select tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName

from OPS_TimeCards tc

join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo

group by tc.TimeCardID, tc.DateCreated,tc.DateEntered, oe.FirstName, oe.LastName



union

select tc.TimeCardID, tc.DateCreated,tc.DateEntered,oe.FirstName, oe.LastName

from OPS_TimeCards tc

join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo

group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x

left outer join (

select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses

from OPS_TimeCards tc

join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID

group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered

left outer join (

select tc.TimeCardID, tc.DateCreated,tc.DateEntered, count(*) WorkedCount

from OPS_TimeCards tc

join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID

join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID

where oe.LoginID = @LoginID

group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered

End

GO

View 3 Replies View Related

Where To Place Articles

May 30, 2006

I have a design that includes articles that will be searched. Obviously its too slow to put them into fields, and impossible because some have photos or are otherwise html documents. So I want to put pointers to their location.

Two questions. For each deployment, both desktop and web, where is the best place to put the articles. In any folder, or only in an iis virtual folder?

dennist685

View 5 Replies View Related

SQL Servers Place In The 'Application'

Jan 28, 2004

I'm rapidly understanding that much more of my application as a whole is in SQL Server that I would have originally thought.

Stored Procedures
Triggers
Constrains
And so on

It generally means that some of the stuff I'd have naturally done in the Business Layer might be best done in SQL - certain issues in the Business Layer might be best being triggers or constraints for example...

One thing that still puzzles me, and I'd like some references or advice now as it's a blank area in my mind is how this interfaces to your asp.net code.

Obviously I call stored procedures and the like from code, and use parameters, etc, not problem, it's more what I do when these stored procedures or associated triggers fail (or a constrain fails - though this should be less likely)?

SQL sends back an error? But what? Then what do you get your page to do, especially if SQL failed midway through a 'big' transaction? Do you have save 'where the user was somehow' so they don't start inputting again?

It's all a bit vague at the moment, some detail would be nice? :)

View 4 Replies View Related

Place Index On Filegroup

Dec 14, 2001

Hi, I want to replace the indexes in my database to a different
filegroup. How can I do that using T-SQL? I only found a way that
uses the EM, but I have a lot of indexes and I hate to do it manually.

Thanks a lot.

View 2 Replies View Related

Null Error In One Place, But Not The Next

Aug 9, 2001

One of our customers has a production and a test database. We are loading tables via a C++ program that works on production, but not on test, which is supposed to be an exact duplicate.

The error we are getting when trying to add columns to one of the table is 80004e21 null That is it.

When we load the exact same records in the production environment we do not get the error. We have spent many hours ensuring that the tables are exactly the same on test as in production.

Does anyone know, is there an environment variable at a database level that says how to handle null values?

Thanks!!!
C

View 1 Replies View Related

Upgrading From SQL 6.5 To 7.0 With Replication In Place.

Feb 4, 2000

If I have a publisher, distributor, and subscribers all running SQL 6.5, and I wish to upgrade to SQL 7.0, in what order to I upgrade?

View 1 Replies View Related

Database Backup To Different Place

Mar 11, 2004

Hi All!

I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:isl_fs1DBName.bak'

BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\isl_fs1DBName.bak'
it doesn't work please help

View 2 Replies View Related

Can You Use A VARIABLE In Place Of A Database Name?

Apr 8, 1999

Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement

For Example: Instead of using the following with each database hardcoded in the SP:

select @dataused = sum(b.reserved)
from DBSglep..sysindexes b
where b.indid in (0, 1, 255) and segment != 2

I would like to loop for every database listed in sysdatabases and do this:

select @dbname = @dataname+'..sysindexes'

select @dataused = sum(b.reserved
from @dbname b
where b.indid in (0, 1, 255) and segment != 2

I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name).

Any assistance in resolving this problem would be greatly appreciated! :-)

Many thanks in advance...

Regards,
Wayne

View 2 Replies View Related

Best Place To Look For Memory Leak

Jun 21, 2001

Last 2 nights (at night) my SQL Server has locked up, first night trying to back up MSDB(20 meg) and last night ran out of memory at 6:30 AM. No users on at either time, no jobs running on the second night. I was going to turn perfmon on tonite. Any input on what best to monitor?

View 2 Replies View Related

Log Shring When No Place On The Disk ?

Nov 28, 2007

Hello,I run out of space on the disk, my db log is 130GB, I need to shrink db log. I made a backup through network by UNC of the database, but when I want to backup log, I get a message :BACKUP LOG cannot be performed because there is no current database backup.But the backup is reachable, I can restore from it.Any ideas ?

View 8 Replies View Related







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