Adding Sequence number in SQL

Being a newbie to SQL programming, was hoping someone would be able to
guide me in the right direction.
I have a table that has the following data
Center Emp_ID
11 112
11 2254
11 346
12 456
12 138
13 8761
Etc, etc
I want to add a Sequence number which resets to 1 by center. So I want
it to look like this
List_ID Center Emp_ID
1 11 112
2 11 2254
3 11 346
1 12 456
2 12 138
1 13 8761
I've done the following:
SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
(SELECT COUNT(*)
FROM dbo.Planning_Heads e2
WHERE e2.ACCT_CD <=
dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
FROM dbo.Planning_Heads
ORDER BY ACCT_CD
but it's no "restarting" the List_Id or incrementing it properly
Sorry for the long post, but thought it would be best to give as much
info as I couldWhich criteria can we use to tell sql server that Emp_ID = 346 goes after
Emp_ID = 2254, other than analyzing row by row?.
In db [northwind], the orders for each customer are stored in table [orders]
and the column [orderid] is an identity one that we can use to sort them
chronologically by customer.
use northwind
go
select
count(*) as rank,
a.orderid,
a.employeeid
from
dbo.orders as a
inner join
dbo.orders as b
on a.employeeid = b.employeeid
and a.orderid >= b.orderid
group by
a.employeeid,
a.orderid
order by
a.employeeid,
rank
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"kimmal" wrote:

> Being a newbie to SQL programming, was hoping someone would be able to
> guide me in the right direction.
> I have a table that has the following data
> Center Emp_ID
> 11 112
> 11 2254
> 11 346
> 12 456
> 12 138
> 13 8761
> Etc, etc
> I want to add a Sequence number which resets to 1 by center. So I want
> it to look like this
> List_ID Center Emp_ID
> 1 11 112
> 2 11 2254
> 3 11 346
> 1 12 456
> 2 12 138
> 1 13 8761
> I've done the following:
> SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
> (SELECT COUNT(*)
> FROM dbo.Planning_Heads e2
> WHERE e2.ACCT_CD <=
> dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
> FROM dbo.Planning_Heads
> ORDER BY ACCT_CD
> but it's no "restarting" the List_Id or incrementing it properly
> Sorry for the long post, but thought it would be best to give as much
> info as I could
>

Adding number of decimal places during table design

Hello,

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

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

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

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

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

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

What simple thing I am not doing ?

Cheers
NealPut values in Scale properties,you will find it just below precision if you are creating tables in EM design Table option...|||Many thanks.
Neal

Adding data in SQL server

This is probably a very basic question, so thanks in advance for not flaming the newbie.

I created a sample database in Access, then converted it into SQL Server format using the import/export wizard. I'm doing the "Creating a Distributed Application" walkthrough in Visual Studio and everything has compiled and runs fine. My problem is that I just created an empty database in access. It only has one table with two fields (a names table with lastName and firstName fields.) When I run the project I created in Visual Studio, I don't get any errors, but the data grid that I added doesn't have any data. This isn't surprising, because it is an empty data base. I'm very new to SQL Server and can not for the life of me figure out how to add data to a database. I feel dumb even asking such a question, but how do I create some dummy first and last names?

Thanks for the help.Use an insert statement In Query Analyser.

If your table is called names it would be something like:

INSERT Names VALUES('John', 'Smith')
INSERT Names VALUES('Jane', 'Doe')

Or you can open the table in query analyser and type the data directly into the table.

Hope this helps

Brent

Originally posted by orangewater
This is probably a very basic question, so thanks in advance for not flaming the newbie.

I created a sample database in Access, then converted it into SQL Server format using the import/export wizard. I'm doing the "Creating a Distributed Application" walkthrough in Visual Studio and everything has compiled and runs fine. My problem is that I just created an empty database in access. It only has one table with two fields (a names table with lastName and firstName fields.) When I run the project I created in Visual Studio, I don't get any errors, but the data grid that I added doesn't have any data. This isn't surprising, because it is an empty data base. I'm very new to SQL Server and can not for the life of me figure out how to add data to a database. I feel dumb even asking such a question, but how do I create some dummy first and last names?

Thanks for the help.|||Since you are familiar with MS Access you could create a link table in Access pointing to the SQL Server table and then enter data that way as well.|||OK, I'm confused. When I type in this query into the Query Analyzer:

INSERT names VALUES('John', 'Smith')
INSERT names VALUES('Jane', 'Doe')

I get the following error message:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'names'.

The name of the database is names, the table is called name and it has firstName and lastName entities. when i click on the dbo.name table in sql server, Edit is grayed out. why is this?

why is something so common completely obfuscated in SQL server?|||Here is an example:

create table myTbl (myKey varchar(20), myValue int)
go
insert myTbl (myKey , myValue) values ('a.1.red',99)
insert myTbl (myKey , myValue) values ('a.1.blue',7)
insert myTbl (myKey , myValue) values ('a.2.red',88)
insert myTbl (myKey , myValue) values ('a.2.blue',5)
insert myTbl (myKey , myValue) values ('b.1.red',9)
insert myTbl (myKey , myValue) values ('b.1.blue',1)
insert myTbl (myKey , myValue) values ('b.2.red',8)
insert myTbl (myKey , myValue) values ('b.2.blue',6)
go|||If the table is called name then modify:

INSERT name VALUES('John', 'Smith')
INSERT name VALUES('Jane', 'Doe')

name is whatever the table is called. If your table was called contacts then it would be:

INSERT contacts VALUES('John', 'Smith')
INSERT contacts VALUES('Jane', 'Doe')

Brent

Originally posted by orangewater
OK, I'm confused. When I type in this query into the Query Analyzer:

INSERT names VALUES('John', 'Smith')
INSERT names VALUES('Jane', 'Doe')

I get the following error message:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'names'.

The name of the database is names, the table is called name and it has firstName and lastName entities. when i click on the dbo.name table in sql server, Edit is grayed out. why is this?

why is something so common completely obfuscated in SQL server?|||OK...I figured it out. It's really easy to add data in SQL server, without adding links to an Access database or INSERT statements. All I did was navigate to the table in Enterprise Manager, right click on Open and then click "Return all rows." This pulls up an Access style representation of the data and allows you to easily enter or edit the data.

Thanks to all for your help though! Now if only I could get the update button in the web service to work....|||If you are having problems with UPDATE you should try SQL Profiler to verify the SQL statement is making it to SQL Server. If it is, then COPY/PASTE the statement from SQL Profiler into SQL Analyzer and run the SQL statement. When executing the SQL statement in Analyzer make sure you are logged in with the same UserID used for the Web to verify permissins as well.

Adding comments to TEXT field...

Newbie question:

Aside from the single quote (i.e. chr(39)) what other characters can cause
MS-SQL
server to cough-up the insertion / update back in your face?

TIAA. Nonymous (someone@.hotmail.com) writes:
> Aside from the single quote (i.e. chr(39)) what other characters can cause
> MS-SQL
> server to cough-up the insertion / update back in your face?

None, what I can think of. Well if you use the double " as delimiter,
then this is the odd one out. But you can only use " with certain
settings.

Anyway, to include the string delimiter in the string, you double it:

SELECT 'Three o''clock'

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding a variable to a stored procedure

Hi,

I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.

Help!!!

I want to add @.OrderBy at the end of :

------
SELECT *
FROM Annonces
WHERE CategoryID = @.CategoryID AND DateOnCreate >= @.DateFinale
------

My declaration :

Declare @.OrderBy nvarchar(50)

If @.Sort = 'date_de'
set @.OrderBy = ' ORDER BY DateOnCreate DESC'
Else
set @.OrderBy = ' ORDER BY DateOnCreate ASC'

You want your code to be built dynamically within in the SQL it sounds like.

I don't think you can do that. Variables can not have inline code in SQL.

|||

You can use a CASE in the ORDER BY with limited flexibility. Do a quick google search or search for my previous post. I think I posted a similar sample a while back.

|||

Dynamic SQL is the classic solution to this, if you need complete flexibility, as Kelsey said. CASE is way less flexible.

You can also write the query in SQLCLR code, if you're using SQL Server 2005 and need lots of flexibility.

Or you can wait until SQL Server 2008, which reportedly will have this capability.

Don

|||

Ok thanks,

Now, how can i add this line

--> AND dbo.fn_GetDistance(@.CodePostal, UserNumber) <= @.Dist <--

In this code :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + '''' + @.OrderBy

I tried many things but all failed.

Thanks|||

In fact, if the dataset is small, your front end is the best place to do this dynamic sorting.

|||

This should give you an idea.:

Declare @.stmtvarchar(1000), @.CodePostalvarchar(10), @.CategoryIDint, @.DateFinaledatetime, @.orderbyvarchar(10)Select @.CodePostal ='567', @.CategoryID = 10, @.DateFinale =getdate(),@.orderby =' name asc'select @.stmt ='SELECT * , dbo.fn_GetDistance(''' + @.CodePostal +''', UserNumber) AS DistanceFROM AnnoncesWHERE CategoryID =' +convert(Varchar,@.CategoryID) +' AND DateOnCreate >= ''' +convert(varchar,@.DateFinale) +'''' + @.OrderBy

|||

Hi,

I tried :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + ''' AND dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) <=' + @.Dist + '' + @.OrderBy

But i get :

Conversion failed when converting the varchar value 'SELECT *, .........blablabla....... <=' to data type int.

I dont understand?

|||

ok i got it!!!

Finally...

My error was i tried to use an integer in a varchar statement.

So i use:

Set@.Distance =convert(Varchar, @.Dist)

And now i can do:

select@.stmt ='SELECT *, dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID ='+ @.CategoryID +' AND DateOnCreate >='''+ @.DateFinale +''' AND dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) <='+ @.Distance +''+ @.OrderBy