Decimal In Design Table

Jul 12, 2005

Hi, this is a trivial question.I am trying to make a 2 point decimal stored in the sql database.For example,a field called, "Points", will have values such as 100.00, 107.07, 99.88, 56.01 etc...and all decimals like 108.009 would round up to 108.01.Now, in the design table of the sql server. Column Name Data Type Length Allow NullsPoints Decimal 9 Yes-------------------------------------Description (blank)Default Value (blank)Precision 18Scale 0Formula (blank)The numbers in red are assign by default. How would I alter that to make it a 2 decimal point number? What should I change to make it work? Thank you

View 4 Replies

Adding Number Of Decimal Places During Table Design

Nov 3, 2006

Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
Neal

View 2 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..

Product name :
Nestle milk |
Rainbow milk
packages :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos
| CTN=8 Nos

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

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

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

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

CREATE TABLE For BULK INSERT: How To Set The Decimal Number Right In Col ?

Jan 26, 2008

Hi all,

I executed the following sql code in my SQL Server Management Studio Express (SSMSE):
-- myCSV1.sql --

USE MyDatabase

GO

CREATE TABLE myCSVtable

(

Col1 int,

Col2 nvarchar(25),

Col3 nvarchar(25),

Col4 decimal (9.3),

)

BULK INSERT myCSVbulk

FROM 'c:myfile.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

);

GO
=====================================
I got the following error message:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '9.3'.

How can I set the statement "Col4 decimal (9.3)" right? Please help and advise.

Thanks in advance,
Scott Chang

View 7 Replies View Related

Transact SQL :: Values In Table - Convert Varchar To Decimal

Jul 22, 2015

I have one of the sample values in my table. I need to convert below value to Decimal(18,5)

DECLARE @i
VARCHAR
SET @i
= '0.9'

Output i m looking for is 0.90000

View 16 Replies View Related

DB Design :: Insert / Update FACT Table From Staging Table

May 6, 2015

We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.

View 7 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

How To Load A Flat Text File With Packed Decimal Field To A Sql Table

Apr 3, 2001

i have this flat text file that has a number of packed decimal
field type. How do I load that text file into a sql table.

thanks

View 4 Replies View Related

Decimal Places Error On Opening SQLServer Table From Access Project

Oct 31, 2006

One single table when openen from a MSAccess project file gives error:

The setting for DecimalPlaces property must be from 0 through 15, 255 for Auto (default).

After which the table is openend normally. I don't recall setting Decimal Places to the table. The only numeric fields are two INTs that are also ID's (PK and FK). This error occurs also when recreating the db project. These fields can not be edited eitther from MSAccess nor Enterprise Manager.

What can be done on the table, resp. MSAccess to change this?

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

DB Design :: Insert Data From One Table To Another Table

Jul 30, 2015

I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?

View 6 Replies View Related

Table Design

Apr 30, 2008

Hi, I am developing an application to a garment factory. I have a doubt in designing a table.Basic tables:Jobs, JobColors, Material, Units, Currencies ...These tables are designed with normalization rules.I got a problem at PurchaseOrderDetailsMain
table is JobMaterial. It has materialid, jobid, supplierid, description
and TypeFactor(which represents the type of order) means that the
material is ordered based on size or colors or total qty.1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colorsThe main problem at the details of the sub table.JobMaterialDetailsIf typefactor is by size, i need to store the details based on sizeex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcsSo I will have 4 records per each sizeIf it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.If it is by general, Total qty 10000pcsHow
can I design this table. If I take, ColorOrSize column, it will refer
different values for diffrent typefactor. When by size, it will have
Size and when by color, it will refer colorcode.But colors are having referential integrity. So it is violated other than by color as typefactor.What is the best way to design this table?Can anybody suggest?Thanks in advance

View 2 Replies View Related

Table Design

Jul 24, 2000

I am desingning a table and i have a column OrderID and another column call Order, is neccessary to use a primary key, because One OrderID may have many Orders?
Thanks.

View 5 Replies View Related

Table Design

Aug 21, 2000

Coming from a support background and having to design my first database I have a couple of questions re- table design. Firstly I have set up several tables and included one field (of the same name) in each. This is a primary key in one table with an incremental seed. I would like this info to appear in the other tables although these can be duplicates in the other tables. How is it best to achieve this relationship. From reading it suggest FK in the relationship application but looking at other databases this seems to have been achieved by some other means. Is it more common to use stored procedures to enforce this? If so please add pointers. Secondly, I have set up a couple of master tables to act as looks ups for fields in other tables. Again how do I get this to look up the table - is it done through stored procedures or at the time of writing the front end application?? Sorry if this is all basic stuff but it is new to me.

Thanks for any help

View 1 Replies View Related

Table Design

Oct 26, 2004

CREATE TABLE [dbo].[table1] (
[aaa] [bigint] IDENTITY (10000, 1) NOT NULL ,
[bbb] [int] NOT NULL ,
[ccc] [int] NOT NULL ,
[ddd] [bigint] NOT NULL ,
[eee] [int] NOT NULL ,
[ffff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[gggg] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hhh] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iii] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jjj] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[kkk] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lll] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mmm] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nnnn] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ooo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ppp] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qqqq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rrrr] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssss] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tttt] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uuuuu] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vvvvv] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wwwww] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xxxxx] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[yyyyy] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zzzzz] [int] NULL ,
[abc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[def] [datetime] NULL ,
[ghi] [datetime] NOT NULL ,
[jkl] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mno] [bigint] NULL
) ON [PRIMARY]

I have created a table with above column width. The rowsize is more than 8kb.And the table holds millions of rows of data. So is it a correct way of designing the table?
Or how can I redesign this table.

Thanks.

View 1 Replies View Related

Table Design Help

Mar 16, 2004

I'm currently developing a real estate system to manage order processing and work flow. I'm a little uncertain as to how to design the tables because an order can have N number of applicants, owners, buyers, and properties. There are cases where there are 9 different buyers and some where the number of properties exceeds 20. It seems that normalization might make the situation crazy, but I'm a touch rusty. Thanks.

View 2 Replies View Related

Table Design ? What To Do?

Apr 16, 2004

I am developing an estimating application and at the "quantity takeoff" level, all products/equipment/sub contracting/etc.... end up on the same table because of the common elements:

Item | Quantity | Unit Price | SubTotal | Total

Now, since the unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work, etc...
how do I direct the attention to the appropriate table when trying to obtain the unit price or edit the unit price etc...

If my question is not clear, let me know, I will try to clarify.

Mike B

View 3 Replies View Related

Design Table

May 10, 2008

Hi,

Is there a way to copy and save my Design Table? I want to transfer it to another workstation.

View 8 Replies View Related

Table Design

Feb 13, 2006

Hi am trying to figure out my table design need some advice

I have a table that catches the telephone numbers of a customer , there are 5 telephones for each customer. All the numbers are there in the DB

Should I create the design in the following format

CustomerName
Tel1
Tel2
Tel3
Tel4
Tel5

Or Should I have

Customer
Telephone

& then have 5 rows for each customer

Please advice

I even wanted to knw how should the design be , if all the Tel Nos are not captured, I mean what if some customers have 3 tel Nos, while some may just have 1 while some may have all 5

Thanks

View 7 Replies View Related

Please Help With SQL Table Design

Aug 21, 2006

Hi I'm having trouble figuring out this database design and normalising the data. Following are the tables and I am wonder if it can be normalised any further. This is a database of Architects and their work history and building they have designed.

Architects:
FirstName
LastName
Gender
DOB
Bio

Works:
Title
Client
Type_of_Building
Style
Address
Year_Designed
Year_Completed
Comments
Architect_ID

Firm: (this is the various firms that the architect has worked for)
CompanyName
Address

EmploymentHistory: (when the architect worked with a particular firm)
Year_Start
Year_End
Firm_ID
Architect_ID

Publication: (where the architects works were published)
Author
Date_Publication
Title
Source
Publisher
Architect_ID

I would really appreciate some help and suggestions on creating the relationships.
thanks

View 5 Replies View Related

Table Design

Sep 17, 2007

I create following tables.

employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint

department table
------------------
DEPARTMENT_CODEint
DEPARTMENT_NAMEvarchar

position table
---------------
POSITION_CODEint
POSITION_NAMEvarchar

country table
-------------
COUNTRY_CODEint
COUNTRY_NAMEvarchar

In employee table I have to store two country names.
ie. employee currently working in this country
but his home country name (origin country) is another
name. In country table all countries information available.
ie. country_code and country_name.

How I can design the employee table that it get country_name from COUNTRY TABLE ?
but we can not use same country_code in two fields.

OR
otherwise I will have to create employee table like this

employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint
COUNTRY_NAMEvarchar

ie. during data entries direct insert the country_name in employee
table ?

I want to create separate table for separate data.
department,postion,country.

How I can use unique country_code in employee table's two
fields ?

regards
Martin

View 2 Replies View Related

Best Table Design

Nov 8, 2007

Hi,

I'm struggling coming up with an optimal table design for the following scenario.

I have a table in which each row is recording information about a document - author, publisher, publication, title, abstract and so forth. I also have an image table which contains information about the images which can be associated with the documents.

Each document can have on of three image types associated with it - an author photo, a publisher logo and a publication logo. There aren't actually that many different authors, publishers and publications in the database so it would be common for many documents to have the same author or the same publisher although authors sometimes write for different publications. In the current setup it's possible for a document to have more than one image of each type associated with it, which is undesirable - this needs to eliminated and we need to ensure that each document can only have one image of each type associated with it.

It'd also be a huge boon if I could get all the information about a document and it's attached images with a single database call. In the current setup, trying to do this returns multiple rows so I've ended up making two calls, one for the document and one for it's attached images.

I can see two possible ways of setting this up, neither of which appeal.

The first is to add three columns to my document table to record the image URLs which is simple but inflexible should I want to add more image types later on.

The second is to set up three new tables for author, publisher and publication and associate each of these with the document table through a key and then to the image table through another key. There's also an image type table which is going to come in to play. I'm unwilling to do this because it's going to just explode the number of tables I've got, especially if we add more image types, and play havoc with my XML schemas.

Is there another way of rationalizing this that I've missed?

Cheers,
Matt

View 3 Replies View Related

Table Design

Jan 11, 2007

Hi,

I am trying to decide between 2 table designs. I basically have several different types of work items that need to be tracked. Each work item has their own set of statuses, some of which may be the same as other work item's statuses, some of which will be unique for that work item.

Now, should I create one status table for each work item, or would it be better to use one status table, but add a column to track which work item the status belongs to?

I'm not sure which is a better design, or perhaps there is some alternate design that is better. Any help is appreciated.

Thx.

View 1 Replies View Related

Design Table Need Help

Sep 20, 2007

Currently I need to design database table and got stuck.

I have a project and its sub projects. A parent project and its sub projects share the same requirement.The description of the requirement will be changed every day.
For example
Project A - Do Some thing 1 on Project A [09/28/2007]
Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]

Every time the comments updated, both project A and project A-A1 should share the same update i.e select project A and its subproject will be displayed
Project A Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Project A-A1 Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29//2007]

When you tried to insert new sub project, the requirement will be shared with the parent project ie.
Project A - Do Some thing 1 on Project A [09/28/2007]
Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Project A-A2 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] , Do Some thing 2 on Project A-A2 [09/30/2007]

assume Project A-A1 and Project A-A2 are sub project of project A

Could anyone show me how to design the database table for those mess?

Thank you

View 6 Replies View Related