August 26, 2014 at 8:57 am
hi all, ( first at all i want to thank you for your answres wich help me alot :))
drop table #test
create table #test ( num int ,orig_time datetime,flg int,value int)
--select * from #test
insert #test (num,orig_time,flg ,value)
select 1,'2014-08-26 13:15:03.830',1,10
union all
select 1,'2014-08-26 13:20:03.830',0,12
union all
select 1,'2014-08-26 13:24:03.830',0,15
union all
select 1,'2014-08-26 13:26:03.830',1,25
union all
select 1,'2014-08-26 13:27:03.830',0,30
union all
select 1,'2014-08-26 13:32:03.830',0,35
union all
select 1,'2014-08-26 13:34:03.830',1,39
union all
select 1,'2014-08-26 13:38:03.830',1,40
union all
select 1,'2014-08-26 13:45:03.830',0,42
result:
select * from #test
'2014-08-26 13:15:03.830','2014-08-26 13:15:03.830',2
'2014-08-26 13:26:03.830','2014-08-26 13:27:03.830',5
'2014-08-26 13:34:03.830','2014-08-26 13:45:03.830',3
every time flag change from 0 to 1 or from 1 to 0
i need to menipulte value between flg chaging
thank alot
sharon
August 26, 2014 at 1:39 pm
Triggers can be used for this.
August 27, 2014 at 3:42 am
where you want to manipulate the value? During INSERT operation?
Or you are trying to build SQL Query to retrieve the data as per your need?
Thanks
August 28, 2014 at 12:16 am
hi,
trying to build SQL Query to retrieve the data
sharon
August 28, 2014 at 7:20 am
sharon-472085 (8/28/2014)
hi,trying to build SQL Query to retrieve the data
sharon
You haven't given us a lot to go on here but I will take a shot in the dark. I think that what you are trying to do is do something when the value of "flg" changes.
I would urge you to not use a column as a flag. And by all means why bother abbreviating flag? It is only 4 characters. I would rather use a meaningful name. What does flag represent? Regardless of that I just don't know what you are trying to do.
every time flag change from 0 to 1 or from 1 to 0
i need to menipulte value between flg chaging
I would help but given the above comment I have no idea what you wanting to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2014 at 8:00 am
Here is a quick SQL 2K8 solution, toggles by the flag changes using self-join, SQL 2012 solution would be easier with running total;-)
😎
USE tempdb;
GO
create table #test ( num int ,orig_time datetime,flg int,value int)
insert #test (num,orig_time,flg ,value)
select 1,'2014-08-26 13:15:03.830',1,10
union all
select 1,'2014-08-26 13:20:03.830',0,12
union all
select 1,'2014-08-26 13:24:03.830',0,15
union all
select 1,'2014-08-26 13:26:03.830',1,25
union all
select 1,'2014-08-26 13:27:03.830',0,30
union all
select 1,'2014-08-26 13:32:03.830',0,35
union all
select 1,'2014-08-26 13:34:03.830',1,39
union all
select 1,'2014-08-26 13:38:03.830',1,40
union all
select 1,'2014-08-26 13:45:03.830',0,42;
select * from #test
;WITH BASE_DATA AS
(
SELECT
T.num
,T.orig_time
,T.flg
,T.value
,ROW_NUMBER() OVER
(
ORDER BY T.orig_time ASC
) AS BD_RID
FROM #test T
)
,GROUPED_DATA AS
(
SELECT
BA.BD_RID AS A_RID
,MIN(BB.BD_RID) AS B_RID
,MIN(BA.orig_time) AS A_orig_time
,MAX(BB.orig_time) AS B_orig_time
FROM BASE_DATA BA
OUTER APPLY BASE_DATA BB
WHERE BA.BD_RID < BB.BD_RID
AND BA.flg <> BB.flg
GROUP BY BA.BD_RID
)
,COMP_INTERVALS AS
(
SELECT
GD.A_RID
,GD.B_RID
,GD.A_orig_time
,GD.B_orig_time
,MAX(GD.A_RID) OVER (PARTITION BY GD.B_RID) AS GR_RID
,ROW_NUMBER() OVER
(
PARTITION BY GD.B_RID
ORDER BY GD.A_RID ASC
) AS CI_RID
FROM GROUPED_DATA GD
)
SELECT
CI.A_orig_time
,CI.B_orig_time
,(CI.B_RID + 1) - CI.A_RID
FROM COMP_INTERVALS CI
WHERE CI.CI_RID = 1;
DROP TABLE #test
Results
A_orig_time B_orig_time
----------------------- ----------------------- --
2014-08-26 13:15:03.830 2014-08-26 13:45:03.830 2
2014-08-26 13:20:03.830 2014-08-26 13:38:03.830 3
2014-08-26 13:26:03.830 2014-08-26 13:45:03.830 2
2014-08-26 13:27:03.830 2014-08-26 13:38:03.830 3
2014-08-26 13:34:03.830 2014-08-26 13:45:03.830 3
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply