CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
JB32
JB32

Reputation: 21

SQL Calculating Changes Within a Column

I have data from an accelerometer which measures activity levels – the output is given as number of seconds in each minute spent standing or lying (see table below for an example from 1 individual).

**Date**   **Time**  **Standing** **Lying**
21/02/2017  10:15:00    0      60
21/02/2017  10:16:00    0      60
21/02/2017  10:17:00    0      60
21/02/2017  10:18:00    0      60
21/02/2017  10:19:00    0      60
21/02/2017  10:20:00    0      60
21/02/2017  10:21:00    43     17
21/02/2017  10:22:00    60     0
21/02/2017  10:23:00    60     0
21/02/2017  10:24:00    46     14
21/02/2017  10:25:00    0      60
21/02/2017  10:26:00    0      60
21/02/2017  10:27:00    0      60
21/02/2017  10:28:00    0      60
21/02/2017  10:29:00    0      60
21/02/2017  10:30:00    0      60
21/02/2017  10:31:00    0      60
21/02/2017  10:32:00    0      60

I am interested in how activity changes for each individual on a day by day basis. So interested in calculating the number of times the patient changes from lying to standing in a 24 hr period – e.g. how many times does a period of lying occur in 24 hrs – but with a threshold for 60s included. For example if the data changes from lying to standing for only a number of seconds then it is likely false.

A second thing I’d be interested in doing is calculating the average time each (true) lying bout is for, again in a 24 hr period within each individual.

What would be the best approach within SQL Server for each?

EDIT

Question above is too broad. I want to know the best way to calculate total number of lying bouts AND the average length of lying bout - both in a 24 hr period

A Transition would be anything that changes from lying to standing (e.g. 10:21:00 on the table) or the reverse - standing to lying (e.g. 10:24:00)

Date Bouts AvBoutTime 
21/2 41    20 
22/2 38    25 
23/2 48    17

Upvotes: 1

Views: 124

Answers (2)

Mazhar
Mazhar

Reputation: 3837

This is a Gaps and Islands question

DECLARE @tab TABLE ([Date] DATE, [Time] TIME, Standing INT, Lying INT )
INSERT INTO @tab
(Date, Time, Standing, Lying)
VALUES
('2017-02-21','10:15:00',0,60),
('2017-02-21','10:16:00',0,60),
('2017-02-21','10:17:00',0,60),
('2017-02-21','10:18:00',0,60),
('2017-02-21','10:19:00',0,60),
('2017-02-21','10:20:00',0,60),
('2017-02-21','10:21:00',43,17),
('2017-02-21','10:22:00',60,0),
('2017-02-21','10:23:00',60,0),
('2017-02-21','10:24:00',46,14),
('2017-02-21','10:25:00',0,60),
('2017-02-21','10:26:00',0,60),
('2017-02-21','10:27:00',0,60),
('2017-02-21','10:28:00',0,60),
('2017-02-21','10:29:00',0,60),
('2017-02-21','10:30:00',0,60),
('2017-02-21','10:31:00',0,60),
('2017-02-21','10:32:00',0,60)

SELECT 
      Y.[Date]
    , Bouts     = COUNT(DISTINCT Y.Island) --DISTINCT Islands for number of bouts
    , AvBoutTime=CAST((SUM(Y.Lying)/ (COUNT(DISTINCT Y.Island)* 1.0) ) AS DECIMAL(9,1))
FROM
(
    SELECT
            --Break the dataset down into ISLANDS- using the funky technique
          Island = X.RN - ROW_NUMBER()OVER(PARTITION BY X.Date ORDER BY X.RN)
        , X.[Date]
        , X.[Time]
        , X.Standing
        , X.Lying
    FROM
    (
        SELECT
                --CREATE AN INCREMENTING ID Column like an Identity using ROW_NUmber
                --No need for this if there's already is one just missing from the example shown
              RN = ROW_NUMBER()OVER(PARTITION BY T.[Date] ORDER BY T.[Time] ASC)
            , T.[Date]
            , T.[Time]
            , T.Standing
            , T.Lying 
        FROM @tab T
    ) X
    WHERE X.Lying > 0
) Y
GROUP BY
      Y.[Date]
ORDER BY [Y].[Date]

Output

Date        Bouts   AvBoutTime
2017-02-21  2       435.5

Upvotes: 0

Alexis.Rolland
Alexis.Rolland

Reputation: 6353

I have only a partial answer here but I'll post it in case it helps and I might look into it if I have additional ideas.

Here is what I have plus the link to the SQL Fiddle: http://sqlfiddle.com/#!18/72532/27/0

Table structure

CREATE TABLE Activity (
  [Date] varchar(10),
  [Time] varchar(8),
  [Standing] Integer,
  [Lying] Integer
);


INSERT INTO Activity ([Date], [Time], [Standing], [Lying])
VALUES
('21/02/2017', '10:15:00', 0,  60),
('21/02/2017', '10:16:00', 0,  60),
('21/02/2017', '10:17:00', 0,  60),
('21/02/2017', '10:18:00', 0,  60),
('21/02/2017', '10:19:00', 0,  60),
('21/02/2017', '10:20:00', 0,  60),
('21/02/2017', '10:21:00', 43, 17),
('21/02/2017', '10:22:00', 60, 0),
('21/02/2017', '10:23:00', 60, 0),
('21/02/2017', '10:24:00', 46, 14),
('21/02/2017', '10:25:00', 0,  60),
('21/02/2017', '10:26:00', 0,  60),
('21/02/2017', '10:27:00', 0,  60),
('21/02/2017', '10:28:00', 0,  60),
('21/02/2017', '10:29:00', 0,  60),
('21/02/2017', '10:30:00', 0,  60),
('21/02/2017', '10:31:00', 0,  60),
('21/02/2017', '10:32:00', 0,  60)
;

Intermediate Table

I have created an intermediate table where I pivoted the data into a CurrentPosture and Duration columns. I have also added a PreviousPosture column to be able to track the changes.

CREATE TABLE ActivityPivoted (
  [Date] varchar(10),
  [Time] varchar(8),
  [Duration] Integer,
  [CurrentPosture] varchar(20),
  [PreviousPosture] varchar(20)
);

INSERT INTO ActivityPivoted
select Date,
Time,
Duration,
CurrentPosture,
lag(CurrentPosture, 1, 'N/A') over(order by Time) as 'PreviousPosture'
from (
    select *,
    case when Standing>Lying then 'Standing' else 'Lying' end as CurrentPosture,
    case when Standing>Lying then Standing else Lying end as Duration
    from Activity
) tmp
order by Time

Here is what it contains:

|       Date |     Time | Duration | CurrentPosture | PreviousPosture |
|------------|----------|----------|----------------|-----------------|
| 21/02/2017 | 10:15:00 |       60 |          Lying |             N/A |
| 21/02/2017 | 10:16:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:17:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:18:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:19:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:20:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:21:00 |       43 |       Standing |           Lying |
| 21/02/2017 | 10:22:00 |       60 |       Standing |        Standing |
| 21/02/2017 | 10:23:00 |       60 |       Standing |        Standing |
| 21/02/2017 | 10:24:00 |       46 |       Standing |        Standing |
| 21/02/2017 | 10:25:00 |       60 |          Lying |        Standing |
| 21/02/2017 | 10:26:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:27:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:28:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:29:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:30:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:31:00 |       60 |          Lying |           Lying |
| 21/02/2017 | 10:32:00 |       60 |          Lying |           Lying |

From there, it's acutally quite easy to count the number of posture changes

select CurrentPosture,
count(*)
from ActivityPivoted
where CurrentPosture<>PreviousPosture
group by CurrentPosture

Result

| CurrentPosture |   |
|----------------|---|
|          Lying | 2 |
|       Standing | 1 |

Unfortunately, I haven't been able to sum the duration for each posture partition because SQL Server is not resetting the sum over the partition. Example:

select *,
sum(Duration) over(partition by CurrentPosture order by Time)
from ActivityPivoted
order by Time

Result:

|       Date |     Time | Duration | CurrentPosture | PreviousPosture |     |
|------------|----------|----------|----------------|-----------------|-----|
| 21/02/2017 | 10:15:00 |       60 |          Lying |             N/A |  60 |
| 21/02/2017 | 10:16:00 |       60 |          Lying |           Lying | 120 |
| 21/02/2017 | 10:17:00 |       60 |          Lying |           Lying | 180 |
| 21/02/2017 | 10:18:00 |       60 |          Lying |           Lying | 240 |
| 21/02/2017 | 10:19:00 |       60 |          Lying |           Lying | 300 |
| 21/02/2017 | 10:20:00 |       60 |          Lying |           Lying | 360 |
| 21/02/2017 | 10:21:00 |       43 |       Standing |           Lying |  43 |
| 21/02/2017 | 10:22:00 |       60 |       Standing |        Standing | 103 |
| 21/02/2017 | 10:23:00 |       60 |       Standing |        Standing | 163 |
| 21/02/2017 | 10:24:00 |       46 |       Standing |        Standing | 209 |
| 21/02/2017 | 10:25:00 |       60 |          Lying |        Standing | 420 | < Sum not reset
| 21/02/2017 | 10:26:00 |       60 |          Lying |           Lying | 480 |
| 21/02/2017 | 10:27:00 |       60 |          Lying |           Lying | 540 |
| 21/02/2017 | 10:28:00 |       60 |          Lying |           Lying | 600 |
| 21/02/2017 | 10:29:00 |       60 |          Lying |           Lying | 660 |
| 21/02/2017 | 10:30:00 |       60 |          Lying |           Lying | 720 |
| 21/02/2017 | 10:31:00 |       60 |          Lying |           Lying | 780 |
| 21/02/2017 | 10:32:00 |       60 |          Lying |           Lying | 840 |

Upvotes: 1

Related Questions