Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic ranking


Dynamic ranking

Author
Message
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4575
Thanks in advance if you can offer any assistance.

I have a table of ranked cities-#Cities

I have a table called #CityGroups with a RankStart and RankEnd field. Example, GroupA should contain the top 3 ranked cities.

I have a table #CityGroupmembers which I periodically need to refresh as the data in table #Cities is updated.

This would be the classic MERGE scenario but table #CityGroupmembers is accessed through a linked server, so I need to generate an insert and a delete statement based on existence checks, that is I can not delete all members of a group and then repopulate in its entirety.

I would like to include ties if there are ties in table #Cities, not shown in my sample data.

I guess the rub (at least for me) is how do I extract the rankings from table #CityGroups and use them in my insert and delete statements.

I would strongly prefer to not use dynamic SQL.

CREATE TABLE #Cities
(
City varchar (20),
CityRank int
)

INSERT INTO #Cities
SELECT 'Paris',1 UNION
SELECT 'Chicago',2 UNION
SELECT 'Seattle',3 UNION
SELECT 'Hong Kong',4 UNION
SELECT 'New York',5 UNION
SELECT 'Brasilia',6 UNION
SELECT 'Tangier',7 UNION
SELECT 'Berlin',8 UNION
SELECT 'Tokyo',9 UNION
SELECT 'Mexico City',10


CREATE TABLE #CityGroups
(
CityGroup varchar(10),
CityDescription varchar(15),
RankStart int,
RankEnd int
)

INSERT INTO #CityGroups
SELECT 'GroupA','Top 3 Cities',1,3 UNION
SELECT 'GroupB','Top 10 Cities',1,10 UNION
SELECT 'GroupC','Middle Eight',2,9

CREATE TABLE #CityGroupmembers
(
CityGroup varchar(10),
City varchar (20)
)

--The inserts I would be trying to generate
--These are obviously not getting the rankings from the #CityGroups table in any dynamic fashion

INSERT INTO #CityGroupmembers
SELECT TOP 3
'GroupA',
City
FROM #Cities
ORDER BY CityRank

INSERT INTO #CityGroupmembers
SELECT TOP 10
'GroupB',
City
FROM #Cities
ORDER BY CityRank

INSERT INTO #CityGroupmembers
SELECT
'GroupC',
City
FROM #Cities
WHERE CityRank BETWEEN 2 AND 9


SELECT * FROM #Cities ORDER BY CityRank
SELECT * FROM #CityGroups
SELECT * FROM #CityGroupmembers


DROP TABLE #Cities
DROP TABLE #CityGroups
DROP TABLE #CityGroupmembers

--Updated list for testing
/*
INSERT INTO #Cities
SELECT 'Montreal',1 UNION
SELECT 'Chicago',2 UNION
SELECT 'Mexico City',3 UNION
SELECT 'San Diego',4 UNION
SELECT 'New York',5 UNION
SELECT 'Brasilia',6 UNION
SELECT 'Paris',7 UNION
SELECT 'Berlin',8 UNION
SELECT 'Tokyo',9 UNION
SELECT 'Hong Kong',10
*/


geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
First, let me say that the idea of a "dynamic ranking" table that defines multiple ranking sets is an intriguing idea, and I may use it in other applications of my own.

My solution below translates your temp tables into CTEs, which is my preference for these testing situations. I will line out how each CTE relates to the tables of your original post:

* CityGroups represents the table with the same name, of ranking group definitions
* Cities represents the original set of cities
* CityGroupMembers_Base represents what the initial CityGroupMembers table would look like after the inserts. Note that it uses a simple join to accomplish the equivalent of all three inserts, and the join would work just as well with any number of grouping definitions in the CityGroups table. In fact it is the simplicity of this join that makes the use of a "dynamic ranking" table appealing to me.
* Cities_Update represents the changed data to be incorporated into the CityGroupMembers table.
* CityGroupMembers_Update represents a view you might use to compare against the existing CityGroupMembers table to determine the rows for deleting and inserting.
* The main query at the end of all the CTEs is a UNION of two queries, one that identifies the rows to be deleted from the existing CityGroupMembers table and one that identifies the rows to be inserted into it.

And here is my solution:

with
CityGroups as
(SELECT CityGroup = 'GroupA', CityDescription = 'Top 3 Cities',RankStart = 1,RankEnd = 3 UNION
SELECT 'GroupB','Top 10 Cities',1,10 UNION
SELECT 'GroupC','Middle Eight',2,9),

Cities as
(SELECT City = 'Paris', CityRank = 1 UNION
SELECT 'Chicago',2 UNION
SELECT 'Seattle',3 UNION
SELECT 'Hong Kong',4 UNION
SELECT 'New York',5 UNION
SELECT 'Brasilia',6 UNION
SELECT 'Tangier',7 UNION
SELECT 'Berlin',8 UNION
SELECT 'Tokyo',9 UNION
SELECT 'Mexico City',10),

CityGroupMembers_Base as
(select
CityGroups.CityGroup,
Cities.City
from
Cities inner join
CityGroups on Cities.CityRank between RankStart and RankEnd),

Cities_Update as
(SELECT City = 'Montreal', CityRank = 1 UNION
SELECT 'Chicago',2 UNION
SELECT 'Mexico City',3 UNION
SELECT 'San Diego',4 UNION
SELECT 'New York',5 UNION
SELECT 'Brasilia',6 UNION
SELECT 'Paris',7 UNION
SELECT 'Berlin',8 UNION
SELECT 'Tokyo',9 UNION
SELECT 'Hong Kong',10),

CityGroupMembers_Update as
(select
CityGroups.CityGroup,
Cities_Update.City
from
Cities_Update inner join
CityGroups on Cities_Update.CityRank between RankStart and RankEnd)

select
ChangeType = 'Delete',
CityGroupMembers_Base.City,
CityGroupMembers_Base.CityGroup
from
CityGroupMembers_Base
where
not exists (select City from CityGroupMembers_Update where CityGroupMembers_Base.City = CityGroupMembers_Update.City and CityGroupMembers_Base.CityGroup = CityGroupMembers_Update.CityGroup)
union all
select
ChangeType = 'Insert',
CityGroupMembers_Update.City,
CityGroupMembers_Update.CityGroup
from
CityGroupMembers_Update
where not exists
(select City from CityGroupMembers_Base where CityGroupMembers_Base.City = CityGroupMembers_Update.City and CityGroupMembers_Base.CityGroup = CityGroupMembers_Update.CityGroup)



I'm not sure if this addresses all your issues with having to pull data from a linked server, but hopefully can frame at least part of your solution.
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4575
Thanks, this definitely set me in the correct direction.

The JOIN using BETWEEN is what I was missing. I started thinking about using a tally table but that was over complicating matters.

My completed code is below using permanent tables.

--Create the tables
IF OBJECT_ID('dbo.Cities', 'U') IS NOT NULL DROP TABLE dbo.Cities
CREATE TABLE Cities
(
City varchar (20),
CityRank int
)

IF OBJECT_ID('dbo.CityGroups', 'U') IS NOT NULL DROP TABLE dbo.CityGroups
CREATE TABLE CityGroups
(
CityGroup varchar(10),
CityDescription varchar(15),
RankStart int,
RankEnd int
)

IF OBJECT_ID('dbo.CityGroupmembers', 'U') IS NOT NULL DROP TABLE dbo.CityGroupmembers
CREATE TABLE CityGroupmembers
(
CityGroup varchar(10),
City varchar (20)
)

--Populate the tables
INSERT INTO Cities
SELECT 'Paris',1 UNION
SELECT 'Chicago',2 UNION
SELECT 'Seattle',3 UNION
SELECT 'Hong Kong',4 UNION
SELECT 'New York',5 UNION
SELECT 'Brasilia',6 UNION
SELECT 'Tangier',7 UNION
SELECT 'Berlin',8 UNION
SELECT 'Tokyo',9 UNION
SELECT 'Mexico City',10

INSERT INTO CityGroups
SELECT 'GroupA','Top 3 Cities',1,5 UNION
SELECT 'GroupB','Top 10 Cities',1,10 UNION
SELECT 'GroupC','Middle Eight',2,9

--Populate CityGroupmembers based on the current data in table Cities
--Insert data
INSERT INTO CityGroupmembers
SELECT --'Insert' AS DMLAction,
CG.CityGroup,
C.City
FROM Cities C
INNER JOIN CityGroups CG
ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd
WHERE
NOT EXISTS
(
SELECT
CGM.City
FROM CityGroupMembers CGM
WHERE
CGM.City = C.City AND
CG.CityGroup = CGM.CityGroup
)

--Delete data-Not really needed when the table is populated for the first time
DELETE CGM
--SELECT 'Delete' AS DMLAction, CityGroup,City
FROM dbo.CityGroupmembers CGM
WHERE
NOT EXISTS
(
SELECT
CG.CityGroup,
C.City
FROM Cities C
INNER JOIN CityGroups CG
ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd
WHERE
CGM.City = C.City AND
CG.CityGroup = CGM.CityGroup
)

--Confirm the population of CityGroupmembers is correct
SELECT
*
FROM CityGroupmembers
ORDER BY
CityGroup

--Truncate and repopulate the Cities table simulating a data refresh
--View the cities before the refresh if you like
--SELECT * FROM Cities ORDER BY CityRank

TRUNCATE TABLE Cities
INSERT INTO Cities
SELECT 'Montreal',1 UNION
SELECT 'Chicago',2 UNION
SELECT 'Mexico City',3 UNION
SELECT 'San Diego',4 UNION
SELECT 'New York',5 UNION
SELECT 'Brasilia',6 UNION
SELECT 'Paris',7 UNION
SELECT 'Berlin',8 UNION
SELECT 'Tokyo',9 UNION
SELECT 'Hong Kong',10

--View the cities after the refresh if you like
--SELECT * FROM Cities ORDER BY CityRank

--Re-populate CityGroupmembers based on the current data in table Cities
--Insert data
INSERT INTO CityGroupmembers
SELECT --'Insert' AS DMLAction,
CG.CityGroup,
C.City
FROM Cities C
INNER JOIN CityGroups CG
ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd
WHERE
NOT EXISTS
(
SELECT
CGM.City
FROM CityGroupMembers CGM
WHERE
CGM.City = C.City AND
CG.CityGroup = CGM.CityGroup
)

--Delete data
DELETE CGM
--SELECT 'Delete' AS DMLAction, CityGroup,City
FROM dbo.CityGroupmembers CGM
WHERE
NOT EXISTS
(
SELECT
CG.CityGroup,
C.City
FROM Cities C
INNER JOIN CityGroups CG
ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd
WHERE
CGM.City = C.City AND
CG.CityGroup = CGM.CityGroup
)



--Confirm the population of CityGroupmembers is correct after the data is refreshed
SELECT
*
FROM CityGroupmembers
ORDER BY
CityGroup


geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
Glad I could help out. Although they are needed rarely, non-equi-joins (joins on an expression other than columnA = columnB) can be just the ticket for complex relational problems.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16616 Visits: 17024
Do be careful when doing "non-equi" joins.

http://www.sqlservercentral.com/articles/T-SQL/61539/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search