August 21, 2012 at 9:38 pm
Hi !
i have a table "bridgeP"
with cols bridgePid int, fromlen decimal(10,2) , tolen decimal(10,2)
bridgePid is autogenerated, and fromlen and tolen has lenght of painting done of bridge.
ex if bridge is of 100 meter and painting of bridge is done from 10 m to 20 m , 40 m to 50 m etc.
so i would like to find the gaps for which painting is not done.
the problem is entry in table has overlap as shown below.
bridgepid , fromlen , tolen
1 , 10 ,20
2 ,18 ,21
3 ,11 ,20
4 ,40 ,50
5 ,19 ,35
so please find solution , with overlaps , i have found a solution by using loop.looping through the record of bridgeP
table one by one in asceding order of fromlen and finding out gap and inserting it into new table.
so please provide me solution with out loop.
yours sincerely
August 22, 2012 at 1:48 am
OK, let's setup some sample data first: -
SELECT bridgepid, fromlen, tolen
INTO #yourTable
FROM (VALUES(1,10,20),(2,18,21),(3,11,20),(4,40,50),(5,19,35)
)a(bridgepid, fromlen, tolen);
So, from that, we're expecting gaps of 1 -> 9 and 36 -> 39, agreed?
We'll work that out first.
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1),
CTE2(N) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(bridgepid,N) AS (SELECT bridgepid, N
FROM #yourTable a
CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5) b(N)
WHERE N >= fromlen AND N <= tolen)
SELECT bridgepid, gapStart, N - 1 AS gapEnd
FROM CTE6 a
CROSS APPLY (SELECT ISNULL(MAX(b.N),0)+1
FROM CTE6 b
WHERE b.N < a.N) b(gapStart)
WHERE a.N - 1 NOT IN (SELECT N FROM CTE6);
Results: -
bridgepid gapStart gapEnd
----------- -------------------- --------------------
1 1 9
4 36 39
I bet that's faster than your loop 😉
OK, now the overlapping results. We're expecting 11-21, is that correct?
I'm not sure how you want these results displayed, so you may want to have a play around with this. But here we go: -
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1),
CTE2(N) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(bridgepid,N) AS (SELECT bridgepid, N
FROM #yourTable a
CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5) b(N)
WHERE N >= fromlen AND N <= tolen)
SELECT bridgepid, gapStart, N - 1 AS gapEnd,
overlapStart, overlapEnd
FROM CTE6 a
CROSS APPLY (SELECT ISNULL(MAX(b.N),0)+1
FROM CTE6 b
WHERE b.N < a.N) b(gapStart)
CROSS APPLY (SELECT MIN(N), MAX(N)
FROM (SELECT N
FROM CTE6
GROUP BY N
HAVING COUNT(1) > 1
) a
) c(overlapStart,overlapEnd)
WHERE a.N - 1 NOT IN (SELECT N FROM CTE6);
Results: -
bridgepid gapStart gapEnd overlapStart overlapEnd
----------- -------------------- -------------------- -------------------- --------------------
1 1 9 11 21
4 36 39 11 21
Now that I think of it, the overlapping section that I've written will only work when the overlap has no gaps. I'll rework the code to fix, unless you think you can fix it yourself?
I may have over-complicated this part of the process. Hopefully, either you or someone else will be able to simplify things.
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1),
CTE2(N) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(bridgepid,N) AS (SELECT bridgepid, N
FROM #yourTable a
CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5) b(N)
WHERE N >= fromlen AND N <= tolen),
CTE7(N) AS (SELECT N FROM CTE6 GROUP BY N HAVING COUNT(1) > 1),
CTE8(N, X) AS (SELECT N, ROW_NUMBER() OVER (ORDER BY N) - N FROM CTE7),
CTE9(N, X, Y) AS (SELECT N, X, (ROW_NUMBER() OVER (PARTITION BY X ORDER BY N) - N)*-1
FROM CTE8),
CTE10(overlapStart, overlapEnd) AS (SELECT DISTINCT
MIN(N) OVER (PARTITION BY X, Y),
MAX(N) OVER (PARTITION BY X, Y)
FROM CTE9)
SELECT bridgepid, gapStart, N - 1 AS gapEnd,
overlapStart, overlapEnd
FROM CTE6 a
CROSS APPLY (SELECT ISNULL(MAX(b.N),0)+1
FROM CTE6 b
WHERE b.N < a.N) b(gapStart)
CROSS APPLY CTE10 c
WHERE a.N - 1 NOT IN (SELECT N FROM CTE6);
Results in: -
bridgepid gapStart gapEnd overlapStart overlapEnd
----------- -------------------- -------------------- -------------------- --------------------
1 1 9 11 21
4 36 39 11 21
If you change the sample data so that we have two overlapping periods: -
SELECT bridgepid, fromlen, tolen
INTO #yourTable
FROM (VALUES(1,10,20),(2,18,21),(3,11,20),(4,40,50),(5,19,35),(6,27,30)
)a(bridgepid, fromlen, tolen);
The first bit of code I produced resulted in this: -
bridgepid gapStart gapEnd overlapStart overlapEnd
----------- -------------------- -------------------- -------------------- --------------------
1 1 9 11 30
4 36 39 11 30
Which is incorrect because the overlapping area does not include 21 -> 26.
The new code produces this: -
bridgepid gapStart gapEnd overlapStart overlapEnd
----------- -------------------- -------------------- -------------------- --------------------
1 1 9 11 21
4 36 39 11 21
1 1 9 27 30
4 36 39 27 30
The results are correct, but because the gaps and overlaps are not really related we end up with this Cartesian looking result set. If you can show how you want the results displayed, I can change the code to do so - or you could have a play yourself.
Hell, we might get lucky and have more solutions suggested 😀
August 22, 2012 at 3:57 am
Thank u,
thing is i am keeping decimal (14,4) for from length and to length,
and it is not bridge it is road where paiting is going on.
will the query be able to accomadate this much of lenght.
and overlaps are there but we need to show only gaps in points.
yours sincerely
August 22, 2012 at 4:02 am
rajemessage (8/22/2012)
Thank u,thing is i am keeping decimal (14,4) for from length and to length,
and it is not bridge it is road where paiting is going on.
will the query be able to accomadate this much of lenght.
and overlaps are there but we need to show only gaps in points.
yours sincerely
Can you knock up some sample data that is similar to your actual data for me please? Then, based on your sample data, can you knock up what your expected result-set looks like? At the moment I feel like I'm stumbling in the dark - normally I wouldn't have answered a complex question without sample data and DDL but yours interested me.
There's some advise on how to post sample data and DDL here --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D. If you follow the same advise when setting up the expected result-set, that would be great.
August 22, 2012 at 5:17 am
please take on sample of data.
Entry can be done in from lenght and to lenght like following.
900000.0000 to 1000000.0001,1000000.0005 to 1000100.0000 so there is gap of .0004.
starting from 1000000.0001(start gap) to 1000000.0005 (end gap) which we want to show.
with this we want to show starting gap and last gap.
yours sincerely.
August 22, 2012 at 5:22 am
rajemessage (8/22/2012)
please take on sample of data.Entry can be done in from lenght and to lenght like following.
900000.0001,900000.0005 so there is gap of .0004.
starting from .0001 to .0005 which we want to show.
yours sincerely.
As I said, please read this article (http://www.sqlservercentral.com/articles/Best+Practices/61537/) about the best way to set-up sample data and DDL scripts[/url]. Everyone that answers questions on this site is an unpaid volunteer. By providing the sample data and DDL scripts, you remove any chance of misinterpretations. Also, if you use the same format to provide your expected result-set, it makes it even easier and much more likely that many people will offer solutions.
Thanks!
August 31, 2012 at 4:44 am
It's a real shame that you've decided not to post the requested DDL, sample data and expected results based on the sample data provided. I'd have really enjoyed showing you a fast and efficient way of producing your results.
Hope you managed to solve this yourself without resorting to a cursor, which will be immensely slower than the set-based solution I was aiming at.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply