Newbie Question

How do I create a report in SQL?
I have a database created with a table that has names
I would like to print the table out by last name in ascending order.
Any ideas or help?
Thanks
Tom
----
I am using the free version of SPAMfighter for private users.
It has removed 61 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!If I'm understanding you correctly, you will want to open the Business
Intelligence Development environment, create a new reporting services
project and follow the report creation wizard and for the query enter
something like:
select * from NamesTable order by names
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Thomas Grassi wrote:
> How do I create a report in SQL?
> I have a database created with a table that has names
> I would like to print the table out by last name in ascending order.
> Any ideas or help?
> Thanks
> Tom
> ----
> I am using the free version of SPAMfighter for private users.
> It has removed 61 spam emails to date.
> Paying users do not have this message in their emails.
> Try SPAMfighter for free now!|||The way you have asked, I can make out your are very new... to SSRS..
Anyways the great way to start SSRS is to go to SQL 2005 online help
find "Reporting Services Tutorial" from the left pane and select the
"Creating a Basic Report" and go through all the 6 lessons, they are very
simple and you can start using RS for your purpose.
Amarnath, MCTS.
"Thomas Grassi" wrote:
> How do I create a report in SQL?
> I have a database created with a table that has names
> I would like to print the table out by last name in ascending order.
> Any ideas or help?
> Thanks
> Tom
> ----
> I am using the free version of SPAMfighter for private users.
> It has removed 61 spam emails to date.
> Paying users do not have this message in their emails.
> Try SPAMfighter for free now!
>
>

Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

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

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

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

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

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

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack