Please tell me how can I write one query to resolve this problem.

Question

Please tell me how can I write one query to resolve this problem.

liam4white

Newbie

Points: 4
More actions
April 9, 2013 at 11:39 pm

#273311

Let say I've two tables:
Plates table with fields:
-Start plate ( unsigned long type)
-Stop plate ( unsigned long type)
Plates row1: 1 12
Plates row2: 15 23
Broken plates table with fields:
-Start plate ( unsigned long type)
-Stop plate ( unsigned long type)
Broken plates row1: 3 7
Broken plates row2: 15 18
Broken plates row3: 21 22
Please tell me how can I make the remaining plates query?
Remaining plates query row1: 1 2
Remaining plates query row2: 8 12
Remaining plates query row3: 19 20
Remaining plates query row4: 23 23
Best regards.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Liam, welcome aboard. Before you start, have a read of this article[/url]. It describes how to post a question and what to include with it, to increase your chances of a rapid reply and an accurate solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

This uses a tally table, probably not very efficient though.

DECLARE @Plates TABLE(StartPlate INT, StopPlate INT);

INSERT INTO @Plates(StartPlate, StopPlate)

VALUES(1,12),(15,23);

DECLARE @BrokenPlates TABLE(StartPlate INT, StopPlate INT);

INSERT INTO @BrokenPlates(StartPlate, StopPlate)

VALUES(3,7),(15,18),(21,22);

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4),

grps(N,rn) AS (

SELECT t.N,

ROW_NUMBER() OVER(ORDER BY t.N)

FROM cteTally t

WHERE EXISTS(SELECT * FROM @Plates p WHERE t.N BETWEEN p.StartPlate AND p.StopPlate)

AND NOT EXISTS(SELECT * FROM @BrokenPlates b WHERE t.N BETWEEN b.StartPlate AND b.StopPlate))

SELECT MIN(N) AS RemainingStartPlate,

MAX(N) AS RemainingStopPlate

FROM grps

GROUP BY N-rn

ORDER BY MIN(N);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

liam4white Newbie Points: 4 More actions · Answer 3

Thank you for your answer. Your script help me.

Best regards.