Arranging Data - Start & end times Pivot 1

[mptwoadmin]

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

 

[gmmastros]

based on your sample data, what would be the expected results?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Please take a look at this query and let me know if it works for you.

SET NOCOUNT ON
Declare @Temp Table(DataDateTime DateTime, TagName VarChar(50), Value int)

Insert Into @Temp Values('4/19/2013 8:03:36.806','machine5_Dat_badge',	63430)
Insert Into @Temp Values('4/19/2013 8:04:37.368','machine4_Dat_badge',	29549)
Insert Into @Temp Values('4/19/2013 8:05:30.943','machine6_Dat_badge',	29023)
Insert Into @Temp Values('4/19/2013 8:06:10.919','machine2_Dat_badge',	28807)
Insert Into @Temp Values('4/19/2013 8:06:56.653','machine1_Dat_badge',	30007)
Insert Into @Temp Values('4/19/2013 8:07:12.080','machine3_Dat_badge',	36464)
Insert Into @Temp Values('4/19/2013 8:13:15.180','machine7_Dat_badge',	31388)
Insert Into @Temp Values('4/19/2013 8:13:22.996','machine2_Dat_badge',	28807)
Insert Into @Temp Values('4/19/2013 8:14:32.534','machine7_Dat_badge',	31388)
Insert Into @Temp Values('4/19/2013 8:15:03.158','machine6_Dat_badge',	29023)
Insert Into @Temp Values('4/19/2013 8:15:57.653','machine1_Dat_badge',	30007)
Insert Into @Temp Values('4/19/2013 8:16:56.307','machine7_Dat_badge',	35512)
Insert Into @Temp Values('4/19/2013 8:16:58.779','machine4_Dat_badge',	29549)
Insert Into @Temp Values('4/19/2013 8:17:03.685','machine1_Dat_badge',	35444)
Insert Into @Temp Values('4/19/2013 8:17:19.245','machine5_Dat_badge',	63430)
Insert Into @Temp Values('4/19/2013 8:17:37.486','machine4_Dat_badge',	36717)
Insert Into @Temp Values('4/19/2013 8:18:12.453','machine5_Dat_badge',	61329)
Insert Into @Temp Values('4/19/2013 8:19:24.085','machine3_Dat_badge',	36464)
Insert Into @Temp Values('4/19/2013 8:20:03.315','machine3_Dat_badge',	36679)
Insert Into @Temp Values('4/19/2013 8:22:43.868','machine2_Dat_badge',	35648)
Insert Into @Temp Values('4/19/2013 8:25:28.638','machine6_Dat_badge',	36750)
Insert Into @Temp Values('4/19/2013 8:44:52.509','machine7_Dat_badge',	35512)
Insert Into @Temp Values('4/19/2013 8:45:57.612','machine6_Dat_badge',	36750)
Insert Into @Temp Values('4/19/2013 8:46:02.101','machine7_Dat_badge',	29475)
Insert Into @Temp Values('4/19/2013 8:46:07.180','machine4_Dat_badge',	36717)
Insert Into @Temp Values('4/19/2013 8:46:39.736','machine6_Dat_badge',	63430)
Insert Into @Temp Values('4/19/2013 8:47:06.509','machine2_Dat_badge',	35648)
Insert Into @Temp Values('4/19/2013 8:47:16.523','machine3_Dat_badge',	36679)
Insert Into @Temp Values('4/19/2013 8:47:57.236','machine5_Dat_badge',	61329)
Insert Into @Temp Values('4/19/2013 8:47:57.306','machine3_Dat_badge',	29549)
Insert Into @Temp Values('4/19/2013 8:48:02.824','machine2_Dat_badge',	30007)
Insert Into @Temp Values('4/19/2013 8:48:28.914','machine1_Dat_badge',	35444)
Insert Into @Temp Values('4/19/2013 8:48:55.323','machine5_Dat_badge',	29023)
Insert Into @Temp Values('4/19/2013 8:50:29.564','machine4_Dat_badge',	36464)
Insert Into @Temp Values('4/19/2013 8:51:56.866','machine1_Dat_badge',	28807)

; With Data As
(
  Select *,
         Row_Number() Over (Partition By TagName, Value Order By DataDateTime) As RowId,
         Row_Number() Over (Partition By TagName, Value Order By DataDateTime DESC) As ReverseRowId
  From   @Temp
)
Select	A.TagName,
        A.Value,
        A.DataDateTime As Start,
        NullIf(B.DataDateTime, A.DataDateTime) As EndTime
From    Data As A
        Inner Join Data As B
          On A.TagName = B.TagName
	  And A.Value = B.Value
	  And A.RowId = 1
	  And B.ReverseRowId = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mptwoadmin]

hi sorry i did not get back sooner. I tried your qry as is and works for the data in the order it is. If I put a qualifier in such as order by or even more "tagname = 'machine5_Dat_badge'" then the qry fails. How would you modify the qry to handle when a tagname is being defined or the value is being defined such as "value = '28807'"

Thank You again.

 

[gmmastros]

This query basically has 2 parts to it. The top part is considered a CTE. For filtering data, I would put it there. Ex:

; With Data As
(
  Select *,
         Row_Number() Over (Partition By TagName, Value Order By DataDateTime) As RowId,
         Row_Number() Over (Partition By TagName, Value Order By DataDateTime DESC) As ReverseRowId
  From   @Temp
  [!]Where  Value = '28807'[/!]
)
Select	A.TagName,
        A.Value,
        A.DataDateTime As Start,
        NullIf(B.DataDateTime, A.DataDateTime) As EndTime
From    Data As A
        Inner Join Data As B
          On A.TagName = B.TagName
	  And A.Value = B.Value
	  And A.RowId = 1
	  And B.ReverseRowId = 1

The reason I would put it there is because I usually try to filter out as much data as possible, as early as possible. It's possible that the SQL query engine would be smart enough to figure out the filtering if you put it in the outer query (at the bottom of the code), but why leave things to chance?

As far as ordering goes, you really need to put that at the end. Notice that the outer query is written like as though it is using 2 tables, but they are actually the same table.

From    [!]Data As A[/!]
        Inner Join [!]Data As B[/!]
          On A.TagName = B.TagName
	  And A.Value = B.Value
	  And A.RowId = 1
	  And B.ReverseRowId = 1

The important thing to understand here is that there are "two" tables, and each on eis aliased. This means that you MUST refer to the alias name when referencing the columns. Notice the select clause:

Select	[!]A[/!].TagName,
        [!]A[/!].Value,
        [!]A[/!].DataDateTime As Start,
        NullIf([!]B[/!].DataDateTime, [!]A[/!].DataDateTime) As EndTime

The select clause is only referencing the table aliases. Based on the join clause, A.TagName is the same as B.TagName and A.Value is the same a B.Value. This means that the first 2 columns in the select could be changed to pull from the B table and you wouldn't see any difference in the output.

Anyway... to add an order by clause, you need to put it at the end and reference one of the table aliases, like this:

; With Data As
(
  Select *,
         Row_Number() Over (Partition By TagName, Value Order By DataDateTime) As RowId,
         Row_Number() Over (Partition By TagName, Value Order By DataDateTime DESC) As ReverseRowId
  From   @Temp
)
Select	A.TagName,
        A.Value,
        A.DataDateTime As Start,
        NullIf(B.DataDateTime, A.DataDateTime) As EndTime
From    Data As A
        Inner Join Data As B
          On A.TagName = B.TagName
	  And A.Value = B.Value
	  And A.RowId = 1
	  And B.ReverseRowId = 1 
[!]Order By A.Value[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mptwoadmin]

Wow..thank you so much for the explaination..That helped so much.