Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book a
nd
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530
Is there a question here? You certainly can create an index on a Bit column
but the question is do you really want to? Most of the time the selectivity
is too low to be of use for an index. But under certain conditions it makes
sense.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:3339C46D-605A-4522-852D-E67DDA36B8DF@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>
|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an Identity column to a SELECT statement

Hi all,
I am pretty much going insane. I have tried all sorts of things and gotten
nowhere, and I'm fairly sure there exists a simple solution to my problem.
If you help me, I will be eternally in your debt.
Here is the scenario. I have a stored procedure which conains a fairly heavy
UNION query, which I'm not going to repeat here. The data comes from all
sorts of places, and the data that comes back has no unique record
identifier. I want to add one, that is, effectively add a IDENTITY column to
the query result.
I have put the data from the UNION query into a table variable, called
@.myResults (I could put it into a temp table #myResult instead, if you
care). This kind of makes my problem simpler to see, but be aware that the
source tables have no unique identifier I can use. The column "myID" I have
just made up with zero value, in case it can be used.
SELECT myID, Color FROM @.myResults -- Simplified example, this is
what I get
0 Blue
0 Red
0 Green
I want to add a column or update the myID column so that it looks like this,
counting each row
-- This is what I want. How?!?!
1 Blue
2 Red
3 Green
Simple, eh? That's what I thought.
I have tried this --
SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
work
and
SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has number
3 on each row
what I want is something like this --
SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt as
far as I can tell
As a general thing, I'm not sure how to add an identity column to a table
that already has data in it. That's kind of what I am trying to do, but to a
select statement result.
I have even considered looping through each record in a cursor and manually
updating the int. Seems like a lot of work, and this stored procedure is
going to get hit a lot and needs to be fairly fast.
Mostly I'm just burning up because I *know* there is a simple answer to
this - I just can't see it!
Thanks in advance,
SaulLook at the IDENTITY function in Books Online; you basically want to do
something like:
SELECT IdentColumn = IDENTITY(int, 1,1),
OTHERColumns
INTO TargetTable --must be a table or temp table
FROM @.myResults
HTH
Stu|||I suggest you to insert data into a #TempTable like
Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From TableNam
e
-- That should generate record numbers for you
HTH
Ed
"Saul" wrote:

> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly hea
vy
> UNION query, which I'm not going to repeat here. The data comes from all
> sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
to
> the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I hav
e
> just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like thi
s,
> counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has numbe
r
> 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt a
s
> far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
a
> select statement result.
> I have even considered looping through each record in a cursor and manuall
y
> updating the int. Seems like a lot of work, and this stored procedure is
> going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>
>|||Guys,
Thank you for your responses!!
Yes, what you suggested works, and works quite well. What I guess I don't
like about it is that this means creating a temp table to solve the problem.
But it does work, so I'm not complaining! So, thanks again!!
On the way down to lunch, I thought of another solution though, which I like
better and also works. Here's the idea - when I intially declare the table
variable (@.myResults) I define an indentity column there. Then, when I do
the insert into.., I don't insert into that ID column, and it takes care of
creating the identity. What I preffer about this solution is that the
identity is built the first time when the data is being inserted.
eg
declare @.myResults table (my_ID int identity(1,1) , colour varchar(20))
insert into @.myResults
SELECT colour
FROM table1
UNION
SELECT colour
FROM table2
Of course, my real world query is vastly more complicated, but it's for
illustration purposes.
- Saul
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:7088713E-1340-4D8D-9328-C8F0DEAF251B@.microsoft.com...
>I suggest you to insert data into a #TempTable like
> Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From
> TableName
> -- That should generate record numbers for you
> HTH
> Ed
>
> "Saul" wrote:
>|||There is no "simple" way of doing this, and there may not every be. The
issue here is that you need to something to order the data on. If you have
some unique value, and you want to add a sequence number, you can do
something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
To do a non-sortable order, you will need to build the data first (don't
expect the order you see from a select to always be the order of the
results. There are no guarantees with row order in a relational database.)
(note, in 2005 there will be an easier way to do this, but the same
limitations do exist.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Saul" <sbryan@.nsw.counterpoint.com.au> wrote in message
news:4355a839$0$1360$c30e37c6@.ken-reader.news.telstra.net...
> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly
> heavy UNION query, which I'm not going to repeat here. The data comes from
> all sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
> to the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I
> have just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like
> this, counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has
> number 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt
> as far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
> a select statement result.
> I have even considered looping through each record in a cursor and
> manually updating the int. Seems like a lot of work, and this stored
> procedure is going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>

Creating an IDENTITY column in a view

Is it possible to create an IDENTITY column in a view? I would like to have a column which will always start at 1 and increment 1 for every record in the view.
Thanks!
CSThere might be another way to get what you want. It depends on your data. For example if you have a table that has unique rows you could write something like this:

SELECT
COUNT(*) AS ID,
A.Activity_Type_Ky
FROM
Activity_Type AS A
JOIN Activity_Type AS B
ON A.Activity_Type_Ky > B.Activity_Type_Ky
GROUP BY
A.Activity_Type_Ky

You could also use a function or stored procedure with a temporary table to get what you want if you are not limited to a view.|||I suggest you use a stored procedure to:

1. create a temporary table that includes the identity column
2. insert all the records of your view into the temporary table using single T-SQL statement
3. select * from the temprary table

in sql server 2000 you don't need to drop the temp table

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating a View with detailed informations

Hello,
I have a code for creating view in T-SQL. I want to ask you guys, i want to make this result set should grouped by DepoAdi column and StokKodu (this is an alias sure you can get it from code). Did i make it on group by line? My second problem is i want to add 2 columns to this query. This 2 column will calculate some values with SUM function and - operator. At CRM.Depolar.DepoBilgileri table i have a column named Miktar (this one stores int type datas) and i have a column named islemturu(this one stores 1 or 0). I want to calculate Miktar values which rows has islemturu column 0 and subtract them from which rows has islemturu column 1 value and this computing action must be based on Grouped columns.

DepoAdi | StokKodu | Miktar | IslemTuru
ABS SK101 5 0
ABS SK101 3 1
ABS SK102 4 0
ABS SK102 3 1

This is the table and i'm imaging view what i want now...

DepoAdi | StokKodu | Miktar
ABS SK101 2
ABS SK102 1

How can i add this resultset to my code..

Thanks for reading... Waiting your answers.. Happy coding...

CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT DepoBilgileri

FROM [CRM.Depolar]

WHERE Id IN (SELECT Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT ObjeGrubu

FROM [CRM.Objeler]

WHERE Id IN (SELECT Id

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE Id IN (SELECT KartBilgileri

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu

Select DepoAdi

, StokKodu

, (Giren - Cikan) As Miktar

From

(

Select Depo.DepoAdi

, Depo.StokKodu

, Sum(Depo.Miktar) As Giren

, 0 As Cikan

From DepoBilgileri As Depo

Where IslemTuru = 0

Group By Depo.DepoAdi, Depo.StokKodu

Union All

Select Depo.DepoAdi

, Depo.StokKodu

, 0 As Giren

, Sum(Depo.Miktar) As Cikan

From DepoBilgileri As Depo

Where IslemTuru = 1

Group By Depo.DepoAdi, Depo.StokKodu

) As Core

|||

Thanks for your reply.. I solved problem with making some changes in my code.

CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu,

(SELECT SUM(CASE [CRM.Depolar.DepoHareketleri].IslemTuru

WHEN 0

THEN [CRM.Depolar.DepoHareketleri].Miktar

ELSE

-1 * [CRM.Depolar.DepoHareketleri].Miktar

END)

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].Depo, [CRM.Depolar.DepoHareketleri].StokKart) AS Miktar,

(SELECT SUM(CASE [CRM.Depolar.DepoHareketleri].IslemTuru

WHEN 0

THEN [CRM.Depolar.DepoHareketleri].Tutar

ELSE

-1 * [CRM.Depolar.DepoHareketleri].Tutar

END)

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].Depo, [CRM.Depolar.DepoHareketleri].StokKart) AS Tutar

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT [CRM.Depolar].DepoBilgileri

FROM [CRM.Depolar]

WHERE [CRM.Depolar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT [CRM.Objeler].ObjeGrubu

FROM [CRM.Objeler]

WHERE [CRM.Objeler].Id IN (SELECT [CRM.StokKartlar].Id

FROM [CRM.StokKartlar]

WHERE [CRM.StokKartar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].StokKart

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].StokKart)))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT [CRM.StokKartlar.KartBilgileri].StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE [CRM.StokKartlar.KartBilgileri].Id IN (SELECT [CRM.StokKartlar].KartBilgileri

FROM [CRM.StokKartlar]

WHERE [CRM.StokKartlar].Id IN (SELECT [CRM.Depolar.DepoHareketleri].StokKart

FROM [CRM.Depolar.DepoHareketleri]

GROUP BY [CRM.Depolar.DepoHareketleri].StokKart)))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu

Creating a unique constarint on a multiple null column

HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

Yes.

Since a Primary Key CONSTRAINT requires NOT NULL values, a UNIQUE index is the best alternative method to force a constraint on columns that can contain NULL values.

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit table
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> > Good Day All,
> >
> > I am trying to create a trigger on a table and this trigger must update
> >
> > an Audit table which reflects the column name (the changes apply to),
> > the old value and the new value.
> >
> >
> > I have tried running through a cursor to dynamically update the Audit
> > table with the individual fields but this does not work since when
> > selecting from the inserted or deleted table one can either select all
> > fields or certain fields but I find it difficult to select only values
> > for the field that is current on my cursor.
> >
> >
> > I really will appreciate your help.
> >
> >
> > Regards,
> > Phonzo.
> >

Creating a trigger on a table using a cursor.

Good Day All,
I am trying to create a trigger on a table and this trigger must update
an Audit table which reflects the column name (the changes apply to),
the old value and the new value.
I have tried running through a cursor to dynamically update the Audit
table with the individual fields but this does not work since when
selecting from the inserted or deleted table one can either select all
fields or certain fields but I find it difficult to select only values
for the field that is current on my cursor.
I really will appreciate your help.
Regards,
Phonzo.I would caution against using a cursor inside a trigger.
Normally, when creating Audit trails, it is only necessary to append the
contents of deleted and/or inserted to the Audit table. And the Audit table
'should' have at least a couple of additional columns: 'WhoDoneIt' default
SYSTEM_USER, 'WhenDoneIt' default getdate().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...
> Good Day All,
> I am trying to create a trigger on a table and this trigger must update
> an Audit table which reflects the column name (the changes apply to),
> the old value and the new value.
>
> I have tried running through a cursor to dynamically update the Audit
> table with the individual fields but this does not work since when
> selecting from the inserted or deleted table one can either select all
> fields or certain fields but I find it difficult to select only values
> for the field that is current on my cursor.
>
> I really will appreciate your help.
>
> Regards,
> Phonzo.
>|||Hi Arnie,
Thanks a lot for this info. Much appreciated.
Thanks,
Regards,
Phonzo.
Arnie Rowland wrote:[vbcol=seagreen]
> I would caution against using a cursor inside a trigger.
> Normally, when creating Audit trails, it is only necessary to append the
> contents of deleted and/or inserted to the Audit table. And the Audit tabl
e
> 'should' have at least a couple of additional columns: 'WhoDoneIt' default
> SYSTEM_USER, 'WhenDoneIt' default getdate().
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Phonzo" <alphonse.zulu@.treehousemis.com> wrote in message
> news:1157552615.607572.152860@.m73g2000cwd.googlegroups.com...

Creating a table from the Rows of Other table

How can I create table from the rows of other table?

My requirement is I have a table test which has a column Abc with some values say a,b,c,d,e

Is it possible to create a table which has the column names as a,b,c,d,e...

Since the rows in the test table are dynamic...is it possible to create a table with the dynamic columns?

Any pointers in this regard?

Yes.. You can do this. Use INTO clause on your Select Statement.

Select A,B,C,D,E INTO NEWTABLE from ABC

|||

Thanks Sekaran.

But my problem is I am not sure the number of rows in my first table.

i,e if I do Select * from temp and it it returns 10 rows then those 10 rows should be the column names in my second table.and if there are only 5 rows then my second table should have 5 columns only.

|||

Ok.. You want to create table using your Rows..

I am not sure why you need this.. This is not good idea to create a table on the fly.

Are you want to convert the Row wise data into column? Something like PIVOT table.

Give more info...

|||

tried with PIVOT it doesnt seem to work out .. as i dont have an INTEGER on which i can pivot

and i dont know what would my for() will have.

see this is my case:

I've a table A with columns a1,a2,a3

i've table B with column b1 and values a4,a5 (offcourse the number of rows in b1 always vary)

select a1,a2,a3 from A

pivot

max(?)

for ([?],[?].....)

order by ?

and above all.....I'm just trying to create the schema and surely not going for the population at this moment

awaiting for your quick reply

|||

I've a table A with columns a1,a2,a3

i've table B with column b1 and values a4,a5 (offcourse the number of rows in B always vary)

and my resulting table C should be having a1,a2,a3,a4,a5 columns

|||

If I understand you want the table to have the same schema as table A but in addition to also have the data values in table B as additional columns?

The only way I can think is to create the table using dynamic sql using syscolumns to generate the first part of the SQL and then cursoring through the datavalues in table be to generate the remaining SQL.

I'm not sure why you would do this but if you need to then I suggest that you strictly control the entries in table B.

|||

Thanks Sunny,

But there is no way that I can restrict the entries in table B. But at the max there will be 30-40 rows which needs to be changed as the header for other report.

Can trigger help me in my case?

|||

Still your problem is not clear, help us understand in better way.

Pls put some proper sample data rather A,B,C & a1, a2 ...

Its confusing buddy..

|||

Here is the example code:

Create table Meta(Columnname varchar(20))

Insert into Meta values('EmpNo')

Insert into Meta values('EmpName')

Insert into Meta values('Address')

Now if I do select * from Meta the result will be

EmpNo

EmpName

Address

Then I have another table Employee with two columns(Tel# ,SSN)

So my requirement is to change the schema of Employee table as (EmpNo,EmpName,Address,Tel#,SSN)

This is just an example as the number of rows in the Meta table is not known.

|||

Ok. You want to change the database (table) structure when you insert any new column on META table.

I don't recommand this. This is not a good practice at all.

If there is any schema change it should be done via proper script & by one hand(most of the time DBA).

I am really not sure why this dangerous logic you took in your hand.

If you ask me strightly its possible to do via trigger... But take care, take care on Update/Delete of your META data.

You may mess-up lot of dependent SPs, Views, Functions & even on your UI .

Create Trigger Meta_Trigger On Meta

For Insert

as

Begin

Declare @.Q as varchar(1000);

Select @.Q ='Alter Table Employee Add ' + ColumnName + ' Varchar(1000)' From Inserted;

Exec(@.Q)

End

|||

sekharan....how will i get the "columnname" in the above case

my problem was always getting the variable name here!!!

|||Whenever you insert the new column from your variable to the Meta table the trigger will find the newly inserted value using the INSERTED spl table...

creating a table column that only takes data from another table.

I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?

You could create a trigger on the table or a rule?|||The term which applies to your situation is called "referential integrity". To enforce referential integrity in your situation, you could use aFOREIGN KEY constraint in your table which limits the possibilities in the State column to only those values in the 'states' table.sql

creating a table column that contains long string

I have made a database inside a C# project using project --> Add New Item --> SQL Database.

I have made its tables and define the columns, but I need a data type that allows inserting a long string , as a news paper .

When I use the 'text' data type , the inserted document can't entered as a whole, only subdocument can be entered.

I need also to know how can I create a table that contains Objects .

of any created class.

Thanks,

Aya.

The text type can hold up to 2GB of data. Should be enough no?

Could you show us your insert procedure?

|||

Ok thanks.

I just detect an error in my inserting process.

But I need to know whether I can create a table that contains Objects or not.

And also if I can create a table that contains lists or not.

Thanks,

Aya.

|||

Hi,

You should be aware that the 'text' datatype is being deprecated, please look at varchar(max)/nvarchar(max) as a replacement, which should give you a better user experience in general.

Could you define what do you mean by Objects and Lists? Maybe there are other ways to achieve what you are trying to do. Please elaborate.

Thanks!

-Mat

Creating a string from Date Fields

I have a table with a startdatetime and an enddatetime column such as:

StartDateTime EndDateTime what I want to see returned
is:
01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
1:30PM"
01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
10:30AM"

Maybe someone has accomplished this aready in a stored procedure and
has an example of how to do it?
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?

Looks like you need to use the following T-SQL functions/operators:

convert() - to format the date.
substring() - to extract the portions of the end time you want to display
CASE - to determine whether all of or just part of endtime is to be
included.

Then again, a lot of these display issuses are often best handled client
side.

The above-mentioned functions are all documented in Books Online, see
the T-SQL Reference. convert() may be tricky to find, as it is under
the topic CAST and CONVERT.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401291443.47b9d2c8@.posting.google.c om...
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?
> lq

You could do this with CONVERT() and various string functions, but it would
be better to use your client application to handle this. The dates above are
not correct for most European formats, for example, and it's much easier to
deal with client locale settings in a client-side application.

Simon

Creating a stored proc that references an optional column

Is it possible to create a stored proc which references a non-existent
field without getting the "invalid column name" error?
The field will only exist in one or two production databases (not in
all) but we still want the stored proc to exist in all databases so
that users in the future may add the new field to their own database
(using a Dictionary builder form that exists in the application) and
then be able to run the report (via the stored procedure) that
references the field.
The stored proc checks that the field exists before proceeding.
ThanksIt is possible only if the table does not exist when the procedure is
created. However, this is not a good idea. It would be better to create
two different procedures.
Razvansql

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
.. ... ... 4
Howto achieve it?
TIA
Ana
That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>
|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
--
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribió en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Sorry, I didn't explain myself well. The result of a query is a ranking
>> based on customers' sales.
>> The fields from a table are:
>> Cust_ID
>> Sales
>> Date (European format)
>> The query generates the following results:
>> Cust_ID Sales Date
>> 526 12.350 12/5/2007
>> 632 11.520 5/5/2007
>> 123 10.899 6/6/2007
>>
>> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
>> Customer ID 632 generated 11.520 euros so should be 2.
>> And Cust. ID 123 should be 3. and etc.
>> So I was wondering if a column can be generated in a query which would
>> label the ranking from 1 to wherever ends the query. Meaning, if I have
>> 10 rows so will be till 10.
>> Hope I have been a bit clearer.
>> Thank you much for your prompt response.
>> Ana
>>
>> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
>> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>>
>sql

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en e
l
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>