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.
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 ))
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'
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy