mptwoadmin
Programmer
Hello,
Been working on arranging data below in desired format in SQL Db.
DateTime TagName Value
4/19/2013 8:03:36.806 machine5_Dat_badge 63430
4/19/2013 8:04:37.368 machine4_Dat_badge 29549
4/19/2013 8:05:30.943 machine6_Dat_badge 29023
4/19/2013 8:06:10.919 machine2_Dat_badge 28807
4/19/2013 8:06:56.653 machine1_Dat_badge 30007
4/19/2013 8:07:12.080 machine3_Dat_badge 36464
4/19/2013 8:13:15.180 machine7_Dat_badge 31388
4/19/2013 8:13:22.996 machine2_Dat_badge 28807
4/19/2013 8:14:32.534 machine7_Dat_badge 31388
4/19/2013 8:15:03.158 machine6_Dat_badge 29023
4/19/2013 8:15:57.653 machine1_Dat_badge 30007
4/19/2013 8:16:56.307 machine7_Dat_badge 35512
4/19/2013 8:16:58.779 machine4_Dat_badge 29549
4/19/2013 8:17:03.685 machine1_Dat_badge 35444
4/19/2013 8:17:19.245 machine5_Dat_badge 63430
4/19/2013 8:17:37.486 machine4_Dat_badge 36717
4/19/2013 8:18:12.453 machine5_Dat_badge 61329
4/19/2013 8:19:24.085 machine3_Dat_badge 36464
4/19/2013 8:20:03.315 machine3_Dat_badge 36679
4/19/2013 8:22:43.868 machine2_Dat_badge 35648
4/19/2013 8:25:28.638 machine6_Dat_badge 36750
4/19/2013 8:44:52.509 machine7_Dat_badge 35512
4/19/2013 8:45:57.612 machine6_Dat_badge 36750
4/19/2013 8:46:02.101 machine7_Dat_badge 29475
4/19/2013 8:46:07.180 machine4_Dat_badge 36717
4/19/2013 8:46:39.736 machine6_Dat_badge 63430
4/19/2013 8:47:06.509 machine2_Dat_badge 35648
4/19/2013 8:47:16.523 machine3_Dat_badge 36679
4/19/2013 8:47:57.236 machine5_Dat_badge 61329
4/19/2013 8:47:57.306 machine3_Dat_badge 29549
4/19/2013 8:48:02.824 machine2_Dat_badge 30007
4/19/2013 8:48:28.914 machine1_Dat_badge 35444
4/19/2013 8:48:55.323 machine5_Dat_badge 29023
4/19/2013 8:50:29.564 machine4_Dat_badge 36464
4/19/2013 8:51:56.866 machine1_Dat_badge 28807
Desired outcome ; This is only sampling 3 records.
machine Value start end
machine5_Dat_badge 63430 4/19/2013 8:03:36.806 4/19/2013 8:18:12.453
machine2_Dat_badge 30007 4/19/2013 8:48:02.824 N/A
machine1_Dat_badge 35444 4/19/2013 8:17:03.685 4/19/2013 8:48:28.914
Is it even possible to do this in one qry?
This is the qry i was trying to alter..the test data in in a temp table but with the new qry i'm trying to do I do not have the 1 or 0 data. (hope i explained that right)..
create table #dttest (dt datetime, mn integer, [stat] integer)
insert into #dttest values ('2013-03-26 07:46:07', 07, 1)
insert into #dttest values ('2013-03-26 07:47:49', 07, 0)
insert into #dttest values ('2013-03-26 07:48:04', 13, 1)
insert into #dttest values ('2013-03-26 07:49:57', 13, 0)
insert into #dttest values ('2013-03-26 07:50:15', 07, 1)
insert into #dttest values ('2013-03-26 07:52:11', 07, 0)
; with Data As
(
Select *, Row_Number() Over (Partition By mn, stat Order By dt) As RowId
From #dttest
)
Select A.mn, A.dt As StartFill, B.dt as EndFill
From Data As A
Inner Join Data As B
On A.mn = b.mn
And A.Stat = 1
and b.stat = 0
and a.RowId = B.RowId
Thank you
Been working on arranging data below in desired format in SQL Db.
DateTime TagName Value
4/19/2013 8:03:36.806 machine5_Dat_badge 63430
4/19/2013 8:04:37.368 machine4_Dat_badge 29549
4/19/2013 8:05:30.943 machine6_Dat_badge 29023
4/19/2013 8:06:10.919 machine2_Dat_badge 28807
4/19/2013 8:06:56.653 machine1_Dat_badge 30007
4/19/2013 8:07:12.080 machine3_Dat_badge 36464
4/19/2013 8:13:15.180 machine7_Dat_badge 31388
4/19/2013 8:13:22.996 machine2_Dat_badge 28807
4/19/2013 8:14:32.534 machine7_Dat_badge 31388
4/19/2013 8:15:03.158 machine6_Dat_badge 29023
4/19/2013 8:15:57.653 machine1_Dat_badge 30007
4/19/2013 8:16:56.307 machine7_Dat_badge 35512
4/19/2013 8:16:58.779 machine4_Dat_badge 29549
4/19/2013 8:17:03.685 machine1_Dat_badge 35444
4/19/2013 8:17:19.245 machine5_Dat_badge 63430
4/19/2013 8:17:37.486 machine4_Dat_badge 36717
4/19/2013 8:18:12.453 machine5_Dat_badge 61329
4/19/2013 8:19:24.085 machine3_Dat_badge 36464
4/19/2013 8:20:03.315 machine3_Dat_badge 36679
4/19/2013 8:22:43.868 machine2_Dat_badge 35648
4/19/2013 8:25:28.638 machine6_Dat_badge 36750
4/19/2013 8:44:52.509 machine7_Dat_badge 35512
4/19/2013 8:45:57.612 machine6_Dat_badge 36750
4/19/2013 8:46:02.101 machine7_Dat_badge 29475
4/19/2013 8:46:07.180 machine4_Dat_badge 36717
4/19/2013 8:46:39.736 machine6_Dat_badge 63430
4/19/2013 8:47:06.509 machine2_Dat_badge 35648
4/19/2013 8:47:16.523 machine3_Dat_badge 36679
4/19/2013 8:47:57.236 machine5_Dat_badge 61329
4/19/2013 8:47:57.306 machine3_Dat_badge 29549
4/19/2013 8:48:02.824 machine2_Dat_badge 30007
4/19/2013 8:48:28.914 machine1_Dat_badge 35444
4/19/2013 8:48:55.323 machine5_Dat_badge 29023
4/19/2013 8:50:29.564 machine4_Dat_badge 36464
4/19/2013 8:51:56.866 machine1_Dat_badge 28807
Desired outcome ; This is only sampling 3 records.
machine Value start end
machine5_Dat_badge 63430 4/19/2013 8:03:36.806 4/19/2013 8:18:12.453
machine2_Dat_badge 30007 4/19/2013 8:48:02.824 N/A
machine1_Dat_badge 35444 4/19/2013 8:17:03.685 4/19/2013 8:48:28.914
Is it even possible to do this in one qry?
This is the qry i was trying to alter..the test data in in a temp table but with the new qry i'm trying to do I do not have the 1 or 0 data. (hope i explained that right)..
create table #dttest (dt datetime, mn integer, [stat] integer)
insert into #dttest values ('2013-03-26 07:46:07', 07, 1)
insert into #dttest values ('2013-03-26 07:47:49', 07, 0)
insert into #dttest values ('2013-03-26 07:48:04', 13, 1)
insert into #dttest values ('2013-03-26 07:49:57', 13, 0)
insert into #dttest values ('2013-03-26 07:50:15', 07, 1)
insert into #dttest values ('2013-03-26 07:52:11', 07, 0)
; with Data As
(
Select *, Row_Number() Over (Partition By mn, stat Order By dt) As RowId
From #dttest
)
Select A.mn, A.dt As StartFill, B.dt as EndFill
From Data As A
Inner Join Data As B
On A.mn = b.mn
And A.Stat = 1
and b.stat = 0
and a.RowId = B.RowId
Thank you