Urgent request please
-
if i have records as follows:
ID Status
1 0
2 1
3 0
4 1
5 1
6 1
7 1
8 1
then I need to find from which ID to which ID had consecutive status of 1
for example:
4-8 had 5 (also need to have a count 5 or whatever it is)
Thanks.
-
there's a few assumptions here: that the column ID is always an integer, with no gaps.
the trick is to simply join the table against itself.
With MyCTE (ID,Status)
AS
(
SELECT '1','0' UNION ALL
SELECT '2','1' UNION ALL
SELECT '3','0' UNION ALL
SELECT '4','1' UNION ALL
SELECT '5','1' UNION ALL
SELECT '6','1' UNION ALL
SELECT '7','1' UNION ALL
SELECT '8','1'
)
select * FROM MYCTE T1
INNER JOIN MYCTE T2
ON T1.ID + 1 = T2.ID
WHERE T1.Status = 1
AND T2.STATUS = 1
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! -
Hi Lovel,
I need the format "4-8 had 5".
Thanks.
-
this gets you closer,and I left enough in there for you to do so you can understand the code.
you need to concatenat teh values, as well as accomodate teh counting by one more.
/*
--results
----------- ----------- --------------------
4 8 4
*/
With MyCTE (ID,Status)
AS
(
SELECT 1,'0' UNION ALL
SELECT 2,'1' UNION ALL
SELECT 3,'0' UNION ALL
SELECT 4,'1' UNION ALL
SELECT 5,'1' UNION ALL
SELECT 6,'1' UNION ALL
SELECT 7,'1' UNION ALL
SELECT 8,'1'
)
SELECT MIN(T1ID),MAX(T2ID),MAX(rw) FROM
(
select ROW_NUMBER() over (PARTITION BY T1.STATUS ORDER BY T1.ID) As RW,
T1.ID AS T1ID,
T1.Status As T1STATUS,
T2.ID AS T2ID,
T2.Status As T2STATUS
FROM MYCTE T1
INNER JOIN MYCTE T2
ON T1.ID + 1 = T2.ID
WHERE T1.Status = 1
AND T2.STATUS = 1
)X
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! -
Assumes ID is contiguous
DECLARE @t TABLE(ID INT,Status INT)INSERT INTO @t(ID,Status)
SELECT 1,0 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,0 UNION ALL
SELECT 4,1 UNION ALL
SELECT 5,1 UNION ALL
SELECT 6,1 UNION ALL
SELECT 7,1 UNION ALL
SELECT 8,1;
WITH CTE AS (
SELECT ID,Status,
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID) AS rn
FROM @t)
SELECT CAST(MIN(ID) AS VARCHAR(10)) + '-' + CAST(MAX(ID) AS VARCHAR(10)) + ' had ' + CAST(COUNT(*) AS VARCHAR(10))
FROM CTE
WHERE Status=1
GROUP BY rn-ID;
____________________________________________________
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
-
Also, google Gaps and Islands. Here is a link to a sample chapter in an amazing book that illustrates the problem and solutions. I suggest that if this sample chapter helps you, that you buy the book. It is full of great information from experts. The proceeds also go to a great cause.
Jared
CE - Microsoft -
Others beat me, but I didn't give it just as the OP asked.
create table #testtab1(ID int, Status int);
insert into #testtab1 (ID, Status)
values (1,0),(2,1),(3,0),(4,1),(5,1),(6,1),(7,1),(8,1),(9,0),(10,1),(11,1),(12,1);
go
With BaseData as (
select
ID,
[Status],
rn2 = row_number() over (partition by [status] order by ID),
rn1 = row_number() over (order by ID)
FROM
#testtab1 T1
)
select
min(ID) as MinID,
max(ID) as MaxID,
count([Status]) Status1Cnt
from
BaseData
where
[Status] = 1
group by
rn1 - rn2
having
count([Status]) > 1
order by
rn1 - rn2;
go
drop table #testtab1;
go
-
Thanks to all
-
You are welcome. Now, try and tell us how we accomplished the task. If you can't explain it, you shouldn't use it in production as you are the one who needs to support it.
-
I worked exactly the way I wanted.
Thanks for your time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply