August 22, 2012 at 9:56 am
First I want to Thanks for Reading this.
SOURCE:-
NM_ID STRT_DT END_DT
ABD 01/10/2010 01/11/2010
ABD 01/11/2010 01/12/2011
ABD 01/12/2011 01/01/9999
BBD 01/20/2010 01/20/2011
BBD 01/20/2012 01/01/9999
TARGET:-
NM_ID STRT_DT END _DT
ABD 01/10/2010 01/01/9999
BBD 01/20/2010 01/20/2011
BBD 01/20/2012 01/01/9999
By seeing the above example while extracting the data from source table to target i want to use if Particular Id END_DT=STRT_DT then collaspe it to One Line instead of 2 lines.
Please provide me the logic in SQL
August 22, 2012 at 11:56 am
--edit. Removed post.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 22, 2012 at 12:10 pm
this logic will only apply if there are only 2 matching rows.
If there are multiple i cannot get the match.
August 22, 2012 at 12:21 pm
vp7986 (8/22/2012)
this logic will only apply if there are only 2 matching rows.If there are multiple i cannot get the match.
Sorry, my first post was rubbish. Try this:
create table #temp (
NM_ID char(3)
,STRT_DT date
,END_DT date
)
insert #temp
select 'ABD', '01/10/2010', '01/11/2010' union all
select 'ABD', '01/11/2010', '01/12/2011' union all
select 'ABD', '01/12/2011', '01/01/9999' union all
select 'BBD', '01/20/2010', '01/20/2011' union all
select 'BBD', '01/20/2012', '01/01/9999'
select * from #temp
select t1.nm_id, min(t1.strt_dt) strt_dt, max(t2.end_dt) End_dt
from #temp t1
join #temp t2 on t1.End_Dt = t2.Strt_dt
group by t1.nm_id
union select t1.nm_id, t1.strt_dt, t1.end_dt
from #temp t1 where not exists (select 1 from #temp t2 where t2.nm_id = t1.nm_id and (t2.strt_dt = t1.end_dt or t1.strt_dt = t2.end_dt ))
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 22, 2012 at 1:11 pm
Thanks Pro this Is understandable.
I hope this will work in my Actual Example.
Thanks For you Time.
August 22, 2012 at 2:29 pm
Here is a possible alternative. I haven't compared it to Phil's solution.
create table #temp (
NM_ID char(3)
,STRT_DT date
,END_DT date
);
insert #temp
select 'ABD', '01/10/2010', '01/11/2010' union all
select 'ABD', '01/11/2010', '01/12/2011' union all
select 'ABD', '01/12/2011', '01/01/9999' union all
select 'BBD', '01/20/2010', '01/20/2011' union all
select 'BBD', '01/20/2012', '01/01/9999' union all
select 'CBD', '01/10/2010', '01/11/2010' union all
select 'CBD', '01/11/2010', '01/12/2011' union all
select 'CBD', '01/12/2011', '01/01/9999'
go
with BaseData as (
select
t1.NM_ID,
t1.STRT_DT,
t1.END_DT,
row_number() over (order by t1.NM_ID, t1.STRT_DT) rn,
t2.NM_ID as NM_ID2
from
#temp t1
left outer join #temp t2
on (t1.NM_ID = t2.NM_ID and
t1.STRT_DT = t2.END_DT)
), rCTE as (
select
bd1.NM_ID,
bd1.STRT_DT,
bd1.END_DT,
bd1.rn
from
BaseData bd1
where
bd1.NM_ID2 is null
union all
select
bd1.NM_ID,
bd2.STRT_DT,
bd2.END_DT,
bd1.rn
from
rCTE bd1
inner join #temp bd2
on (bd1.NM_ID = bd2.NM_ID and
bd2.STRT_DT = bd1.END_DT)
)
select
NM_ID,
min(STRT_DT) STRT_DT,
max(END_DT) END_DT
from
rCTE
group by
NM_ID,
rn
order by
NM_ID,
STRT_DT;
go
drop table #temp;
go
August 22, 2012 at 2:31 pm
Here is a possible alternative. I haven't compared it to Phil's solution.
Actually, my name is now 'Pro' 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 22, 2012 at 2:38 pm
Phil Parkin (8/22/2012)
Here is a possible alternative. I haven't compared it to Phil's solution.
Actually, my name is now 'Pro' 🙂
oops :blush:
I'll try to remember that in the future. 😉
August 22, 2012 at 3:26 pm
Phil Parkin (8/22/2012)
vp7986 (8/22/2012)
this logic will only apply if there are only 2 matching rows.If there are multiple i cannot get the match.
Sorry, my first post was rubbish. Try this:
create table #temp (NM_ID char(3)
,STRT_DT date
,END_DT date
)
insert #temp
select 'ABD', '01/10/2010', '01/11/2010' union all
select 'ABD', '01/11/2010', '01/12/2011' union all
select 'ABD', '01/12/2011', '01/01/9999' union all
select 'BBD', '01/20/2010', '01/20/2011' union all
select 'BBD', '01/20/2012', '01/01/9999'
select * from #temp
select t1.nm_id, min(t1.strt_dt) strt_dt, max(t2.end_dt) End_dt
from #temp t1
join #temp t2 on t1.End_Dt = t2.Strt_dt
group by t1.nm_id
union select t1.nm_id, t1.strt_dt, t1.end_dt
from #temp t1 where not exists (select 1 from #temp t2 where t2.nm_id = t1.nm_id and (t2.strt_dt = t1.end_dt or t1.strt_dt = t2.end_dt ))
Had a chance to test this a bit. Mine is slower against the same data, has more reads and sorts, but 1 fewer table scan with 3+ a little more times the reads.
Hey, it was interesting to work and an opportunity to try a recursive cte.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply