I know there is lot of answers but none of my use. My table looked like
where we store meter on and off status on given time. After every Off status there is an On status. We want to transpose table like this
MeterNo | [power off] | [power on]
____________________________________________________________________
x0039938 | 2016-10-08 14:14:37.610 | 2016-10-08 14:17:15.047
____________________________________________________________________
x0039938 | 2016-10-08 14:20:50.257 | NULL
____________________________________________________________________
x0039938 | 2016-10-08 14:23:07.610 | 2016-10-08 14:23:17.920
____________________________________________________________________
x0039940 | 2016-10-08 15:45:38.250 | 2016-10-08 15:52:40.080
____________________________________________________________________
and so on
I tried this query
SELECT *
FROM
(
SELECT MeterNo, Status, CreatedOn
FROM [BreakdownShutdowDetails]
) s
PIVOT
(
MAX(CreatedOn) FOR Status IN ( [power off], [power on])
) p
which gives only on result per meterno like this
Basic idea to get time duration between Off on On status of each meter. Please suggest for right query. Thanks.
Update 1 Sample data
1 x0039938 power off 2016-10-08 14:14:37.610
2 x0039938 power on 2016-10-08 14:17:15.047
3 x0039938 power off 2016-10-08 14:20:50.257
4 x0039938 power off 2016-10-08 14:23:07.610
5 x0039938 power on 2016-10-08 14:23:17.920
6 x0039940 power off 2016-10-08 15:45:38.250
7 x0039940 power on 2016-10-08 15:52:40.080
8 x0040281 power off 2016-10-08 15:59:26.513
9 x0040281 power on 2016-10-08 16:20:23.323
10 x0039940 power off 2016-10-08 16:26:29.133
19 x0040281 power off 2016-10-08 17:17:48.900
22 x0039937 power off 2016-10-08 17:24:24.617
23 x0039937 power on 2016-10-08 17:24:38.590
24 x0039937 power off 2016-10-08 17:33:31.843
25 x0039937 power on 2016-10-08 17:35:47.470
27 x0039940 power off 2016-10-08 17:37:18.360
28 x0040281 power on 2016-10-08 17:40:08.093
30 x0043637 power off 2016-10-09 14:32:23.130
31 x0039937 power off 2016-10-09 14:32:24.893
32 x0040281 power off 2016-10-09 14:32:27.387
33 x0039940 power off 2016-10-09 14:32:29.407
34 x0040281 power on 2016-10-09 15:01:42.480
It called commonly called as Gaps and islands problem. Try something like this (Not tested)