Resequence column

Any ideas how to resequence the numbers in a column after inserting or
updating a record. The column is not the Unique ID and is an integer column
it is used to allow users to select the order in which things are displayed
for them the column is displayOrder and the select statement would Order By
that column. I am trying to do this in a stored procedue but I am not
having any luckPlease read: www.aspfaq.com/5006
Can you provide some more information including DDLs, sample data & expected
results, so that others can better understand your requirements?
Anith|||Assuming it's just a sequential resequencing you want and there
are no foreign key issues:
create table test (id int, name varchar(50))
insert into test select 5, 'test 1'
insert into test select 6, 'test 2'
insert into test select 9, 'test 3'
select * from test
declare @.id int
set @.id = 0
update test
set @.id = id = @.id + 1
"Ian Galloway via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:9aea371c6f2e4868b71c18edec3f9a87@.SQ
webservertalk.com...
> Any ideas how to resequence the numbers in a column after inserting or
> updating a record. The column is not the Unique ID and is an integer
column
> it is used to allow users to select the order in which things are
displayed
> for them the column is displayOrder and the select statement would Order
By
> that column. I am trying to do this in a stored procedue but I am not
> having any luck|||Here is an example of what I am trying to do
tbl_Browsers
ID browserName browserVersion displayOrder
1 Intern Explorer 6.5 1
2 Intern Explorer 6.0 2
3 Intern Explorer 5.0 3
4 Intern Explorer 4.0 4
5 Firefox 1.1 5
5 Firefox 1.2 6
the displayOrder columns holds the users preference of which order he would
like to display these records, as he inserts new records he may want the
newer record to be diplayed third in the list so I created this procedure:
CREATE PROCEDURE sp_sptInsertTicketStatusType
(@.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
update tbl_Browser set displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
insert tbl_Browsers (browserName, browserVersion, displayOrder) values
(@.strName, @.strDescription, @.intDisplayOrder)
GO
Thhis would increment all the displayOrder Values by one where the
submitted display order is equal to or greater than the newly submitted
records displayOrder.
The problem is when editing the records to update the displayOrder things
can get out of sequence that way. I need a way to do the following
Edit a record - say ID 1 (IE 6.5) and make its displayOrder 3 - I need a
way to decrement the displayOrder of the records with displayOrder of 1
and 2 (or lesser values) by one and increment the others to fit
displayOrder 3 into the sequence so ultimately the table would look like
this - of course this would be dynamic as the display order can be any
value
ID browserName browserVersion displayOrder
2 Intern Explorer 6.0 1
3 Intern Explorer 5.0 2
1 Intern Explorer 6.5 3
4 Intern Explorer 4.0 4
5 Firefox 1.1 5
5 Firefox 1.2 6|||Not sure how you are accomodating multi-row inserts & updates with your
existing logic. Ignoring that, based on your narrative, you could do:
CREATE TABLE Browsers(
Browser VARCHAR( 20 ) NOT NULL,
Version DECIMAL( 2, 1 ) NOT NULL,
DisplayOrder INT NOT NULL
PRIMARY KEY ( Browser, Version ) ) ;
-- for inserts
BEGIN TRAN
UPDATE Browsers SET displayOrder = displayOrder + 1
WHERE displayOrder >= @.DisplayOrder;
INSERT Browsers SELECT @.Browser, @.version, @.DisplayOrder ;
COMMIT
-- for updates
BEGIN TRAN
UPDATE Browsers
SET displayOrder = displayOrder - 1
WHERE displayOrder <= @.DisplayOrder
...
UPDATE Browsers
SET displayOrder = @.DisplayOrder
WHERE Browser = 'Intern Explorer'
AND Version = 6.5
COMMIT TRAN
Anith|||I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.
This is the isert procedure
________________________________________
_____________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @.intDisplayOrder = 0
BEGIN
Set @.intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
BEGIN TRAN
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
ELSE
-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @.intDisplayOrder >= 1
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
WHERE displayOrder <= @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus
SET displayOrder = (displayOrder + 1)
WHERE displayOrder > @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
GO
_______________________
so using just the ID and displayOrder column here is what should happen
original data
ID diplayOrder
10 1
20 2
30 3
40 4
now the user may want to make ID 40 the first option in a list which should
make the table look like this
ID diplayOrder
40 1
10 2
20 3
30 4
Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this
ID diplayOrder
40 1
20 2
10 3
30 4
so when I pull the data I will use Order By displyOrder.
This and other tables using this method may have any number or rows.
Thanks for any assistance.
Message posted via http://www.webservertalk.com|||I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.
This is the isert procedure
________________________________________
_____________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @.intDisplayOrder = 0
BEGIN
Set @.intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
BEGIN TRAN
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
ELSE
-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @.intDisplayOrder >= 1
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
WHERE displayOrder <= @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus
SET displayOrder = (displayOrder + 1)
WHERE displayOrder > @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
GO
_______________________
so using just the ID and displayOrder column here is what should happen
original data
ID diplayOrder
10 1
20 2
30 3
40 4
now the user may want to make ID 40 the first option in a list which should
make the table look like this
ID diplayOrder
40 1
10 2
20 3
30 4
Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this
ID diplayOrder
40 1
20 2
10 3
30 4
so when I pull the data I will use Order By displyOrder.
This and other tables using this method may have any number or rows.
Thanks for any assistance.
Message posted via http://www.webservertalk.com|||Here's a revision of the procedure sans any transactions. Add them where
you like.
Try it out and let me know how it works out.
create PROCEDURE sp_sptInsertTicketStatusType
(@.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
declare @.olddisplayorder tinyint
select @.olddisplayorder = displayorder
from tbl_browser
where browserName = @.strBrowserName
and browserversion = @.strBrowserVersion
delete
from tbl_browser
where browserName = @.strBrowserName
and browserversion = @.strBrowserVersion
if @.intDisplayOrder > @.olddisplayorder
begin
update tbl_Browser
set displayOrder = (displayOrder - 1)
where displayorder between @.olddisplayorder and @.intDisplayOrder
end
if @.intDisplayOrder < @.olddisplayorder
begin
update tbl_Browser
set displayOrder = (displayOrder + 1)
where displayorder between @.intDisplayOrder and @.olddisplayorder
end
if (@.intDisplayOrder = 0)
select @.intDisplayOrder = max(displayorder) + 1
from tbl_Browser
insert tbl_Browser (browserName, browserVersion, displayOrder) values
(@.strBrowserName, @.strBrowserVersion, @.intDisplayOrder)
GO
"Ian Galloway via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:aaf79ff55d9d4406bc34ebdab258dc6f@.SQ
webservertalk.com...
> I hope this can clarify a bit more what my desired results are, using the
> table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
> able to select the display order of the record. For inserting new records
I
> use the following.
> This is the isert procedure
> ________________________________________
_____________
> -- If user selects default dispayOrder (0) then add it as the last item to
> display
> IF @.intDisplayOrder = 0
> BEGIN
> Set @.intDisplayOrder = (select count(displayOrder) from
> tbl_sptTicketStatus)+1
> BEGIN TRAN
> INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
> (@.strName, @.strDescription, @.intDisplayOrder)
> COMMIT TRAN
> END
> ELSE
> -- If a diplayOrder is entered into the user form then insert it into
he -
> - proper place and increment all records equal to or greater than it
> IF @.intDisplayOrder >= 1
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
> where displayOrder >= @.intDisplayOrder
> INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
> (@.strName, @.strDescription, @.intDisplayOrder)
> COMMIT TRAN
> END
> __________________________
> For the EDIT procedure I need to make sure that if the user changes the
> diplayOrder that the other records resequence accoringly, here is what I
> have now for the procedure, but it does not get the desired affect
> __________________________
> IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> ELSE
> IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
> WHERE displayOrder <= @.intDisplayOrder
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> ELSE
> IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus
> SET displayOrder = (displayOrder + 1)
> WHERE displayOrder > @.intDisplayOrder
>
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> GO
> _______________________
> so using just the ID and displayOrder column here is what should happen
> original data
> ID diplayOrder
> 10 1
> 20 2
> 30 3
> 40 4
> now the user may want to make ID 40 the first option in a list which
should
> make the table look like this
> ID diplayOrder
> 40 1
> 10 2
> 20 3
> 30 4
> Then the user may choose to make the displayOrder of ID 10 to become third
> which would make the table look like this
> ID diplayOrder
> 40 1
> 20 2
> 10 3
> 30 4
> so when I pull the data I will use Order By displyOrder.
> This and other tables using this method may have any number or rows.
> Thanks for any assistance.
> --
> Message posted via http://www.webservertalk.com|||Armando Prato, that worked great and as desired. Thank you for your
assistance.
Message posted via http://www.webservertalk.com|||After testing I realized that deleting and recreating the record was
changing the ID field of the record which is the Identity seed, where I am
using this as a foriegn key in another table this would have broken
relationships. So I modified the proc a bit to insert the existing ID of
the record as opposed to creating a new ID. In order to acheive this the
SET IDENTITY_INSERT flag had to be set to on.
I also wanted to add error checking so that if an error occurs I do not
have a half complete transaction deleting a record and not updating it
propoerly. Here is the finished code I hope now that it is set up properly,
if any one sees errors or drawbacks feel free to let me know.
CREATE PROCEDURE sptBrowserTypeEdit
(@.intID Int, @.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
SET IDENTITY_INSERT tbl_sptBrowsers ON
declare @.olddisplayorder tinyint
select @.olddisplayorder = displayorder
from tbl_sptBrowsers
where [ID] = @.intID
BEGIN TRAN
delete from tbl_sptBrowsers where [ID] = @.intID
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
if @.intDisplayOrder > @.olddisplayorder
BEGIN
BEGIN TRAN
UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder - 1) WHERE
displayOrder between @.olddisplayorder and @.intDisplayOrder
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
END
if @.intDisplayOrder < @.olddisplayorder
BEGIN
BEGIN TRAN
UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder + 1) WHERE
displayOrder between @.intDisplayOrder and @.olddisplayorder
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
END
IF (@.intDisplayOrder = 0)
SELECT @.intDisplayOrder = MAX(displayOrder) + 1 FROM tbl_sptBrowsers
BEGIN TRAN
INSERT tbl_Browser ([ID], browserName, browserVersion, displayOrder)
VALUES (@.intID, @.strBrowserName, @.strBrowserVersion, @.intDisplayOrder)
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
GO
Message posted via http://www.webservertalk.comsql

Repost: Solution didn't work any other ideas??

hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy

>--Original Message--
>Kamy,
>This will do it at least as you specified.
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =
t2.testcol AND t1.Ctr
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
records[vbcol=seagreen]
You need to update using a query and the PK. That identity would be a useful
PK for this
alter table tbl add seq int
update tbl set seq = (select count(*) from tbl t1 where t1.sheet = t.sheet
and t1.pk <= t.pk)
from tbl t
"Kamy" wrote:

> hi Ron,
> It didn't work, as soon as I add an identity column,
> it inserts running nos. into the field.
> Am I missing something?
> Thank you,
> -Kamy
> t2.testcol AND t1.Ctr
> message
> records
>
|||Nigel,
Thanks for taking time to reply! But this also does not
solve my problem, it increments all numbers in order from
1 to ...
I don't want it to do that, please take a look at my
results again, for a given set I would like it to
increment nos, and then start with 1 again.
Thank you again,
-Kamy

>--Original Message--
>You need to update using a query and the PK. That
identity would be a useful
>PK for this
>alter table tbl add seq int
>update tbl set seq = (select count(*) from tbl t1 where
t1.sheet = t.sheet[vbcol=seagreen]
>and t1.pk <= t.pk)
>from tbl t
>"Kamy" wrote:
dbo.test[vbcol=seagreen]
(1,1)
>.
>

Repost: Solution didn't work any other ideas??

hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy
>--Original Message--
>Kamy,
>
>This will do it at least as you specified.
>
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol = t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>>
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>>
>> Thank you in advance
>> -Kamy
>> .
>>
>>You need to update using a query and the PK. That identity would be a useful
PK for this
alter table tbl add seq int
update tbl set seq = (select count(*) from tbl t1 where t1.sheet = t.sheet
and t1.pk <= t.pk)
from tbl t
"Kamy" wrote:
> hi Ron,
> It didn't work, as soon as I add an identity column,
> it inserts running nos. into the field.
> Am I missing something?
> Thank you,
> -Kamy
> >--Original Message--
> >Kamy,
> >
> >This will do it at least as you specified.
> >
> >SET NOCOUNT ON
> >GO
> >IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
> >GO
> >CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
> >GO
> >INSERT test VALUES ('A001',0)
> >INSERT test VALUES ('A001',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A003',0)
> >INSERT test VALUES ('A003',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('C189',0)
> >INSERT test VALUES ('D9090',0)
> >GO
> >ALTER TABLE dbo.test add Ctr int not null identity(1,1)
> >GO
> >SELECT t1.TestCol
> > , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol => t2.testcol AND t1.Ctr
> >>= t2.Ctr)
> >FROM test t1
> >GO
> >ALTER TABLE dto.test DROP COLUMN Ctr
> >
> >Hope this helps,
> >Ron
> >--
> >Ron Talmage
> >SQL Server MVP
> >
> >"Kamy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
> >> I have a need to update data in a table so that
> >> a column contains the generated number for sets of
> records
> >> for instance I have fileName as listed and it should
> >> generate the SheetNum for each of the set
> >>
> >> FileName SheetNum
> >> A001 1
> >> A001 2
> >> A002 1
> >> A002 2
> >> A002 3
> >> A003 1
> >> A003 2
> >> B011 1
> >> B011 2
> >> B011 3
> >> C189 1
> >> D9090 1
> >>
> >> Thank you in advance
> >> -Kamy
> >> .
> >>
> >>
>|||Nigel,
Thanks for taking time to reply! But this also does not
solve my problem, it increments all numbers in order from
1 to ...
I don't want it to do that, please take a look at my
results again, for a given set I would like it to
increment nos, and then start with 1 again.
Thank you again,
-Kamy
>--Original Message--
>You need to update using a query and the PK. That
identity would be a useful
>PK for this
>alter table tbl add seq int
>update tbl set seq = (select count(*) from tbl t1 where
t1.sheet = t.sheet
>and t1.pk <= t.pk)
>from tbl t
>"Kamy" wrote:
>> hi Ron,
>> It didn't work, as soon as I add an identity column,
>> it inserts running nos. into the field.
>> Am I missing something?
>> Thank you,
>> -Kamy
>> >--Original Message--
>> >Kamy,
>> >
>> >This will do it at least as you specified.
>> >
>> >SET NOCOUNT ON
>> >GO
>> >IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE
dbo.test
>> >GO
>> >CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>> >GO
>> >INSERT test VALUES ('A001',0)
>> >INSERT test VALUES ('A001',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A003',0)
>> >INSERT test VALUES ('A003',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('C189',0)
>> >INSERT test VALUES ('D9090',0)
>> >GO
>> >ALTER TABLE dbo.test add Ctr int not null identity
(1,1)
>> >GO
>> >SELECT t1.TestCol
>> > , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =>> t2.testcol AND t1.Ctr
>> >>= t2.Ctr)
>> >FROM test t1
>> >GO
>> >ALTER TABLE dto.test DROP COLUMN Ctr
>> >
>> >Hope this helps,
>> >Ron
>> >--
>> >Ron Talmage
>> >SQL Server MVP
>> >
>> >"Kamy" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> >> I have a need to update data in a table so that
>> >> a column contains the generated number for sets of
>> records
>> >> for instance I have fileName as listed and it should
>> >> generate the SheetNum for each of the set
>> >>
>> >> FileName SheetNum
>> >> A001 1
>> >> A001 2
>> >> A002 1
>> >> A002 2
>> >> A002 3
>> >> A003 1
>> >> A003 2
>> >> B011 1
>> >> B011 2
>> >> B011 3
>> >> C189 1
>> >> D9090 1
>> >>
>> >> Thank you in advance
>> >> -Kamy
>> >> .
>> >>
>> >>
>>
>.
>

Repost - How to reset page numbers in PDF Report

Hello,
Looking for ideas on this one. Would like a report (single pdf file) whose page numbers would be say 1 of 5 and then 1 of 6, etc.. The page number resets would be based on the changing value of some field like summarybillnumber.
Any help/ideas would be appreciated.
ThanksAdd this to the Code property of the report:
shared offset as integer
public function GetPN(reset as boolean, pagenumber as integer) as integer
if reset
offset = pagenumber - 1
end if
return pagenumber - offset
end function
Use the function in the page number expression, basing the reset property on
the presence of something that only appears on the first page of the group.
For example:
="Page " & Code.GetPN(ReportItems!tag.Value = "Tag",Globals!PageNumber)
Note: To make this work, the offset member variable must be declared as
shared. This means the report cannot be run multiple times simultaneously,
otherwise the separate runs will smash the shared member variable's value.
In addition, failure to access the pages sequentially will have unexpected
results (since the shared offset member variable will be set out of order).
The only way to use this hack is to schedule runs of the report and save off
the rendered results. Users cannot run the report live or even from
snapshot history, since pagination occurs when the report is rendered, not
when the snapshot is taken.
The ability to conditionally reset page numbering on specific page breaks,
such as group page breaks, is on our wishlist for a future version, but this
feature is not likely to make it into SQL 2005.
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"chanley54" <chanley54@.discussions.microsoft.com> wrote in message
news:BDDF14C9-ACA0-4F8B-98BB-91839AAB6644@.microsoft.com...
> Hello,
> Looking for ideas on this one. Would like a report (single pdf file)
> whose page numbers would be say 1 of 5 and then 1 of 6, etc.. The page
> number resets would be based on the changing value of some field like
> summarybillnumber.
> Any help/ideas would be appreciated.
> Thanks
>

ReportViewer: Show toggle for specific item in a list??

Hello,
Wondering if anyone has any ideas about this:
I have ReportViewer on an ASP page with a report that has a list. The
list shows me rows and I want to be able to toggle just one of the
items in the list based on the type of item it is. Currently I can
only show the toggle icon for all the list items, like this:
+Item1
+Item2
+Item3
But I want the report to show only this:
Item1
+Item2
Item3
I've tried all manner of IIF formulas but none work. Is this even
possible?
ThanksPlease, does anyone have any experience with this?
On Mar 13, 6:11 pm, phrankbo...@.hotmail.com wrote:
> Hello,
> Wondering if anyone has any ideas about this:
> I have ReportViewer on an ASP page with a report that has a list. The
> list shows me rows and I want to be able to toggle just one of the
> items in the list based on the type of item it is. Currently I can
> only show the toggle icon for all the list items, like this:
> +Item1
> +Item2
> +Item3
> But I want the report to show only this:
> Item1
> +Item2
> Item3
> I've tried all manner of IIF formulas but none work. Is this even
> possible?
> Thanks