equal distribution of start and finish dates

Hi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls helpQuestion. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita wrote:
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help
This will handle a two-month period, anything more than that is going to
be more complicated:
USE tempdb
GO
CREATE TABLE dbo.Test (
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
GO
CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
END
GO
SELECT
start_date,
finish_date,
cost,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4) END AS portion1,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4)) END AS portion2
FROM dbo.Test|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:
> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rated
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
>
>|||On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
>Hi all
>I have got 3 columns in my table- start date,finish date and cost..in the
>following format...
>start_date finish_date cost
>12/12/2000 20/12/2000 $2000
>01/09/2000 12/10/2000 $400
>Now if the month and year of the start and finish date is same, the cost
>remains same...
>but if the month of the two dates are different, i have to distribute the
>cost between the two months by calculating the cost for the number of days
>for both of the months..
>but i am not able to figure out how?
>i am using sql 2005 ..
>my table has got about 1 million rows...
>pls help
Hi mita,
First, you need to create a table that holds all months in your
reporting period (or more). Something like this:
CREATE TABLE dbo.Months
(MonthStart datetime NOT NULL PRIMARY KEY,
MonthEnd datetime NOT NULL);
go
DECLARE @.TheMonth datetime;
SET @.TheMonth = '200000101'; -- Start at january 2000
WHILE @.TheMonth <= '20191231' -- End at december 2019
BEGIN;
INSERT INTO dbo.Months (MonthStart, MonthEnd)
VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
END;
The above is a one time operation, provided you never drop the table.
Don't forget to add some extra months to the table in a year or ten!
With this table in place, your query becomes something like this:
SELECT PeriodStart, PeriodEnd,
TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
/ DATEDIFF (day, start_date, finish_date) AS cost
FROM (SELECT CASE WHEN a.start_date > b.MonthStart
THEN a.start_date
ELSE b.MonthStart
END AS PeriodStart,
CASE WHEN a.finish_date < b.MonthEnd
THEN a.finish_date
ELSE b.MonthEnd
END AS PeriodEnd,
a.start_date, a.finish_date,
a.cost AS TotalCost
FROM dbo.MyTable AS a
INNER JOIN dbo.Months AS b
ON b.MonthStart <= a.finish_date
AND b.MonthStart >= a.start_date) AS d
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP|||hi hugo
i just ran this query for creating the months table which u suggested... i
am getting an error....
"Conversion failed when converting datetime from character string." what do
i do?
"Tracy McKibben" wrote:
> mita wrote:
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
> This will handle a two-month period, anything more than that is going to
> be more complicated:
> USE tempdb
> GO
> CREATE TABLE dbo.Test (
> start_date DATETIME NOT NULL,
> finish_date DATETIME NOT NULL,
> cost DECIMAL(18,3)
> )
> INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
> INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
> INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
> GO
> CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
> DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
> ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
> END
> GO
> SELECT
> start_date,
> finish_date,
> cost,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4) END AS portion1,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4)) END AS portion2
> FROM dbo.Test
>|||GO
CREATE TABLE [dbo].[DSS](
[Service Start] [datetime] NULL,
[Service End] [datetime] NULL,
[FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[No of Units] [float] NULL,
) ON [PRIMARY]
"Hugo Kornelis" wrote:
> On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
> >Hi all
> >I have got 3 columns in my table- start date,finish date and cost..in the
> >following format...
> >
> >start_date finish_date cost
> >12/12/2000 20/12/2000 $2000
> >01/09/2000 12/10/2000 $400
> >
> >Now if the month and year of the start and finish date is same, the cost
> >remains same...
> >but if the month of the two dates are different, i have to distribute the
> >cost between the two months by calculating the cost for the number of days
> >for both of the months..
> >but i am not able to figure out how?
> >i am using sql 2005 ..
> >my table has got about 1 million rows...
> >pls help
> Hi mita,
> First, you need to create a table that holds all months in your
> reporting period (or more). Something like this:
> CREATE TABLE dbo.Months
> (MonthStart datetime NOT NULL PRIMARY KEY,
> MonthEnd datetime NOT NULL);
> go
> DECLARE @.TheMonth datetime;
> SET @.TheMonth = '200000101'; -- Start at january 2000
> WHILE @.TheMonth <= '20191231' -- End at december 2019
> BEGIN;
> INSERT INTO dbo.Months (MonthStart, MonthEnd)
> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
> END;
> The above is a one time operation, provided you never drop the table.
> Don't forget to add some extra months to the table in a year or ten!
> With this table in place, your query becomes something like this:
> SELECT PeriodStart, PeriodEnd,
> TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
> / DATEDIFF (day, start_date, finish_date) AS cost
> FROM (SELECT CASE WHEN a.start_date > b.MonthStart
> THEN a.start_date
> ELSE b.MonthStart
> END AS PeriodStart,
> CASE WHEN a.finish_date < b.MonthEnd
> THEN a.finish_date
> ELSE b.MonthEnd
> END AS PeriodEnd,
> a.start_date, a.finish_date,
> a.cost AS TotalCost
> FROM dbo.MyTable AS a
> INNER JOIN dbo.Months AS b
> ON b.MonthStart <= a.finish_date
> AND b.MonthStart >= a.start_date) AS d
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 18 Jun 2006 16:32:02 -0700, mita wrote:
>"Hugo Kornelis" wrote:
(snip)
>> First, you need to create a table that holds all months in your
>> reporting period (or more). Something like this:
>> CREATE TABLE dbo.Months
>> (MonthStart datetime NOT NULL PRIMARY KEY,
>> MonthEnd datetime NOT NULL);
>> go
>> DECLARE @.TheMonth datetime;
>> SET @.TheMonth = '200000101'; -- Start at january 2000
>> WHILE @.TheMonth <= '20191231' -- End at december 2019
>> BEGIN;
>> INSERT INTO dbo.Months (MonthStart, MonthEnd)
>> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
>> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
>> END;
>hi hugo
>i just ran this query for creating the months table which u suggested... i
>am getting an error....
>"Conversion failed when converting datetime from character string." what do
>i do?
Hi Mita,
Sorry for the delayed reply.
The error is caused by a stupid typo in my code (quoted above) - I have
one zero to many in the date constant for Jan 1st, 2000. If you change
'200000101' to '20000101', the error should go away.
--
Hugo Kornelis, SQL Server MVP

Enterprise manager table design

I have table with more then 100 coluns . I must change default value
from 0 to 1 in 90 columns. Is there a way to do this in "table design"
in "all in once" metod.Hi
I prefer to write a script does the job .Don't forget to drop first
constraints and then re-create with DEFAULT (1)
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1131349106.061862.27390@.z14g2000cwz.googlegroups.com...
>I have table with more then 100 coluns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>|||Hi
To add to Uri's suggestion, you can script the table by right clicking the
table in EM and choose the Generate SQL option. Then when you have the file
it can be edited (possibly in Query Analyser!) where global replacement coul
d
save you time. You can then run the script in Query Analyser or from a
command prompt using OSQL.
If you don't want to loose the data from the table then you may want to copy
the contents into a temporary table and then load them back if you are using
the drop/create method.
John
"nywebmaster" wrote:

> I have table with more then 100 coluns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>|||On 6 Nov 2005 23:38:26 -0800, nywebmaster wrote:

>I have table with more then 100 coluns . I must change default value
>from 0 to 1 in 90 columns. Is there a way to do this in "table design"
>in "all in once" metod.
Hi nywebmaster,
This question suggests that you have a repeating group in your table.
That is a sign of a weak design. I'll give you an example:
Weak design:
CREATE TABLE SalesPersons
(SalesPersonID int NOT NULL
SalesPersonName varchar(50) NOT NULL,
SalesJan decimal(9,2) DEFAULT NULL,
SalesFeb decimal(9,2) DEFAULT NULL,
SalesMar decimal(9,2) DEFAULT NULL,
SalesApr decimal(9,2) DEFAULT NULL,
SalesMay decimal(9,2) DEFAULT NULL,
SalesJun decimal(9,2) DEFAULT NULL,
SalesJul decimal(9,2) DEFAULT NULL,
SalesAug decimal(9,2) DEFAULT NULL,
SalesSep decimal(9,2) DEFAULT NULL,
SalesOct decimal(9,2) DEFAULT NULL,
SalesNov decimal(9,2) DEFAULT NULL,
SalesDec decimal(9,2) DEFAULT NULL,
PRIMARY KEY (SalesPersonID)
)
Better design:
CREATE TABLE SalesPersons
(SalesPersonID int NOT NULL
SalesPersonName varchar(50) NOT NULL,
PRIMARY KEY (SalesPersonID)
)
CREATE TABLE Sales
(SalesPersonID int NOT NULL,
MonthNo tinyint NOT NULL,
Sales decimal(9,2) NOT NULL,
PRIMARY KEY (SalesPersonID, MonthNo)
FOREIGN KEY (SalesPersonID)
REFERENCES SalesPersons (SalesPersonID)
CHECK (MonthNo BETWEEN 1 AND 12)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Enterprise manager table design

I have table with more then 100 columns . I must change default value
from 0 to 1 in 90 columns. Is there a way to do this in "table design"
in "all in once" metod.
Hi
Please don't multipost , I've answered the question in .programming group.
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1131349183.821846.143180@.o13g2000cwo.googlegr oups.com...
>I have table with more then 100 columns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>

Enterprise manager table design

I have table with more then 100 columns . I must change default value
from 0 to 1 in 90 columns. Is there a way to do this in "table design"
in "all in once" metod.Hi
Please don't multipost , I've answered the question in .programming group.
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1131349183.821846.143180@.o13g2000cwo.googlegroups.com...
>I have table with more then 100 columns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>

Enterprise manager table design

I have table with more then 100 columns . I must change default value
from 0 to 1 in 90 columns. Is there a way to do this in "table design"
in "all in once" metod.Hi
Please don't multipost , I've answered the question in .programming group.
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1131349183.821846.143180@.o13g2000cwo.googlegroups.com...
>I have table with more then 100 columns . I must change default value
> from 0 to 1 in 90 columns. Is there a way to do this in "table design"
> in "all in once" metod.
>sql

Enterprise Manager Problem

Hi. I got Enterprise Manager to work finally.
However, the Columns are backwards when I retrieve all rows, and when i go
to design a table.
Meaning to say that the ID starts on right side of screen, and scrollbar is
on left side (everything is backwards)
in options of enterprise manager, it says english is default language.
thanksHi Michael,
Does this problem happen only with the Enterprise Manager or you see the
same behavior with , say Query Analyzer:
select * from pubs..authors where 1=2
How does the column order appear in the result window of this query ?
This may be more likely a language specific issue and I have seen certain
non-English languages (Arabic etc) which lay out the columns from
left-to-right.
So, you may want to review the configuration on the system, and spcifically
look for non-english related language choices during installation (both OS
and SQL related)
Hope this helps,
Ananth Padmanabham [MSFT]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply only to the newsgroups.
When posting, please state the version of SQL Server being used and the
error number/exact error message text received, if any.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--
>From: "Michael" <Michael@.nospamplease.com>
>Subject: Enterprise Manager Problem
>Date: Fri, 12 Mar 2004 12:05:27 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OFr$4QFCEHA.580@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: h-68-167-73-42.nycmny83.covad.net 68.167.73.42
>Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:333657
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi. I got Enterprise Manager to work finally.
>However, the Columns are backwards when I retrieve all rows, and when i go
>to design a table.
>Meaning to say that the ID starts on right side of screen, and scrollbar is
>on left side (everything is backwards)
>in options of enterprise manager, it says english is default language.
>thanks
>

enterprise manager (or other software) to document tables and columns for Server2k?

Hi,

We have enterprise manager for sql server 2000. I would like to store
comments about my database schema somewhere in the database. I want to
store information about the intent of tables and columns.

We have EM but I am open to any suggestions regarding products.metaperl (metaperl@.gmail.com) writes:
> We have enterprise manager for sql server 2000. I would like to store
> comments about my database schema somewhere in the database. I want to
> store information about the intent of tables and columns.
> We have EM but I am open to any suggestions regarding products.

You can use extended properites to this end, and that will inded put
data about the database into the database itself.

For more heavy-duty tools for data modelling, the three biggest tools
on that market are, I believe, PowerDesigner (from Sybase), ERWin
(from Computer Associates) and Embrocadero. These tools comes with a
price tag, though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Entering data into tables

How does one enter data into the tables of a new database in the first place? I'm using SQL Server Management Studio Express. I can name the columns, but that seems to be as far as I get. Thanks for any Help.

I would recommend you use the T-SQL INSERT statement for adding data to the table.

Please take a look at the following tutorial titled "Inserting and Updating Data In a Table"

http://msdn2.microsoft.com/en-us/library/ms365309.aspx.

Kind Regards,
Jaaved

|||

Thanks a lot, as you can tell, I'm brand new to this SQL. Just trying to learn the ropes.

Thanks again.

Entering all the data in one big table

I am entering text heavy data in one big table, which has many columns. One
problem I'm having is the text does not fit in some rows (because the
characters are so many) and I have to truncate if the text is longer than
the length defined for the column.
Now I am hoping to change the design so that I don't need to truncate any
data. I know that creating a properly normalized database would solve the
problem, but this is very difficult for the situation I have. The change
should be as small as possible. What would be the best way to solve this
problem?You might want to use the TEXT datatype for some of the larger columns. You
can fit 2GB in each.
Andrew J. Kelly SQL MVP
"TomTom" <no_spam_please@.TomTom.com> wrote in message
news:uc2imULAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I am entering text heavy data in one big table, which has many columns.
One
> problem I'm having is the text does not fit in some rows (because the
> characters are so many) and I have to truncate if the text is longer than
> the length defined for the column.
> Now I am hoping to change the design so that I don't need to truncate any
> data. I know that creating a properly normalized database would solve the
> problem, but this is very difficult for the situation I have. The change
> should be as small as possible. What would be the best way to solve this
> problem?
>
>|||I'm working on international text. If I do it, then I cannot host Japanese
and Korean on the same machine. Is this correct?
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uYvGhpMAEHA.3936@.TK2MSFTNGP11.phx.gbl...
> You might want to use the TEXT datatype for some of the larger columns.
You
> can fit 2GB in each.
> --
> Andrew J. Kelly SQL MVP
>
> "TomTom" <no_spam_please@.TomTom.com> wrote in message
> news:uc2imULAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> One
than
any
the
>|||You can use nText datatype to store unicode data.
Andrew J. Kelly SQL MVP
"TomTom" <no_spam_please@.TomTom.com> wrote in message
news:OoeSKSYAEHA.132@.TK2MSFTNGP10.phx.gbl...
> I'm working on international text. If I do it, then I cannot host Japanese
> and Korean on the same machine. Is this correct?
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:uYvGhpMAEHA.3936@.TK2MSFTNGP11.phx.gbl...
> You
columns.
> than
> any
> the
change
this
>

Entering all the data in one big table

I am entering text heavy data in one big table, which has many columns. One
problem I'm having is the text does not fit in some rows (because the
characters are so many) and I have to truncate if the text is longer than
the length defined for the column.
Now I am hoping to change the design so that I don't need to truncate any
data. I know that creating a properly normalized database would solve the
problem, but this is very difficult for the situation I have. The change
should be as small as possible. What would be the best way to solve this
problem?You might want to use the TEXT datatype for some of the larger columns. You
can fit 2GB in each.
--
Andrew J. Kelly SQL MVP
"TomTom" <no_spam_please@.TomTom.com> wrote in message
news:uc2imULAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I am entering text heavy data in one big table, which has many columns.
One
> problem I'm having is the text does not fit in some rows (because the
> characters are so many) and I have to truncate if the text is longer than
> the length defined for the column.
> Now I am hoping to change the design so that I don't need to truncate any
> data. I know that creating a properly normalized database would solve the
> problem, but this is very difficult for the situation I have. The change
> should be as small as possible. What would be the best way to solve this
> problem?
>
>|||I'm working on international text. If I do it, then I cannot host Japanese
and Korean on the same machine. Is this correct?
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uYvGhpMAEHA.3936@.TK2MSFTNGP11.phx.gbl...
> You might want to use the TEXT datatype for some of the larger columns.
You
> can fit 2GB in each.
> --
> Andrew J. Kelly SQL MVP
>
> "TomTom" <no_spam_please@.TomTom.com> wrote in message
> news:uc2imULAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> > I am entering text heavy data in one big table, which has many columns.
> One
> > problem I'm having is the text does not fit in some rows (because the
> > characters are so many) and I have to truncate if the text is longer
than
> > the length defined for the column.
> >
> > Now I am hoping to change the design so that I don't need to truncate
any
> > data. I know that creating a properly normalized database would solve
the
> > problem, but this is very difficult for the situation I have. The change
> > should be as small as possible. What would be the best way to solve this
> > problem?
> >
> >
> >
>|||You can use nText datatype to store unicode data.
--
Andrew J. Kelly SQL MVP
"TomTom" <no_spam_please@.TomTom.com> wrote in message
news:OoeSKSYAEHA.132@.TK2MSFTNGP10.phx.gbl...
> I'm working on international text. If I do it, then I cannot host Japanese
> and Korean on the same machine. Is this correct?
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:uYvGhpMAEHA.3936@.TK2MSFTNGP11.phx.gbl...
> > You might want to use the TEXT datatype for some of the larger columns.
> You
> > can fit 2GB in each.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "TomTom" <no_spam_please@.TomTom.com> wrote in message
> > news:uc2imULAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> > > I am entering text heavy data in one big table, which has many
columns.
> > One
> > > problem I'm having is the text does not fit in some rows (because the
> > > characters are so many) and I have to truncate if the text is longer
> than
> > > the length defined for the column.
> > >
> > > Now I am hoping to change the design so that I don't need to truncate
> any
> > > data. I know that creating a properly normalized database would solve
> the
> > > problem, but this is very difficult for the situation I have. The
change
> > > should be as small as possible. What would be the best way to solve
this
> > > problem?
> > >
> > >
> > >
> >
> >
>

Enter Default Values for all columns in all tables except Primary Keys

How can i enter Default Values of " " to all the columns of type character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank youOn Sun, 3 Apr 2005 18:05:46 -0400, serge wrote:

>How can i enter Default Values of " " to all the columns of type character
>of all the tables (excluding system tables) and Default Values of 0
>of all columns of type numbers. Excluding all primary key columns.
>Thank you

Hi Serge,

CREATE TABLE Example (PKCol int NOT NULL PRIMARY KEY,
NumCol int NOT NULL DEFAULT 0,
CharCol varchar(20) NOT NULL DEFAULT '')
go
INSERT Example (PKCol)
VALUES (1)
SELECT * FROM Example
go
DROP TABLE Example
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)