Flagging Records within a table of sequenced numbers
-
I have a problem I'm hoping someone can help me with. I have a table of sequenced numbers. In this table I need to look at two columns; am_sw and cc_sw. If the am_sw is flagged, I need to check the next 7 sequential records for that number for a cc_sw that is flagged. If i don't find a cc_sw flagged within the next 7 records, I wan't to pull this number out for later.
Below is some sample code to give an idea of what I am working with. In the code below I would want to pull number 201200001 as it has the pattern of 1 am_sw followed by at least 7 records without a cc_sw. The second number would not be flagged as it does not meet this pattern.
Any ideas? I'm banging my head on my desk right now and it doesn't seem to be helping. :crazy:
DECLARE @numbers TABLE (number INT, am_sw INT, cc_sw INT, sequence_number INT)INSERT @numbers(number, am_sw, cc_sw,sequence_number)
SELECT 201200001,1,0,1 UNION ALL
SELECT 201200001,0,1,2 UNION ALL
SELECT 201200001,0,0,3 UNION ALL
SELECT 201200001,1,0,4 UNION ALL
SELECT 201200001,0,0,5 UNION ALL
SELECT 201200001,0,0,6 UNION ALL
SELECT 201200001,0,0,7 UNION ALL
SELECT 201200001,0,0,8 UNION ALL
SELECT 201200001,0,0,9 UNION ALL
SELECT 201200001,0,0,10 UNION ALL
SELECT 201200001,0,0,11 UNION ALL
SELECT 201200001,0,1,12 UNION ALL
SELECT 201200002,1,0,1 UNION ALL
SELECT 201200002,1,0,2 UNION ALL
SELECT 201200002,0,0,3 UNION ALL
SELECT 201200002,0,0,4 UNION ALL
SELECT 201200002,0,0,5 UNION ALL
SELECT 201200002,0,1,6 UNION ALL
SELECT 201200002,0,0,7
select *
FROM @numbers
-
wow great job providing the setup;
my attempt is adding a rownumber ot partiton by two columns, and joining the results;
does this give you what you were after?
DECLARE @numbers TABLE (number INT, am_sw INT, cc_sw INT, sequence_number INT)
INSERT @numbers(number, am_sw, cc_sw,sequence_number)
SELECT 201200001,1,0,1 UNION ALL
SELECT 201200001,0,1,2 UNION ALL
SELECT 201200001,0,0,3 UNION ALL
SELECT 201200001,1,0,4 UNION ALL
SELECT 201200001,0,0,5 UNION ALL
SELECT 201200001,0,0,6 UNION ALL
SELECT 201200001,0,0,7 UNION ALL
SELECT 201200001,0,0,8 UNION ALL
SELECT 201200001,0,0,9 UNION ALL
SELECT 201200001,0,0,10 UNION ALL
SELECT 201200001,0,0,11 UNION ALL
SELECT 201200001,0,1,12 UNION ALL
SELECT 201200002,1,0,1 UNION ALL
SELECT 201200002,1,0,2 UNION ALL
SELECT 201200002,0,0,3 UNION ALL
SELECT 201200002,0,0,46 UNION ALL
SELECT 201200002,0,0,7 UNION ALL
SELECT 201200002,0,1,8 UNION ALL
SELECT 201200002,0,0,9
SELECT *
from @numbers myAlias
LEFT OUTER JOIN (SELECT * FROM (select ROW_NUMBER() over(partition by number,am_sw order by number,sequence_number) As RW,
*
FROM @numbers) innertbl WHERE RW > 7) x
ON myAlias.number = x.number and myAlias.am_sw = x.am_sw
WHERE x.number is null
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here's another possible solution.
SELECT number
FROM @numbers n1
WHERE am_sw = 1
AND 7 = ( SELECT COUNT(*)
FROM @numbers n2
WHERE n2.number = n1.number
AND n2.cc_sw = 0
AND n2.sequence_number > n1.sequence_number
AND n2.sequence_number <= n1.sequence_number + 7)
-
And another possible method:-)
select i.numberfrom @numbers i
where am_sw = 1
and not exists (
SELECT 1 cc_sw_sum
FROM @numbers ca
WHERE i.number = ca.number
and ca.sequence_number - i.sequence_number between 0 and 7
and cc_sw = 1
)
-
You guys are awesome! Just please tell me you've had to deal with this before and that is why you came up with a solution so quickly :-P.
Thanks for the quick responses. I'm going to try both and see what I come up with.
-
Here's a SQL Server 2012 version
WITH CTE AS (SELECT number,
am_sw,
MAX(cc_sw) OVER (PARTITION BY number
ORDER BY sequence_number ASC
ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING
) AS max_cc_sw
FROM @numbers)
SELECT number
FROM CTE
WHERE am_sw=1
AND max_cc_sw = 0;
____________________________________________________
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
-
rossnruthie (5/30/2013)
You guys are awesome! Just please tell me you've had to deal with this before and that is why you came up with a solution so quickly :-P.Thanks for the quick responses. I'm going to try both and see what I come up with.
You're welcome.
I had a bit more of a play with this to see to what happens when you ramp up the number of rows. I used the following to generate a randomly populated table with a 20% chance of generating a flag for am_sw and 10% for cc_sw
SET NOCOUNT ONCREATE TABLE #numbers (number INT, am_sw INT, cc_sw INT, sequence_number INT)
DECLARE @i int = 201200000
WHILE @i <= 201400000
BEGIN
INSERT #numbers(number, am_sw, cc_sw,sequence_number)
SELECT TOP (5 + CAST((RAND(CAST(NEWID() AS varbinary)) * 15) AS INT)) @i, am_sw, cc_sw, n
FROM Tally n
CROSS APPLY (
SELECT CASE WHEN RAND(CAST(NEWID() AS varbinary)) > 0.8 THEN 1 ELSE 0 END am_sw
,CASE WHEN RAND(CAST(NEWID() AS varbinary)) > 0.9 THEN 1 ELSE 0 END cc_sw) a
SET @i = @i + 1
END
CREATE INDEX IDX1 ON #numbers(am_sw) include (number, sequence_number)
CREATE INDEX IDX2 ON #numbers(cc_sw, number, sequence_number)
This generated about 2.4 million rows for me. The indexes seem to be the best I could come up with, but should probably be investigated a bit more.
Some of the sequences generated are not likely to fit the behavior of your data, but have raised a couple of questions for me.
Are you likely to have rows that have both am_sw and cc_sw set to 1? Does this cause an exclusion? My query will exclude it.
Should the following sequence be excluded or included? Sequence 9 causes my query to include the number
number am_sw cc_sw sequence_number
----------- ----------- ----------- ---------------
201200000 1 0 1
201200000 0 0 2
201200000 1 0 3
201200000 0 0 4
201200000 0 1 5
201200000 0 0 6
201200000 0 0 7
201200000 0 0 8
201200000 1 0 9
201200000 0 0 10
Do you want a distinct list of numbers. My query will report the following three times
number am_sw cc_sw sequence_number----------- ----------- ----------- ---------------
201200001 1 0 1
201200001 0 0 2
201200001 1 0 3
201200001 0 0 4
201200001 0 0 5
201200001 1 0 6
201200001 0 0 7
201200001 0 0 8
201200001 0 0 9
-
Thanks for the reply Micky. I actually tested the methods given and found the following.
Method 1 (Luis) seems to work the best in my situation. However, it would return a false positive where the am_sw was flagged as well as the cc_sw in the same record that then had 7 consecutive non cc's. This is something method 2 (Micky) excludes by default and it is the behavior I want. This was more my fault for not specifying this logic piece and was corrected by adding AND cc_sw = 0 in the SELECT
Method 2(Micky) works well for the most part but will return a false positive where it finds a record with the am_sw flagged and less the 7 records following it.
Method 3 (Mark) works just as Method 2 in terms of results.
Duplicates appeared in all methods but is not a problem.
DECLARE @numbers TABLE (number INT, am_sw INT, cc_sw INT, sequence_number INT)INSERT @numbers(number, am_sw, cc_sw,sequence_number)
SELECT 201200001,1,0,1 UNION ALL
SELECT 201200001,0,1,2 UNION ALL
SELECT 201200001,0,0,3 UNION ALL
SELECT 201200001,1,0,4 UNION ALL
SELECT 201200001,0,0,5 UNION ALL
SELECT 201200001,0,0,6 UNION ALL
SELECT 201200001,0,0,7 UNION ALL
SELECT 201200001,0,0,8 UNION ALL
SELECT 201200001,0,0,9 UNION ALL
SELECT 201200001,0,0,10 UNION ALL
SELECT 201200001,0,0,11 UNION ALL
SELECT 201200001,0,1,12 UNION ALL
SELECT 201200002,1,0,1 UNION ALL------|Only 6 Numbers (Should Not Be Returned)
SELECT 201200002,1,0,2 UNION ALL------|Returned in Method 2,3
SELECT 201200002,0,0,3 UNION ALL------|
SELECT 201200002,0,0,4 UNION ALL------|
SELECT 201200002,0,0,5 UNION ALL------|
SELECT 201200002,0,0,6 UNION ALL------|
SELECT 201200003,1,0,1 UNION ALL
SELECT 201200003,0,0,2 UNION ALL
SELECT 201200003,0,0,3 UNION ALL
SELECT 201200003,0,0,4 UNION ALL
SELECT 201200003,0,0,5 UNION ALL
SELECT 201200003,0,0,6 UNION ALL
SELECT 201200003,0,0,7 UNION ALL
SELECT 201200003,0,1,8 UNION ALL
SELECT 201200003,0,0,9
--//METHOD 1
SELECT number
FROM @numbers n1
WHERE am_sw = 1
AND cc_sw = 0
AND7 = ( SELECT COUNT(*)
FROM @numbers n2
WHERE n2.number = n1.number
AND n2.cc_sw = 0
AND n2.sequence_number > n1.sequence_number
AND n2.sequence_number <= n1.sequence_number + 7)
--//METHOD 2
select i.number
from @numbers i
where am_sw = 1
and not exists (
SELECT 1 cc_sw_sum
FROM @numbers ca
WHERE i.number = ca.number
and ca.sequence_number - i.sequence_number between 0 and 7
and cc_sw = 1
) ;
--//METHOD 3
WITH CTE AS (
SELECT number,
am_sw,
MAX(cc_sw) OVER (PARTITION BY number
ORDER BY sequence_number ASC
ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING
) AS max_cc_sw
FROM @numbers)
SELECT number
FROM CTE
WHERE am_sw=1
AND max_cc_sw = 0;
-
rossnruthie (5/30/2013)
...Method 2(Micky) works well for the most part but will return a false positive where it finds a record with the am_sw flagged and less the 7 records following it.
Method 3 (Mark) works just as Method 2 in terms of results.
Duplicates appeared in all methods but is not a problem.
...
You can alter my method to the following. I think it could possibly be done better, but I'm running a bit short on time now.
select i.numberfrom #numbers i
where am_sw = 1
and not exists (
SELECT 1
FROM #numbers ca
WHERE i.number = ca.number
and ca.sequence_number - i.sequence_number between 0 and 7
and cc_sw = 1
)
and exists (
SELECT 1
FROM #numbers cn
WHERE i.number = cn.number
and cn.sequence_number - 7 = i.sequence_number)
As well as the indexes mentioned before, you will need an index like the following
CREATE INDEX IDX3 ON #numbers(number, sequence_number ) -
I'm glad it worked almost fine and even more glad that you tested the code before using it. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply