Separate Groups by Sequential ID

Question

Separate Groups by Sequential ID

gmaendel

Newbie

Points: 3
More actions
February 8, 2013 at 4:31 pm

#273952

Hi All,
First time posting to SQLservercentral.
I have a table called DataLogging that looks like the picture attached.
I would like to get only the Data where "StartSwitch" AND "Wet/DrySwitch" are on.
I can do that easy enough, but now I would like to get the first "DateAndTime" and the last "DateAndTime" in a group where the ID is sequential.
IE.
SELECT * FROM "DataLoggingTable" WHERE "StSwitch" == 'TRUE' AND "WetDrySwitch" == 'TRUE' ORDER BY "ID"
Then I want to get the First "ID" and the last "ID" where the "ID" is sequential.
after that I'd like to get the "DateAndTime" and find out how long they were both "TRUE" for.
I hope I explained it well enough.
thanks in advance for any help.
GM

Viewing 3 posts - 1 through 3 (of 3 total)

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

gmaendel (2/8/2013)
Hi All,
First time posting to SQLservercentral.
I have a table called DataLogging that looks like the picture attached.
I would like to get only the Data where "StartSwitch" AND "Wet/DrySwitch" are on.
I can do that easy enough, but now I would like to get the first "DateAndTime" and the last "DateAndTime" in a group where the ID is sequential.
IE.
SELECT * FROM "DataLoggingTable" WHERE "StSwitch" == 'TRUE' AND "WetDrySwitch" == 'TRUE' ORDER BY "ID"
Then I want to get the First "ID" and the last "ID" where the "ID" is sequential.
after that I'd like to get the "DateAndTime" and find out how long they were both "TRUE" for.
I hope I explained it well enough.
thanks in advance for any help.
GM

Welcome to SSC! Unfortunately the image is not showing for me. At any rate, a better way to proceed is for you to post code. Ideally if you would provide DDL for your table (CREATE TABLE statement), sample data for your test table (INSERT statements) and your expected results based in the test data. The reason for this is that we would like to setup your table and data in our test environment and try writing a query that will deliver something that looks like your expected results. If you can provide those three things I am confident we can help you find a solution.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

OPC.THREE was asking you to put your data into a format something like this:

DECLARE @Switches TABLE

(ID INT IDENTITY

,MyDate DATETIME

,StartSwitch INT

,WetDrySwitch INT)

INSERT INTO @Switches

SELECT '2013-01-20 02:15:30', 1, 0

UNION ALL SELECT '2013-01-20 02:15:30', 1, 0

UNION ALL SELECT '2013-01-20 02:15:30', 1, 1

UNION ALL SELECT '2013-01-20 02:16:30', 1, 1

UNION ALL SELECT '2013-01-20 02:16:30', 0, 1

UNION ALL SELECT '2013-01-20 02:17:30', 0, 0

UNION ALL SELECT '2013-01-20 02:17:30', 1, 0

UNION ALL SELECT '2013-01-20 02:18:30', 1, 0

UNION ALL SELECT '2013-01-20 02:18:30', 1, 1

One solution to this could be something like:

;WITH MyData AS (

SELECT ID, MyDate, StartSwitch, WetDrySwitch

,n=ID-ROW_NUMBER() OVER (ORDER BY MyDate)

FROM @Switches

WHERE StartSwitch = 1 AND WetDrySwitch = 1)

SELECT MinID=MIN(ID), MaxID=MAX(ID)

,MinDate=MIN(MyDate), MaxDate=MAX(MyDate)

,[TimeDiffSec]=DATEDIFF(second, MIN(MyDate), MAX(MyDate))

FROM MyData

GROUP BY n

Reference article on this approach: Group Islands of Contiguous Dates[/url] by Jeff Moden.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St