Declared preference (rank) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Declared preference (rank)

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-11-24 : 14:35:01

Hi,

I am working on a tool for parts catalogs
It has an area where a designer can make declarative matches and then set preferences on which match they want to use as default (rank 1) and other less prefered but still valid choices.


CREATE TABLE partalternates2 (
	partno varchar (255) NOT NULL ,
	partnodesc varchar (255) NULL ,
	alternate varchar (255) NOT NULL ,
	alternatedesc varchar (255) NULL ,
	catalog varchar (24) NULL ,
	process varchar (24) NULL ,
	rank int NULL ,
	owner varchar (50) NULL ,
	CONSTRAINT PK_partalternates2 PRIMARY KEY  CLUSTERED 
	(
		partno,
		alternate
	)
)


INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Widget', 'Our most common widget since 1995', 'WidgetBLA',
'Our most common widget since 1995 (black)', 'CustomerA', 'BUY', 1, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Widget', 'Our most common widget since 1995', 'WidgetBLU',
'Our most common widget since 1995 (blue)', 'CustomerA', 'BUY', 2, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Widget', 'Our most common widget since 1995', 'WidgetGRY',
'Our most common widget since 1995 (grey)', 'CustomerA', 'BUY', 3, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'ThingamabobX0001',
'Attaches to fluxcapacitor', 'CustomerA', 'MAKE', 1, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'Thing-E-MABOB',
'Attaches to online fluxcapacitor', 'CustomerA', 'MAKE', 2, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'BOBAMATHING',
'Attaches to inverted fluxcapacitor', 'CustomerA', 'MAKE', 3, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'doohicky',
'Attaches to online fluxcapacitor', 'CustomerA', 'MAKE', 4, 'jones')

The idea is the Proc would take the (partno,alternate) key and a new rank number and resequence the rank of all same partno's


Declare @partno varchar(255)
Declare @alternate varchar(255)
Declare @newrank int

SET @newrank = 1
SET @partno = 'Thingamabob'
SET @alternate = 'ThingamabobX0001'

so far


UPDATE partalternates2 SET rank = @newrank where partno = @partno AND alternate = @alternate

CREATE TABLE #RemainingRanks
(
seq int IDENTITY (1, 1),
partno varchar(255),
alternate varchar(255)
)

INSERT INTO #RemainingRanks (partno,alternate)
SELECT partno, alternate  FROM tool.dbo.partalternates2
WHERE (partno = @partno) AND (alternate <> @alternate) ORDER BY rank

--SELECT * FROM #RemainingRanks

UPDATE F SET rank = CASE WHEN F.rank < @newrank  THEN C.seq
			WHEN  F.rank = @newrank THEN C.seq
			WHEN F.rank > @newrank THEN F.rank   
			ELSE 1000 END  -- trouble here because I'm dividing it up into bits rather than......
FROM  partalternates2 F JOIN #RemainingRanks C ON
F.partno = C.partno AND F.alternate = C.alternate


DROP TABLE #RemainingRanks

--SELECT partno, partnodesc, alternate, alternatedesc, catalog, process, rank, owner FROM tool.dbo.partalternates2
--order by partno, rank

-- use below to reset
-- UPDATE partalternates2 SET rank = 1 where alternate = 'Thing-E-MABOB'
-- UPDATE partalternates2 SET rank = 2 where alternate = 'ThingamabobX0001'
-- UPDATE partalternates2 SET rank = 3 where alternate = 'BOBAMATHING'
-- UPDATE partalternates2 SET rank = 4 where alternate = 'doohicky'

desired


before
Thingamabob	Attaches to fluxcapacitor	Thing-E-MABOB	Attaches to online fluxcapacitor	CustomerA	MAKE	1	jones
Thingamabob	Attaches to fluxcapacitor	ThingamabobX0001	Attaches to fluxcapacitor	CustomerA	MAKE	2	jones
Thingamabob	Attaches to fluxcapacitor	BOBAMATHING	Attaches to inverted fluxcapacitor	CustomerA	MAKE	3	jones
Thingamabob	Attaches to fluxcapacitor	doohicky	Attaches to online fluxcapacitor	CustomerA	MAKE	4	jones
							
							
after						
Thingamabob	Attaches to fluxcapacitor	Thing-E-MABOB	Attaches to online fluxcapacitor	CustomerA	MAKE	2	jones
Thingamabob	Attaches to fluxcapacitor	ThingamabobX0001	Attaches to fluxcapacitor	CustomerA	MAKE	1	jones
Thingamabob	Attaches to fluxcapacitor	doohicky	Attaches to online fluxcapacitor	CustomerA	MAKE	4	jones
Thingamabob	Attaches to fluxcapacitor	BOBAMATHING	Attaches to inverted fluxcapacitor	CustomerA	MAKE	3	jones

"it's definitely useless and maybe harmful".

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-11-25 : 10:03:31

Solved (maybe!)

I wish I could describe well what this is useful for..
If you have a very large Cross reference catalog that you want to persist preferences (which is close to "Knowledge Capture" in this case) by keeping the sequences of part equivalents "tight". Then you can approach a default automated transformation of one known set of parts, say the ones used by a Design Environment to a different but equivalent set of parts, say a purchasing item master. Then the transformation can be interpreted as "GOOD" when all Design parts have a match of rank 1. Or if not those parts that don't have a specific rank 1 equivalent can be recognized as variances and corrected. The owner or catalog fields can extend the persistance filtered to specific individuals.

I found comments that there maybe a solution to this in SQL for Smarties, some other guy called it a "puzzle" for a php dynamic list order persistance, but whatever. Don't know if the pattern will go to production yet but I needed to know if it is possible.

Can't help but think there is a better way using a self join,
a count, and/or this [url]http://www.sqlteam.com/item.asp?ItemID=765[/url]

but anyway


CREATE TABLE partalternates2 (
	partno varchar (255) NOT NULL ,
	partnodesc varchar (255) NULL ,
	alternate varchar (255) NOT NULL ,
	alternatedesc varchar (255) NULL ,
	catalog varchar (24) NULL ,
	process varchar (24) NULL ,
	rank int NULL ,
	owner varchar (50) NULL ,
	CONSTRAINT PK_partalternates2 PRIMARY KEY  CLUSTERED 
	(
		partno,
		alternate
	)
)


INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Widget', 'Our most common widget since 1995', 'WidgetBLA',
'Our most common widget since 1995 (black)', 'CustomerA', 'BUY', 1, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Widget', 'Our most common widget since 1995', 'WidgetBLU',
'Our most common widget since 1995 (blue)', 'CustomerA', 'BUY', 2, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Widget', 'Our most common widget since 1995', 'WidgetGRY',
'Our most common widget since 1995 (grey)', 'CustomerA', 'BUY', 3, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'ThingamabobX0001',
'Attaches to fluxcapacitor', 'CustomerA', 'MAKE', 1, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'Thing-E-MABOB',
'Attaches to online fluxcapacitor', 'CustomerA', 'MAKE', 2, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'BOBAMATHING',
'Attaches to inverted fluxcapacitor', 'CustomerA', 'MAKE', 3, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'doohicky',
'Attaches to online fluxcapacitor', 'CustomerA', 'MAKE', 4, 'jones')

INSERT INTO tool.dbo.partalternates2(partno, partnodesc, alternate,
alternatedesc, catalog, process, rank, owner)
VALUES('Thingamabob', 'Attaches to fluxcapacitor', 'E-doohicky',
'Attaches to main switch fluxcapacitor', 'CustomerA', 'MAKE', 5, 'jones')


--So now I want to update the rank of 
--('Thingamabob', 'ThingamabobX0001') via @newrank

Declare @partno varchar(255)
Declare @alternate varchar(255)
Declare @newrank int
Declare @oldrank int

SET @newrank = 2  -- change this up and down! Weeeeee!!
SET @partno = 'Thingamabob'
SET @alternate = 'ThingamabobX0001'

SET @oldrank = (SELECT rank FROM partalternates2 where partno = @partno AND alternate = @alternate)

UPDATE partalternates2 SET rank = @newrank where partno = @partno AND alternate = @alternate

-- and begin the code to enable the rest of the subset of 'Thingamabob' to maintain a "tight" sequence

CREATE TABLE #RemainingRanks
(
seq int IDENTITY (1, 1),
partno varchar(255),
alternate varchar(255)
)

INSERT INTO #RemainingRanks (partno,alternate)
SELECT partno, alternate  FROM tool.dbo.partalternates2
WHERE (partno = @partno) AND (alternate <> @alternate) ORDER BY rank

-- Don't really need ORDER BY here since we are just using #RemainingRanks
-- as the set to ACT (adjust old rankings to the new sequence) upon

SELECT * FROM #RemainingRanks

IF @newrank <> @oldrank

BEGIN

	UPDATE F SET rank =  CASE WHEN @newrank < @oldrank THEN
				CASE WHEN F.rank < @newrank  THEN F.rank
				WHEN  F.rank = @newrank THEN F.rank + 1
				WHEN F.rank > @newrank AND F.rank < @oldrank THEN F.rank + 1
				WHEN F.rank > @oldrank THEN F.rank
				ELSE 1000 END
			     ELSE
				CASE WHEN F.rank < @newrank  AND F.rank < @oldrank THEN F.rank 
				WHEN  F.rank = @newrank THEN F.rank - 1 
				WHEN F.rank > @oldrank AND F.rank < @newrank  THEN F.rank - 1
				WHEN F.rank > @oldrank AND F.rank > @newrank THEN F.rank
				ELSE 1000 END
			     END	
	FROM  partalternates2 F JOIN #RemainingRanks C ON
	F.partno = C.partno AND F.alternate = C.alternate

END

DROP TABLE #RemainingRanks

SELECT partno, partnodesc, alternate, alternatedesc, catalog, process, rank, owner FROM tool.dbo.partalternates2
order by partno, rank
-- use to reset for repeated trials
-- UPDATE partalternates2 SET rank = 1 where alternate = 'Thing-E-MABOB'
-- UPDATE partalternates2 SET rank = 2 where alternate = 'ThingamabobX0001'
-- UPDATE partalternates2 SET rank = 3 where alternate = 'BOBAMATHING'
-- UPDATE partalternates2 SET rank = 4 where alternate = 'doohicky'
-- UPDATE partalternates2 SET rank = 5 where alternate = 'E-doohicky'

Not to exciting cause of the conditional stuff, dull and prone to error when I write it.

"it's definitely useless and maybe harmful".

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-25 : 10:44:46

Wish I could be of some help, but I found your post to be truly fascinating.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-11-25 : 11:30:18

Do you have enough code there to get it work. So you can see what it does nice and clearly.

This line

SELECT * FROM #RemainingRanks

is useful in showing the relative rank of the parts that aren't getting a specific NEW rank but just need snuggle up against both sides of partno 'ThingamabobX0001'

maybe this is more common than appears. Say you had a contest of mutliplayer teams of some kind, where total counts per player were being updated. A calculation of team rank might want to be persisted after each team member makes an update?

"it's definitely useless and maybe harmful".

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-11-25 : 11:48:25

A lot of my motivation for this current project came from researching the
http://www.commonparts.org/ which relates directly back to military ship building.
And a profound statement I read somewhere that kind of went like this
..."divorce the balloon number from the drawing"...
Wish I didn't have to go at this alone, still not sure what the end deliverable of this application is.
It's just a gut feeling right now.

"it's definitely useless and maybe harmful".

cfederl
Starting Member

26 Posts

Posted - 2005-11-25 : 16:48:01

Rather than include the re-ranking logic everywhere, an instead of update trigger works well.
Usually, this type of problem occurs with contracts where, for a type of clause, there a different terms over a span of time and then an ammendment occurs which may affect the existing start and end dates.

Try this:
set nocount on
go
create trigger partalternates2_ui_ReRank
on partalternates2 instead of update
as
set nocount on
set xact_abort on
if 0 = (select count(*) from inserted) return
IF EXISTS
(SELECT count(*)
from inserted
group by partno
having count(*) < 1
)
BEGIN
RAISERROR ('Only alternative may be re-ranked at a time',16,1)
ROLLBACK
RETURN
end

UPDATE partalternates2
SET rank
= CASE WHEN inserted.Rank < deleted.Rank THEN
CASE WHEN partalternates2.rank = deleted.Rank then inserted.Rank
WHEN partalternates2.rank >= inserted.Rank
AND partalternates2.rank <= deleted.Rank then partalternates2.rank + 1
ELSE NULL
end
WHEN inserted.Rank > deleted.Rank THEN
CASE WHEN partalternates2.rank = deleted.Rank then inserted.Rank
WHEN partalternates2.rank >= deleted.Rank
and partalternates2.rank <= inserted.Rank then partalternates2.rank - 1
ELSE NULL
end
ELSE partalternates2.RANK
END
from inserted
join deleted
on deleted.partno = inserted.partno
and deleted.alternate = inserted.alternate
where partalternates2.partno = inserted.partno
AND NOT -- exclude where rank would not change
( ( inserted.Rank < deleted.Rank
AND ( partalternates2.rank < inserted.Rank
OR partalternates2.rank > deleted.Rank
)
)
OR ( inserted.Rank > deleted.Rank
and ( partalternates2.rank < deleted.Rank
or partalternates2.rank > inserted.Rank
)
)
)
--DO NOT INCLUDE Partalternates2.alternate = inserted.alternate
go
select 'start'
select rank, alternate from partalternates2 where partno = 'Thingamabob' order by rank
go
select 'Move from last to first'
begin tran
UPDATE dbo.partalternates2
SET Rank = 1
where partno = 'Thingamabob'
and alternate = 'doohicky'
select rank, alternate from partalternates2 where partno = 'Thingamabob' order by rank
rollback
go
select 'Move from first to last'
begin tran
UPDATE dbo.partalternates2
SET Rank = 4
where partno = 'Thingamabob'
and alternate = 'ThingamabobX0001'
select rank, alternate from partalternates2 where partno = 'Thingamabob' order by rank
rollback
go
select 'move from second to third'
begin tran
UPDATE dbo.partalternates2
SET Rank = 3
where partno = 'Thingamabob'
and alternate = 'Thing-E-MABOB'
select rank, alternate from partalternates2 where partno = 'Thingamabob' order by rank
rollback
go

select 'move from third to second'
begin tran
UPDATE dbo.partalternates2
SET Rank = 2
where partno = 'Thingamabob'
and alternate = 'BOBAMATHING'
select rank, alternate from partalternates2 where partno = 'Thingamabob' order by rank
rollback
go

select 'move from second to last'
begin tran
UPDATE dbo.partalternates2
SET Rank = 4
where partno = 'Thingamabob'
and alternate = 'Thing-E-MABOB'
select rank, alternate from partalternates2 where partno = 'Thingamabob' order by rank
rollback
go

Carl Federl

cfederl
Starting Member

26 Posts

Posted - 2005-11-25 : 18:55:31

Sorry, there is a reversal of < and > signs when checking for more one alternative within a product being re-ranked.

IF EXISTS
(SELECT count(*)
from inserted
group by partno
having count(*) < 1
)

Should be:
having count(*) > 1

Carl Federl

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-11-28 : 08:52:49

Nice cfederl, thank you very much, I find your trigger definition logic much easier to follow(* see below) than what I wrote mainly because it's difficult to see all the situations of my Case statements fall through. (see various http://blogs.msdn.com/abhinaba/archive/2005/11/15/492866.aspx concepts)

I wish I understood the business case better, instinct says it would be common but I haven't used it before. If someone was to write write an article about this solution what would it be called? "Maintaining tight sequences in rank column over subsets."???

* not for me personally cause I can't write a triggers worth anything but I think in terms of universality the trigger is more obvious.

"it's definitely useless and maybe harmful".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources