How to find the nth record based on specific condition

Question

How to find the nth record based on specific condition

Prasanthi Reddy

SSC Veteran

Points: 213
More actions
October 2, 2011 at 2:02 am

#390523

Hi,
I need ur help..
I have a table which contains on and off status of a meter. Feederstatus = 0 means On and feederstatus=1 means Off.
Now i need to calculate on status of the meter. I.e, I need to select first on time where status is 0 and next immediate off time where status is 1.
i.e., if i have columns like below
feederstatus time
0 00:00:00
0 00:15:00
0 00:30:00
1 00:45:00
0 01:00:00
0 01:15:00
1 01:30:00
1 01:45:00
1 02:00:00
Now my result should be
ontime offtime
00:00:00 00:45:00
01:00:00 01:30:00
CREATE TABLE [dbo].[MeterInterruptions](
[LiveId] [bigint] primary key,
[MeterNumber] [varchar](15) NOT NULL,
[SlotTimeStamp] [datetime] NOT NULL,
[Slot] [int] NOT NULL,
[FeederStatus] [bit] NOT NULL,
[ModemDate] [varchar](10) NOT NULL,
[ModemTime] [varchar](10) NOT NULL)
values::
insert into meterinterruptions values (78690,,'APB02620','2011-08-19 00:00:00.000',0,0,'18082011', '000000');
insert into meterinterruptions values (78619,'APB02620','2011-08-18 00:15:00.000',1,1,'18082011','001500');
insert into meterinterruptions values (78620,'APB02620','2011-08-18 00:30:00.000',2,1,'18082011','003000',);
insert into meterinterruptions values (78621,'APB02620','2011-08-18 00:45:00.000',3,0,'18082011','004500');
insert into meterinterruptions values (78622,'APB02620','2011-08-18 01:15:00.000',5,0,'18082011','011500');
insert into meterinterruptions values (78623,'APB02620','2011-08-18 01:30:00.000',6,0,'18082011','013000',);
insert into meterinterruptions values (78624,'APB02620','2011-08-18 01:45:00.000',7,0,'18082011','014500');
insert into meterinterruptions values (78625,'APB02620','2011-08-18 02:00:00.000',8,0,'18082011','020000');
insert into meterinterruptions values (78626,'APB02620','2011-08-18 02:15:00.000',9,0,'18082011','021500');
insert into meterinterruptions values (78651,'APB02620','2011-08-18 10:30:00.000',42,1,'18082011','103000');
insert into meterinterruptions values (78652,'APB02620','2011-08-18 10:45:00.000',43,1,'18082011','104500');
insert into meterinterruptions values (78653,'APB02620','2011-08-18 11:00:00.000',44,0,'18082011','110000');
insert into meterinterruptions values (78654,'APB02620','2011-08-18 11:15:00.000',45,0,'18082011','111500');
insert into meterinterruptions values (78655,'APB02620','2011-08-18 11:30:00.000',46,0,'18082011','113000');
insert into meterinterruptions values (78656,'APB02620','2011-08-18 12:00:00.000',48,0,'18082011','120000');
insert into meterinterruptions values (78657,'APB02620','2011-08-18 12:15:00.000',49,0,'18082011','121500');
insert into meterinterruptions values (78658,'APB02620','2011-08-18 12:30:00.000',50,0,'18082011','123000');
insert into meterinterruptions values (78659,'APB02620','2011-08-18 12:45:00.000',51,0,'18082011','124500');
insert into meterinterruptions values (78667,'APB02620','2011-08-18 16:30:00.000',66,1,'18082011','163000');
insert into meterinterruptions values (78668,'APB02620','2011-08-18 16:45:00.000',67,1,'18082011','164500');
insert into meterinterruptions values (78669,'APB02620','2011-08-18 17:15:00.000',69,0,'18082011','171500');
insert into meterinterruptions values (78670,'APB02620','2011-08-18 18:45:00.000',75,1,'18082011','184500');
insert into meterinterruptions values (78671,'APB02620','2011-08-18 19:00:00.000',76,0,'18082011','190000');
insert into meterinterruptions values (78672,'APB02620','2011-08-18 19:15:00.000',77,0,'18082011','191500');
insert into meterinterruptions values (78673,'APB02620','2011-08-18 19:45:00.000',79,0,'18082011','194500');
insert into meterinterruptions values (78674,'APB02620','2011-08-18 20:00:00.000',80,0,'18082011','200000');
insert into meterinterruptions values (78675,'APB02620','2011-08-18 20:15:00.000',81,0,'18082011','201500');
insert into meterinterruptions values (78688,'APB02620','2011-08-18 23:30:00.000',94,1,'18082011','233000');
insert into meterinterruptions values (78689,'APB02620','2011-08-18 23:45:00.000',95,1,'18082011','234500');
Please help me on this ASAP....
Thanks..

Viewing 10 posts - 1 through 9 (of 9 total)

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

vince_sql Right there with Babe Points: 722 More actions · Answer 1

Not very elegant, I am still learning - but it works.

The first row is FeederStatus 0 as your initial post required, you can change it to start from 1 by changing the initial set statement on @status.

The code also assumes that records are inserted incrementally by 'Slot' - I made that assumption based on the sample data you provided, I hope its ok.

DECLARE @status int = 0;

DECLARE @slot int = 0;

DECLARE @i int = 0;

DECLARE @numRows int = (SELECT COUNT(*) FROM dbo.MeterInterruptions);

DECLARE @tempTable table (

LiveId bigint

,MeterNumber varchar(15)

,SlotTimeStamp datetime

,Slot int

,FeederStatus bit

,ModemDate varchar(10)

,ModemTime varchar(10)

);

WHILE @i <= @numRows

BEGIN

INSERT INTO @tempTable

SELECT TOP(1) *

FROM dbo.MeterInterruptions

WHERE FeederStatus = @status

AND Slot >= @slot

SET @status = (SELECT TOP(1) CASE feederstatus WHEN 1 THEN 0 WHEN 0 THEN 1 END AS [status] FROM @tempTable ORDER BY slot DESC)

SET @slot = (SELECT MAX(slot) FROM @tempTable)

SET @i += 1

END

SELECT * FROM @tempTable

http://sqlvince.blogspot.com/[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

It's the typical "Islands" problem.

Let's see hot to solve it with ranking windowed functions:

Setup: (fixed a couple of issues in your code and changed to a #temp table)

CREATE TABLE #MeterInterruptions(

[LiveId] [bigint] primary key,

[MeterNumber] [varchar](15) NOT NULL,

[SlotTimeStamp] [datetime] NOT NULL,

[Slot] [int] NOT NULL,

[FeederStatus] [bit] NOT NULL,

[ModemDate] [varchar](10) NOT NULL,

[ModemTime] [varchar](10) NOT NULL

)

insert into #meterinterruptions values (78690,'APB02620','2011-08-19 00:00:00.000',0,0,'18082011', '000000');

insert into #meterinterruptions values (78619,'APB02620','2011-08-18 00:15:00.000',1,1,'18082011','001500');

insert into #meterinterruptions values (78620,'APB02620','2011-08-18 00:30:00.000',2,1,'18082011','003000');

insert into #meterinterruptions values (78621,'APB02620','2011-08-18 00:45:00.000',3,0,'18082011','004500');

insert into #meterinterruptions values (78622,'APB02620','2011-08-18 01:15:00.000',5,0,'18082011','011500');

insert into #meterinterruptions values (78623,'APB02620','2011-08-18 01:30:00.000',6,0,'18082011','013000');

insert into #meterinterruptions values (78624,'APB02620','2011-08-18 01:45:00.000',7,0,'18082011','014500');

insert into #meterinterruptions values (78625,'APB02620','2011-08-18 02:00:00.000',8,0,'18082011','020000');

insert into #meterinterruptions values (78626,'APB02620','2011-08-18 02:15:00.000',9,0,'18082011','021500');

insert into #meterinterruptions values (78651,'APB02620','2011-08-18 10:30:00.000',42,1,'18082011','103000');

insert into #meterinterruptions values (78652,'APB02620','2011-08-18 10:45:00.000',43,1,'18082011','104500');

insert into #meterinterruptions values (78653,'APB02620','2011-08-18 11:00:00.000',44,0,'18082011','110000');

insert into #meterinterruptions values (78654,'APB02620','2011-08-18 11:15:00.000',45,0,'18082011','111500');

insert into #meterinterruptions values (78655,'APB02620','2011-08-18 11:30:00.000',46,0,'18082011','113000');

insert into #meterinterruptions values (78656,'APB02620','2011-08-18 12:00:00.000',48,0,'18082011','120000');

insert into #meterinterruptions values (78657,'APB02620','2011-08-18 12:15:00.000',49,0,'18082011','121500');

insert into #meterinterruptions values (78658,'APB02620','2011-08-18 12:30:00.000',50,0,'18082011','123000');

insert into #meterinterruptions values (78659,'APB02620','2011-08-18 12:45:00.000',51,0,'18082011','124500');

insert into #meterinterruptions values (78667,'APB02620','2011-08-18 16:30:00.000',66,1,'18082011','163000');

insert into #meterinterruptions values (78668,'APB02620','2011-08-18 16:45:00.000',67,1,'18082011','164500');

insert into #meterinterruptions values (78669,'APB02620','2011-08-18 17:15:00.000',69,0,'18082011','171500');

insert into #meterinterruptions values (78670,'APB02620','2011-08-18 18:45:00.000',75,1,'18082011','184500');

insert into #meterinterruptions values (78671,'APB02620','2011-08-18 19:00:00.000',76,0,'18082011','190000');

insert into #meterinterruptions values (78672,'APB02620','2011-08-18 19:15:00.000',77,0,'18082011','191500');

insert into #meterinterruptions values (78673,'APB02620','2011-08-18 19:45:00.000',79,0,'18082011','194500');

insert into #meterinterruptions values (78674,'APB02620','2011-08-18 20:00:00.000',80,0,'18082011','200000');

insert into #meterinterruptions values (78675,'APB02620','2011-08-18 20:15:00.000',81,0,'18082011','201500');

insert into #meterinterruptions values (78688,'APB02620','2011-08-18 23:30:00.000',94,1,'18082011','233000');

insert into #meterinterruptions values (78689,'APB02620','2011-08-18 23:45:00.000',95,1,'18082011','234500');

Solution:

WITH IslandData AS (

SELECT *,

IslandId = PosInMeter - PosInMeterStatus,

PosInIsland = ROW_NUMBER() OVER (PARTITION BY PosInMeter - PosInMeterStatus ORDER BY PosInMeter)

FROM (

SELECT *,

PosInMeter = ROW_NUMBER() OVER (PARTITION BY MeterNumber ORDER BY SlotTimeStamp),

PosInMeterStatus = ROW_NUMBER() OVER (PARTITION BY MeterNumber, FeederStatus ORDER BY SlotTimeStamp)

FROM #meterinterruptions

) AS src

)

SELECT OnFeeds.MeterNumber,

OnFeeds.SlotTimeStamp AS OnTime,

OffFeeds.SlotTimeStamp AS OffTime

FROM IslandData AS OnFeeds

CROSS APPLY (

SELECT TOP(1) SlotTimeStamp

FROM IslandData

WHERE MeterNumber = OnFeeds.MeterNumber

AND SlotTimeStamp >= OnFeeds.SlotTimeStamp

AND FeederStatus = 1

AND PosInIsland = 1

) AS OffFeeds

WHERE OnFeeds.PosInIsland = 1

AND OnFeeds.FeederStatus = 0

ORDER BY OnFeeds.SlotTimeStamp

It would be very helpful if you could add the desired output based on the sample data you provided.

Hope this helps

Gianluca

-- Gianluca Sartori

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

Here's a slightly different method. BTW check the date in your first insert.

;WITH FilteredData AS (

SELECT o.MeterNumber, o.SlotTimeStamp, o.FeederStatus,

seq = ROW_NUMBER() OVER(ORDER BY o.SlotTimeStamp)

FROM (

SELECT d.MeterNumber, d.SlotTimeStamp, d.FeederStatus,

WantedRows = ROW_NUMBER() OVER (PARTITION BY d.FeederStatus, d.rowid-d.slotid ORDER BY d.SlotTimeStamp)

FROM (

SELECT MeterNumber, SlotTimeStamp, FeederStatus,

rowid = ROW_NUMBER() OVER(ORDER BY SlotTimeStamp),

slotid = DENSE_RANK() OVER(PARTITION BY FeederStatus ORDER BY SlotTimeStamp)

FROM #meterinterruptions

) d

) o

WHERE WantedRows = 1

)

SELECT

s0.MeterNumber,

Ontime = s0.SlotTimeStamp,

Offtime = s1.SlotTimeStamp

FROM FilteredData s0

LEFT JOIN FilteredData s1

ON s1.FeederStatus = 1

AND s1.seq = s0.seq+1

WHERE s0.FeederStatus = 0

ORDER BY s0.SlotTimeStamp

^{“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

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

spaghettidba

SSC Guru

Points: 105732

October 3, 2011 at 6:05 am

#1389438

Nice one, Chris!

-- Gianluca Sartori

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

:blush: shucks it's just a copy of yours Luca!

^{“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

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

ChrisM@Work (10/3/2011)
:blush: shucks it's just a copy of yours Luca!

I don't think so!

-- Gianluca Sartori

drew.allen SSC Guru Points: 76987 More actions · Answer 7

Prasanthi Reddy (10/2/2011)
Hi,
I have a table which contains on and off status of a meter. Feederstatus = 0 means On and feederstatus=1 means Off.

You do realize that this is exactly the opposite of the conventional meanings for those values? While it shouldn't affect the code, it might cause problems with humans trying to understand/update your code.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

drew.allen (10/3/2011)
Prasanthi Reddy (10/2/2011)
Hi,
I have a table which contains on and off status of a meter. Feederstatus = 0 means On and feederstatus=1 means Off.
You do realize that this is exactly the opposite of the conventional meanings for those values? While it shouldn't affect the code, it might cause problems with humans trying to understand/update your code.
Drew

Agreed. Myself, for instance. 😉

-- Gianluca Sartori

Prasanthi Reddy SSC Veteran Points: 213 More actions · Answer 9

Gianluca Sartori (10/3/2011)
drew.allen (10/3/2011)
Prasanthi Reddy (10/2/2011)
Hi,
I have a table which contains on and off status of a meter. Feederstatus = 0 means On and feederstatus=1 means Off.
You do realize that this is exactly the opposite of the conventional meanings for those values? While it shouldn't affect the code, it might cause problems with humans trying to understand/update your code.
Drew
Agreed. Myself, for instance. 😉

I too agree with you ..

but this is the convention given by the client who is using this system.. So no other go for us 🙂

Thanks for all your help.

By using TOP I met my requirement..