complicated query for me :)
-
hi,
i have table like this : ( if its needed its possible to add identity to this table)
(status 1 and 2 ara same )
time status
14:00 0
15:00 0
16:00 1
17:00 2
18:00 2
19:00 1
20:00 0
21:00 0
22:00 1
23:00 1
24:00 0
resault set should be :
time status
14:00 0
16:00 1
20:00 0
22:00 1
24:00 0
thanks alot
sharon
-
Not clear what your result is based on, some group by ? , plz explain.
-
What's the logic behind that? Why is 16:00 in the results but 18:00 isn't?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I think the OP needs to find the rows where the status changing form 0 to 1 and vice-versa. WE should consider Status = 2 as Status = 1.
-
hi ,
i soory if it wasnot not clear :
but this what i neeed :
find the rows where the status changing form 0 to 1 and vice-versa. WE should consider Status = 2 as Status = 1.
🙂
-
For future reference you'll often get quicker help if you provide the table definitions and sample data in a usable format, not just a description.
I'm guessing as to data types, so this could well be useless to you even though it does work
CREATE TABLE Times (SomeTime TIME,
SomeStatus INT
);
INSERT INTO Times (SomeTime, SomeStatus)
VALUES
('14:00', 0),
('15:00', 0),
('16:00', 1),
('17:00', 2),
('18:00', 2),
('19:00', 1),
('20:00', 0),
('21:00', 0),
('22:00', 1),
('23:00', 1),
('00:00', 0)
SELECT t.SomeTime ,
t.SomeStatus
FROM dbo.Times AS t
INNER JOIN dbo.Times AS t2 ON t.SomeTime = DATEADD(hh, 1, t2.SomeTime)
WHERE ( t.SomeStatus = 0
AND t2.SomeStatus != 0
)
OR
( t.SomeStatus != 0
AND t2.SomeStatus = 0
)
Why is 14:00 in your results? The status doesn't change before (there is no row before) or after.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
hi,
thanks for your ansewrs,
i need to catch the first status in the
first raw in table
sharon
-
CREATE TABLE Times (SomeTime TIME,
SomeStatus INT
);
INSERT INTO Times (SomeTime, SomeStatus)
VALUES
('14:00', 0),
('15:00', 0),
('16:00', 1),
('17:00', 2),
('18:00', 2),
('19:00', 1),
('20:00', 0),
('21:00', 0),
('22:00', 1),
('23:00', 1),
('00:00', 0)
SELECT t.SomeTime ,
t.SomeStatus
FROM dbo.Times AS t
LEFT OUTER JOIN dbo.Times AS t2 ON t.SomeTime = DATEADD(hh, 1, t2.SomeTime)
WHERE ( t.SomeStatus = 0
AND t2.SomeStatus != 0
)
OR
( t.SomeStatus != 0
AND t2.SomeStatus = 0
)
OR t2.SomeTime IS NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Here's another way
DECLARE @t TABLE(time CHAR(5), status INT)INSERT INTO @t(time,status)
VALUES('14:00',0),
('15:00', 0),
('16:00', 1),
('17:00', 2),
('18:00', 2),
('19:00', 1),
('20:00', 0),
('21:00', 0),
('22:00', 2),
('23:00', 1),
('24:00', 0);
WITH CTE1 AS (
SELECT time,status,
ROW_NUMBER() OVER(ORDER BY time) -
ROW_NUMBER() OVER(PARTITION BY CASE WHEN status=0 THEN 0 ELSE 1 END ORDER BY time) AS rnDiff
FROM @t),
CTE2 AS (
SELECT time,status,
ROW_NUMBER() OVER(PARTITION BY rnDiff,CASE WHEN status=0 THEN 0 ELSE 1 END ORDER BY time) AS rn
FROM CTE1)
SELECT time,status
FROM CTE2
WHERE rn=1;
____________________________________________________
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
-
THANK YOU
IT HELP ME ALOT
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply