Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Separate Groups by Sequential ID
Add to briefcase

Separate Groups by Sequential ID Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
gmaendel
Posted Friday, February 08, 2013 4:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 9:48 AM
Points: 1, Visits: 12
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
Post #1417950
opc.three
Posted Saturday, February 09, 2013 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1418006
dwain.c
Posted Sunday, February 10, 2013 7:10 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 2,370, Visits: 3,251
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, 0
UNION ALL 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:15:30', 1, 1
UNION ALL SELECT '2013-01-20 02:15:30', 1, 1
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:16:30', 0, 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', 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
UNION ALL SELECT '2013-01-20 02:18:30', 1, 1
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 by Jeff Moden.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1418180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.