Trying To Get The Remainder Of A Decimal By Using Formula On A Field
Dec 22, 2005
Hello Everyone,
Here is my situation.
I have a field in a table (named QuestionPosition)(type Decimal) that contains values like this:
1 2 3 4 5.1 5.2 5.3 ...
I want to have another field in the same table that stores just the remainder (in the above case a 0 for integer values and the 1 of the 5.1, 2 of the 5.2 etc.
I created the field in the table (named QuestionSubPosition) and tried to set the formula for it to QuestionPosition % 1 but it does not accept the formula.
The part that i do not understand is that i can set the formula to QuestionPosition / 1 it works (but not the results i want).
Hi,I'm trying to count field login totals for users which updates another fieldin another table. Which is the most efficient method?I don't want to use a standard query as it will take too long if there are1000 users per company each with 1000 plus logins.I was thinking in terms of either a function, or a formula (using the built-in formula field within mssql).the query though (as its the only way which i'm familiar) is:SELECT SUM(NumberOfLogons) AS TotalLogonsFROM EmployerProfileDB39WHERE (CompanyName = x) AND (EmployerID = y)how would i write this as a formula or as a function?
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
I have an aggregated table called T1 which looks like this:
Code:
Name Amount a 10 b -4 c 9 d 5 e 0 f 0 g 8 h 3 i 3
I need to write a query which selects the top 3 names by amount which have an amount greater than 0. The remaining names which have amounts greater than 0 should be grouped together as 'others' at the bottom. Finally I need to add a total row. So the final result I am looking for is:
Code: Name Amount a 10 c 9 g 8 others 11 total 38
The part I'm struggling with is how to collapse the remaining names into the 'others' group.
I am updating three fields (defined as decimal(18,4) NULL) in a 1.7 million record table to 0. This table is also very wide (meaning lot of columns). A simple query like this -
Hi, I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3. Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.
I have a field in a database which is a datatype Money. When I run a select query the data is coming back with 4 decimal places like 100.0000 but I only want 2 decimal places like 100.00.
I need to format a field to a set number of decimal places which is held as a field within the DB, and also format it as a standard number.
I have been able to set the format of the field to the decimal places field and it all works (the quantity in the row has the right number of decimal places), but i cant now format it as a number...
Heres an example:
The quantity is 1500 with 3 decimal places so it becomes 1500.000
I need to format it as a number with comma's etc such as 1,500.000
How would i go about doing this? Ive already used the format option for the decimal places and dont know how to add another format type (which in this case will be N)
I have a table in SQL 2005 with a field that has a value of type 'money'. When values are added, the field has 4 decimal places. Is there a way that I can make it only have 2 decimal places right away? Thanks!!!
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.
I have a requirement where i have to insert empty string in column whose datatype is decimal.
I have to replace the column value if it is null then insert it as ' '.
Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.
IIF(Column1 IS NULL, ' ', Column1))
I used TRY_PARSE but it insert null value if there is conversion error.
Problem importing data from flat file into decimal(9,2) field. The data in the flat file is 000001453 and I am copying it to a decimal(10,2) field and instead of showing up in the 0000014.53 it comes across as 0001453.00. I tried defining the input columns a few different ways but none seemed to work. How do I do this with SSIS or do I need to write a SP and use convert? Thanks.
Version 2.2.32.7 1.0.0.0 2.0.0.0 1.2.0.0 2.1.8.8 1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show
Version  1.0.0.0  1.2.0.0  1.4.11.0  2.0.0.0  2.1.8.8  2.2.32.7
I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine. CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red ELSE '3' --White END END else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
[code]....
I checked the NRESULT field, and there are not any NULL values in there, either.
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
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)
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?
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?
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
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:
Error: SQL0204 - CONVERT in *LIBL type *N not found.
I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?
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?
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.
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
I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that? please help me? any idea or tutorial..example anything...
hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application thanx
