Creating Temporary Table From a View

Hi,
I would like to create a temporary table is a stored procedure which
contains all the same set of columns as one of my view in the
database. Is there anyway I can do this? I would also like to update
the temp table in the stored procedure itself.
Something like as follows
CREATE TABLE #tmpTable --I dont want to specify the list of columns
SELECT * INTO #tmpTable FROM vwTest
UPDATE #tmpTable SET ......
Can anyone please help me with this?
Thank you
EricGos,
How about SELECT * INTO #mytemptable from viewname
Note that using * in production code is generally not considered good =practice, as things can change oif columns added or removed.
Mike John
Gos wrote:
> Hi,
> > I would like to create a temporary table is a stored procedure which
> contains all the same set of columns as one of my view in the
> database. Is there anyway I can do this? I would also like to update
> the temp table in the stored procedure itself.
> > Something like as follows
> > CREATE TABLE #tmpTable --I dont want to specify the list of columns
> > SELECT * INTO #tmpTable FROM vwTest
> > UPDATE #tmpTable SET ......
> > > Can anyone please help me with this?
> > Thank you
> Eric|||Hello,
Have you tried this:
-- Start
CREATE PROC usp_Test
AS
SELECT * INTO #tmpTable FROM vwCustomers
UPDATE #tmpTable SET CustomerID = 'BERGA' WHERE CustomerID = 'BERGS'
SELECT * FROM #tmpTable
GO
EXEC usp_Test
SELECT * FROM vwCustomers
--End
vwCustomers is a view in the Northwind Sample Database that I created.
You can find the SQL for the vwCustomers here:
http://www.ilopia.com/MSSQL/Tutorials/Views.aspx#PartitionedViews
Hope this helps!
--
Regards,
Kristofer Gafvert
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
"Gos" <ericmoyer2345@.yahoo.com> wrote in message
news:a3cf3db2.0309281229.15c22814@.posting.google.com...
> Hi,
> I would like to create a temporary table is a stored procedure which
> contains all the same set of columns as one of my view in the
> database. Is there anyway I can do this? I would also like to update
> the temp table in the stored procedure itself.
> Something like as follows
> CREATE TABLE #tmpTable --I dont want to specify the list of columns
> SELECT * INTO #tmpTable FROM vwTest
> UPDATE #tmpTable SET ......
>
> Can anyone please help me with this?
> Thank you
> Eric|||Thanks a lot..
It works fine. I have a view which contains only the columns that are
required to be displayed on the User Interface. So, I thought that I
can use that view (SELECT * ) instead of giving a long list of
columns. Isn't it a good programming practice?
Gos

Creating SQL Statements at run-time

Okay, guess a few questions rolled into one post here.

On my site, I have a drop down list where users can select different columns from their database, and then once they select any given field, all the unique values that the field contains are brought up in a CheckBoxList for the user to select which ones they want to search for. (Note: that part of the site is already done--this next part is what I need help with) I want to create a SQL statement based on what the user checked. So like, if from FieldX they checked Item1, Item3, and Item8, the SQL statement created should be something along the lines of:

SELECT * FROM Orders Where FieldX='Item1' OR FieldX='Item3' OR FieldX='Item8'

This is going to be in an intranet, so I'm not too worried about SQL Injection attacks, which I've heard of, but don't really know what they are particulary. Although I guess it would be better to be safe rather than sorry.

Also, as far as creating the SQL statement, some items from the database will be text and others will be numbers, so I guess I also need to know how to find out whether an item in question is a string or a number of some type so that I can know whether to enclose that item in single quotes within the SQL statement.

Okay, I think that's it for now.

Thanks in advance.

Welcome to the forums.

This question has been answered a few times in these forums. Search in these forums and if you still dont find any possible solutio post back.

Creating small index takes LONG time!

Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
ChrisEM is notorious for generating inefficient code. It's far better to script
it by hand because:
1) a skilled human will write better code
2) the script can be used elsewhere and be checked into source code control
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
Hi- I am trying to add a simple, non clustered index, using Enterprise
Manager all default settings, to a table with about 30 columns and about 15K
rows of data. The index is on a smalldatetime column. For some reason, when
I
try to add this index, it takes FOREVER (let it go at least 6 hours, still
not completed). Killed process (which had very high processor count),
restarted SS, tried again, no go. No sign of any locks (in fact, no one even
connected to this database). Plenty of disk space.
Any ideas why this might occur? There are several other indexes on the
table, including primary key id column and other indexes referencing the
column I am trying to index as secondary, tertiary column, but never as
primary column. This is a large database with many tables (100+), lots of
data (4GB or so). But, this seems unusual to me...
I can access the table and the data in it from another instance of EM while
I am trying to create this index.
Any ideas?
Thanks,
Chris|||Chris,
Either it is not building the index, or something is seriously wrong.
An index like you describe should be built in seconds.
When I just created an index using Enterprise Manager (within Design
Table) it did not start the process until I closed the Design Table
window, when it prompted me with the question asking if I wanted to
save changes. Did you reach that point?
Roy
On Mon, 27 Feb 2006 17:09:27 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>Hi- I am trying to add a simple, non clustered index, using Enterprise
>Manager all default settings, to a table with about 30 columns and about 15
K
>rows of data. The index is on a smalldatetime column. For some reason, when
I
>try to add this index, it takes FOREVER (let it go at least 6 hours, still
>not completed). Killed process (which had very high processor count),
>restarted SS, tried again, no go. No sign of any locks (in fact, no one eve
n
>connected to this database). Plenty of disk space.
>Any ideas why this might occur? There are several other indexes on the
>table, including primary key id column and other indexes referencing the
>column I am trying to index as secondary, tertiary column, but never as
>primary column. This is a large database with many tables (100+), lots of
>data (4GB or so). But, this seems unusual to me...
>I can access the table and the data in it from another instance of EM while
>I am trying to create this index.
>Any ideas?
>Thanks,
>Chris|||Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to scrip
t
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code contro
l
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about 1
5K
> rows of data. The index is on a smalldatetime column. For some reason, whe
n
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one ev
en
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM whil
e
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Are there any processes blocking it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
Hmmm... looking at the process info, I can see that the query generated by
Enterprise Manager is
CREATE
INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
I can't imagine a much more efficient means of creating an index than that?
Thanks,
Chris
"Tom Moreau" wrote:

> EM is notorious for generating inefficient code. It's far better to
> script
> it by hand because:
> 1) a skilled human will write better code
> 2) the script can be used elsewhere and be checked into source code
> control
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:6B6FEF4E-9311-41B6-9E59-457B66090FE2@.microsoft.com...
> Hi- I am trying to add a simple, non clustered index, using Enterprise
> Manager all default settings, to a table with about 30 columns and about
> 15K
> rows of data. The index is on a smalldatetime column. For some reason,
> when
> I
> try to add this index, it takes FOREVER (let it go at least 6 hours, still
> not completed). Killed process (which had very high processor count),
> restarted SS, tried again, no go. No sign of any locks (in fact, no one
> even
> connected to this database). Plenty of disk space.
> Any ideas why this might occur? There are several other indexes on the
> table, including primary key id column and other indexes referencing the
> column I am trying to index as secondary, tertiary column, but never as
> primary column. This is a large database with many tables (100+), lots of
> data (4GB or so). But, this seems unusual to me...
> I can access the table and the data in it from another instance of EM
> while
> I am trying to create this index.
> Any ideas?
> Thanks,
> Chris
>|||Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than that
?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||I'm actually using the "manage indexes" window; after specifying the index,
I
click ok, and then another window appears which implies that the index is
being built, and this is where the hang occurs. Also, I know the request has
been sent at that point b/c I can see it as a process. Yes, something is
definitely wrong; I am also used to indexes like this being built in
seconds...
"Roy Harvey" wrote:

> Chris,
> Either it is not building the index, or something is seriously wrong.
> An index like you describe should be built in seconds.
> When I just created an index using Enterprise Manager (within Design
> Table) it did not start the process until I closed the Design Table
> window, when it prompted me with the question asking if I wanted to
> save changes. Did you reach that point?
> Roy
>
> On Mon, 27 Feb 2006 17:09:27 -0800, querylous
> <querylous@.discussions.microsoft.com> wrote:
>
>|||On Mon, 27 Feb 2006 17:46:26 -0800, querylous
<querylous@.discussions.microsoft.com> wrote:

>I'm actually using the "manage indexes" window; after specifying the index,
I
>click ok, and then another window appears which implies that the index is
>being built, and this is where the hang occurs. Also, I know the request ha
s
>been sent at that point b/c I can see it as a process. Yes, something is
>definitely wrong; I am also used to indexes like this being built in
>seconds...
So what happens if you run the same CREATE INDEX in Query Analyzer?
Roy Harvey
Beacon Falls, CT|||Could you please give us the SQL edition, version number, hardware, etc.?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
Nope, neither blocking nor blocked by...
"Tom Moreau" wrote:

> Are there any processes blocking it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:0201C2A0-397D-4C4C-9CF6-EC10248E44C7@.microsoft.com...
> Hmmm... looking at the process info, I can see that the query generated by
> Enterprise Manager is
> CREATE
> INDEX [dateCancelled] ON [dbo].[events] ([dateCancelled])
> I can't imagine a much more efficient means of creating an index than
> that?
> Thanks,
> Chris
> "Tom Moreau" wrote:
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows N
T
5.0 (Build 2195: Service Pack 4)
Never had any trouble with it.
"Tom Moreau" wrote:

> Could you please give us the SQL edition, version number, hardware, etc.?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:90639871-203F-4402-ADC0-D5D583E5BAB0@.microsoft.com...
> Nope, neither blocking nor blocked by...
> "Tom Moreau" wrote:
>
>

Creating select statement with seperate columns for different valu

I've been trying to figure out a select statement that would list a seperate
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?

Creating Report Server 2005 Report Totals

I have a table in my report. In it, I have 2 headers, a group and 1 footer at the bottom.

How do I sum my columns in the footer?

so nobody has tried adding totals in a table before or what?|||Uhm. Why don't you try
=sum(Fields!MyFieldToSum.Value)
typed in the footer field? Or do I miss something here?

Creating Output in columns instead of Rows

Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).

Right now my output would look like this:
PartNum__Yr-Mnth__Qty
Part123___Jan03____88
Part123___Feb03____33
Part123___Mar03____06

What I would like to output is:

PartNum___Jan03__Feb03__Mar03
Part123_____88_____33_____06

Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
From Table1
where
Date >= To_Date('01/01/2003','mm/dd/yyyy') and
Date <= To_Date('01/31/2004','mm/dd/yyyy')
Order by PartNum, Date

(Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)

I have seen an example of PIVOT, but could not get that to work.

Any suggestions?

Thanks!select Distinct PartNum,
cast(0 as decimal(15,0)) as Jan03,
cast(0 as decimal(15,0)) as Feb03,
cast(0 as decimal(15,0)) as Mar03,
into #temp
from Table1

Then from there run your regular query, put in a temp table.

Then from there you can update the columns in the above table.

Its ugly but works.

You can also do subselects.

Hope this gets your mind rolling|||Thanks for the reply. Guess I should not have made my example so simple!

The date actually comes from (part of select statement):
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"

So the "field" Yr-Mnth is not a table field, but created through the select statement.

The Qty comes from:
(dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
There is only 1 value per month for each value.

In this example, the output would have 13 columns of demand data, 1 listed for each month.

As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.

Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)

select
pm.HostPartID,
pm.partcustom1,
lt.loctypename,
lm.loccustom5,
lm.HostLocID,
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
dh.historyamount,
dh.historyschamount
from
DEMAND_HISTORY dh,
PART_MASTER pm,
LOCATION_MASTER lm,
LOC_TYPE lt
where
pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
(dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
order by
pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId

(I don't have to use (+) in my table joins as there will always be a match)

Guess placing the data into columns instead of rows is not as simple as I had hoped!?!|||well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.

Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.

You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.

Let me know Monday if you have not figured it out, I am leaving the office.|||If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

select col1, col2,
sum(case when month(datecol1)=1 then value1 else 0 end) month1,
...
sum(case when month(datecol1)=12 then value1 else 0 end) month12
from table1 ....
where ...
group by col1, col2

Creating New Table & Linking to Datas

Hi Everybody,
I have a table as "AdjSummary" with 5 columns. I have to create a
table with 2 columns from the above table. The structure of the table
"AdjSummary" is as below
INVNO ADJUST MODE PAYMENT MODE
10 100 OTH 0 NULL
11 150 OTH 0 NULL
11 100 DET 0 NULL
12 0 NULL 50 CSH
12 50 DET 50 VIS
12 12 DET 0 NULL
My new table should be with 2 columns (INVNO & ADJUST) as below.
INVNO ADJUST
10 100
11 250
12 62
Moreover, I want ADJUST column should be not equal to zero.
Pls help me in creating the table & to link the data to new table.
Thanks for your help in advance.
Regards,
Selvarathinam.Selvarathinam wrote:
> Hi Everybody,
> I have a table as "AdjSummary" with 5 columns. I have to create a
> table with 2 columns from the above table. The structure of the table
> "AdjSummary" is as below
> INVNO ADJUST MODE PAYMENT MODE
> 10 100 OTH 0 NULL
> 11 150 OTH 0 NULL
> 11 100 DET 0 NULL
> 12 0 NULL 50 CSH
> 12 50 DET 50 VIS
> 12 12 DET 0 NULL
> My new table should be with 2 columns (INVNO & ADJUST) as below.
> INVNO ADJUST
> 10 100
> 11 250
> 12 62
> Moreover, I want ADJUST column should be not equal to zero.
> Pls help me in creating the table & to link the data to new table.
> Thanks for your help in advance.
> Regards,
> Selvarathinam.
SELECT INVNO, ADJUST
--INTO MyTable
FROM AdjSummary
WHERE ADJUST <> 0
This will return the records to you in Query Analyzer. If you want to
keep a permament copy of the records, uncomming the INTO line above,
and change "MyTable" to a legitimate table name.|||Tracy McKibben wrote:
> Selvarathinam wrote:
> SELECT INVNO, ADJUST
> --INTO MyTable
> FROM AdjSummary
> WHERE ADJUST <> 0
> This will return the records to you in Query Analyzer. If you want to
> keep a permament copy of the records, uncomming the INTO line above,
> and change "MyTable" to a legitimate table name.
Sorry, make that:
SELECT INVNO, SUM(ADJUST)
--INTO MyTable
FROM AdjSummary
GROUP BY INVNO
HAVING SUM(ADJUST) <> 0

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Citizen is not important. Surname and Name are the only ones that are
"selective".
Would it be better to create two indexes - one on the Name column and
another on the Surname column, or would one index on both columns be
better?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Andy,
My hunch is that you should create the index on Surname, Firstname, but
why not try it out and find out how many logical reads are required in
each case? You will know for sure which is best then.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> Citizen is not important. Surname and Name are the only ones that are
> "selective".
> Would it be better to create two indexes - one on the Name column and
> another on the Surname column, or would one index on both columns be
> better?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:

>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
>UserID (PK),
>Name,
>Surname,
>DateOfBirth,
>Citizen,
>BranchCode,
>RegionCode,
>Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
>SET NOCOUNT ON
>SELECT UserID, Name, Surname
>FROM Personal
>WHERE Citizen = 'Yes'
>ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:
>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

creating index on calculated field in table -sql 2000

Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2
]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug

creating index on calculated field in table -sql 2000

Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegr oups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
...
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.
|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!
|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug

creating index on calculated field in table -sql 2000

Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 = something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jaynika" <jaynika@.gmail.com> wrote in message
news:1142892662.549052.163100@.i39g2000cwa.googlegroups.com...
Hi all,
we have a table with these columns,
col1 (int)
col2(bigint)
col3 ((convert(varchar(10),[col1]) + '_' + convert(varchar(20),[col2]))
...
..
colN
Where col3 is a unique key for our transaction system..
Question :
(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =something and f2 = somethingelse)
I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.
(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ' How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?
any help is appreciated.
thanks.|||Thanks TOM,
but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?
thx again!!!|||no, it wouldnt' rebuild the entire index.
The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug

Creating dates from table columns

Hi, i have a table with 3 ints that are used to store dates. The
datetime data type is not used because this data comes from an old
AS400 server.

I need to be able to use those 3 columns to build dates within a query
and be able to use them to compare themselves to other dates

Let's say the table has the following values:

myday mymonth myyear
23 5 2006

and suppose i want to do a query that displays all rows with date
greater than '20060520'

Here is the query i have tried:

select
cast(myday as varchar(2))+'/'+cast(mymonth as
varchar(2))+'/'+cast(myyear as varchar(4))
from mytable

That query returns the string '23/5/2006' yet i can't use it to compare
it with '20060520'

Is there a way i can do this in a simple query?
This is on sql server 2000This seems to work but it is way ugly and depends heavily on the date
format:

select
cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
from mytable
where cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
between '20060520' and '20061231'

Also, i tried to use an alias on the select but then it doesn't get
recognized on the where so i had to type it full again.

Anyone knows a better way? Asp has a dateserial() method that
constructs a date given a month , day and year, but sql server doesn't
seem to have anything like that as a sql date function.|||fjleon@.gmail.com wrote:
> Hi, i have a table with 3 ints that are used to store dates. The
> datetime data type is not used because this data comes from an old
> AS400 server.

You mean via a linked server or do you import it? If it's the latter
then preferably fix the dates at import before they get into the
database.

Try:

SELECT myday, mymonth, myyear,
DATEADD(DAY,myday,
DATEADD(MONTH,mymonth,
DATEADD(YEAR,myyear-2000,'19991231')))
FROM mytable ;

This returns a DATETIME, not a string.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||> Try:
> SELECT myday, mymonth, myyear,
> DATEADD(DAY,myday,
> DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myyear-2000,'19991231')))
> FROM mytable ;
> This returns a DATETIME, not a string.

Seems to be one month forward:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

10520062006-06-10 00:00:00.000
20520062006-06-20 00:00:00.000

I had to substract a month:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

I am guessing this only works from y2k forward. I don't quite
understand why i have to substract a month, but at least it works, and
it's a lot cleaner than casting around.|||Do you know how to use that constructed date in a WHERE? dateadd....
as mydate where '20060501' <=p for example doesn't work

The weird thing is that in sql analizer it shows mydate as the name of
the column but if i use it in the where it fails|||(fjleon@.gmail.com) writes:
> Seems to be one month forward:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> 10 5 2006 2006-06-10 00:00:00.000
> 20 5 2006 2006-06-20 00:00:00.000
> I had to substract a month:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> I am guessing this only works from y2k forward.

No, it works for dates in the 1900s as well.

> I don't quite understand why i have to substract a month,

When you've added 5 months, you are at the end of May. Now you
add some days. That brings you into June.

> Do you know how to use that constructed date in a WHERE? dateadd....
> as mydate where '20060501' <=p for example doesn't work
> The weird thing is that in sql analizer it shows mydate as the name of
> the column but if i use it in the where it fails

It's not weird at all. You cannot use a column alias defined in a query
anywhere else in the query, except in the ORDER BY clause. However, you
can use a derived table - a query within the query. See the script
below. I've also modofied David's expression in a way that I think is
more robust.

CREATE TABLE fjleon(myday int NOT NULL,
mymonth int NOT NULL,
myyear int NOT NULL)

INSERT fjleon (myday, mymonth, myyear)
VALUES (23, 5, 2006)
INSERT fjleon (myday, mymonth, myyear)
VALUES (12, 7, 1996)
INSERT fjleon (myday, mymonth, myyear)
VALUES (29, 2, 2004)
INSERT fjleon (myday, mymonth, myyear)
VALUES (1, 3, 2004)
go
SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon
go
SELECT myday, mymonth, myyear, mydate
FROM (SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon) AS x
WHERE mydate > '20040101'
go
DROP TABLE fjleon

--
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|||Thank you both, this works nice.