March 24, 2011 at 6:20 pm
Hi,
I am after the best records from a dataset that will contain overlaps and changes. I guess there will be two parts - identifiying those records with overlaps, then working out which has the minimum value of PR. eg:
HOLEIDFROMTOPRVAL
A202520GA
A212610GA
A253020GB
A263110GB
B51020GA
B101520GB
B51510G
The records that I am looking to return would be:
HOLEIDFROMTOPRVAL
A212610GA
A263110GB
B51510G
In other words if there is an overlap, then use the minimum value of PR to determine which is the best record to return. In this simplified example the PR is known, in the data, this could be any integer. There won't always be an overlap, in which case need to return those records as well.
Appreciate your help,
JK
March 25, 2011 at 5:17 am
I hope this is not overcomplicating your problem:
DECLARE @test TABLE (
[HOLEID] char(1),
[FROM] int,
[TO] int,
[PR] int,
[VAL] varchar(2)
)
INSERT INTO @test
SELECT 'A', 20, 25, 20, 'GA'
UNION ALL SELECT 'A', 21, 26, 10, 'GA'
UNION ALL SELECT 'A', 25, 30, 20, 'GB'
UNION ALL SELECT 'A', 26, 31, 10, 'GB'
UNION ALL SELECT 'B', 5, 10, 20, 'GA'
UNION ALL SELECT 'B', 10, 15, 20, 'GB'
UNION ALL SELECT 'B', 5, 15, 10, 'G'
;WITH
-- Add a unique id to identify rows
baseData AS (
SELECT ID = ROW_NUMBER() OVER (ORDER BY [HOLEID], [FROM], [TO]), *
FROM @test AS A
),
-- You can use this one if you don't have a tally table
Tally AS (
SELECT Number
FROM master.dbo.spt_values
WHERE type = 'P'
),
-- Expand rows on the tally table
numberedData AS (
SELECT *
FROM baseData AS D
INNER JOIN Tally AS T
ON T.Number >= D.[FROM]
AND T.Number < D.[TO]
),
-- Find overlapping records and minimum "PR"
overlappingGroups AS (
SELECT A.ID AS A_ID, B.ID AS B_ID, MIN(CASE WHEN A.PR < B.PR THEN A.PR ELSE B.PR END) AS PR
FROM numberedData AS A
INNER JOIN numberedData AS B
ON A.ID <> B.ID
AND A.Number = B.Number
GROUP BY A.ID, B.ID
)
-- Select out rows that match the criteria for overlapping records
SELECT *
FROM baseData AS D
WHERE EXISTS (
SELECT 1
FROM overlappingGroups G
WHERE D.ID IN (G.A_ID, G.B_ID)
AND D.PR = G.PR
)
If you don't have a Tally table, go and create one. It's priceless.
Here's a great article on Tally tables by Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/62867/
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply