August 24, 2014 at 1:37 am
hi all,
i need to find solution to this situation.
create table #test (name nvarchar (10),point_of_time datetime)
insert #test (name,point_of_time)
select 'd','2014-08-19 13:08:55.517'
union all
select 'a','2014-08-19 13:10:55.517'
union all
select 'a','2014-08-19 13:11:55.517'
union all
select 'a','2014-08-19 13:12:55.517'
union all
select 'b','2014-08-19 13:15:55.517'
union all
select 'b','2014-08-19 13:26:55.517'
union all
select 'b','2014-08-19 13:56:55.517'
order by 2 asc -- record insert ordered from first to last
--select * from #test
i need quary from this with the follwing result :
name , start_point_of_time,end_of_point_in_time
d, '2014-08-19 13:08:55.517','2014-08-19 13:10:55.517'
a, '2014-08-19 13:10:55.517','2014-08-19 13:15:55.517'
b, '2014-08-19 13:15:55.517' ,'2014-08-19 13:56:55.517'
i will explian :
find first record bring point of time.
when name column is changed we need to close the lase record that open with the value of new point_time and open new
record with this point of time.
thank alot
sharon,
August 24, 2014 at 3:55 am
Here's a solution based on the "Groups'n'Islands" concept:
WITH cte AS
(
select *,
ROW_NUMBER() OVER (ORDER BY point_of_time)-ROW_NUMBER() OVER (PARTITION BY name ORDER BY point_of_time) as NameGrp
from #test
),
cte_grp as
(
SELECT
min(name) as Name,
min(point_of_time) start_point_of_time,
max(point_of_time) end_of_point_in_time,
ROW_NUMBER() OVER(ORDER BY min(point_of_time)) pos
FROM cte
group by NameGrp
)
SELECT grp1.Name, grp1.start_point_of_time, ISNULL(grp2.start_point_of_time,grp1.end_of_point_in_time) end_of_point_in_time
FROM cte_grp grp1 LEFT OUTER JOIN cte_grp grp2 ON grp1.pos = grp2.pos -1
ORDER BY start_point_of_time
August 24, 2014 at 4:29 am
Here is a quick code demonstrating the grouping of the set, not a solution but should get you started. BTW, Itzik Ben-Gan and Dwain Camps have written excellent articles on the subject, well worth the reading (sorry I don't have the links at hand)
😎
USE tempdb;
GO
create table #test (name nvarchar (10),point_of_time datetime)
insert #test (name,point_of_time)
select 'd','2014-08-19 13:08:55.517'
union all
select 'a','2014-08-19 13:10:55.517'
union all
select 'a','2014-08-19 13:11:55.517'
union all
select 'a','2014-08-19 13:12:55.517'
union all
select 'b','2014-08-19 13:15:55.517'
union all
select 'b','2014-08-19 13:26:55.517'
union all
select 'b','2014-08-19 13:56:55.517'
order by 2 asc -- record insert ordered from first to last
select
T.name
,T.point_of_time
,ABS(ROW_NUMBER() OVER (PARTITION BY T.name ORDER BY T.point_of_time)
- DENSE_RANK() OVER (ORDER BY T.point_of_time,T.name)) AS GROUP_NO
from #test T
drop table #test
Results
name point_of_time GROUP_NO
---------- ----------------------- ----------
d 2014-08-19 13:08:55.517 0
a 2014-08-19 13:10:55.517 1
a 2014-08-19 13:11:55.517 1
a 2014-08-19 13:12:55.517 1
b 2014-08-19 13:15:55.517 4
b 2014-08-19 13:26:55.517 4
b 2014-08-19 13:56:55.517 4
August 24, 2014 at 1:58 pm
Here is yet another solution, similar to Lutz's apart from the grouping. Tends to be slightly quicker on small sets.
😎
USE tempdb;
GO
create table #test (name nvarchar (10),point_of_time datetime)
insert #test (name,point_of_time)
select 'd','2014-08-19 13:08:55.517'
union all
select 'a','2014-08-19 13:10:55.517'
union all
select 'a','2014-08-19 13:11:55.517'
union all
select 'a','2014-08-19 13:12:55.517'
union all
select 'b','2014-08-19 13:15:55.517'
union all
select 'b','2014-08-19 13:26:55.517'
union all
select 'b','2014-08-19 13:56:55.517'
order by 2 asc -- record insert ordered from first to last
;WITH BASE_DATA AS
(
SELECT
T.name
,MIN(T.point_of_time) OVER (PARTITION BY T.name) AS MN_T
,MAX(T.point_of_time) OVER (PARTITION BY T.name) AS MX_T
,ROW_NUMBER() OVER (ORDER BY T.point_of_time) AS BD_RID
FROM #test T
)
,GROUPED_DATA AS
(
SELECT
BD.name
,CASE WHEN BD.name = BE.name THEN 0 ELSE 1 END AS GR_SHIFT
,BD.MN_T AS start_point_of_time
,ISNULL(BE.MN_T,BD.MX_T) AS end_of_point_in_time
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA BE
ON BD.BD_RID = BE.BD_RID - 1
)
SELECT
GD.name
,GD.start_point_of_time
,GD.end_of_point_in_time
FROM GROUPED_DATA GD
WHERE GR_SHIFT = 1
drop table #test;
Results
name start_point_of_time end_of_point_in_time
---------- ----------------------- -----------------------
d 2014-08-19 13:08:55.517 2014-08-19 13:10:55.517
a 2014-08-19 13:10:55.517 2014-08-19 13:15:55.517
b 2014-08-19 13:15:55.517 2014-08-19 13:56:55.517
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply