original and Duplicate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

original and Duplicate

Author

Topic

pamit_1982
Starting Member

13 Posts

Posted - 2007-06-07 : 09:27:35

no recddate cmpdate name
1 2006-3-2 2006-5-12 xxx
2 2006-8-30 2006-10-30 xxx
3 2006-02-16 2006-3-23 yyy
4 2006-11-13 2007-01-05 yyy

Hi The above is an eg of what my prof has given to solve me .
There is 1 original data and 1 duplicate data .
I need to highlight data for case where recd data for duplicate >= 6 months for cmpdate of original.
The criteria for original and duplicate is that recddate for original < recddate of duplicate .

the correct solution will be

no recddate cmpdate name
1 2006-02-16 2006-3-23 yyy
2 2006-11-13 2007-01-05 yyy

Please guys a tough nut for me..

pamit_1982
Starting Member

13 Posts

Posted - 2007-06-07 : 09:40:32

Please yaar i need your help..

pamit_1982
Starting Member

13 Posts

Posted - 2007-06-07 : 10:50:02

NY help guys..

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-08 : 02:33:44

declare @t table (no int, recddate datetime, cmpdate datetime, name varchar(20))
insert @t
select 1, '2006-3-2', '2006-5-12', 'xxx' union all
select 2, '2006-8-30', '2006-10-30','xxx' union all
select 3, '2006-02-16', '2006-3-23', 'yyy' union all
select 4, '2006-11-13', '2007-01-05', 'yyy'

Select z.* from @t z join
(
Select * from
(Select name as 'o_name', recddate as 'o_recddate', cmpdate as 'o_cmpdate'
from @t
where recddate in
(Select min(recddate)
from @t
group by name)) as a join

(Select name as 'd_name',recddate as 'd_recddate', cmpdate as 'd_cmpdate'
from @t
where recddate not in
(Select min(recddate)
from @t
group by name)) as b
on a.o_name = b.d_name
and abs(datediff(month, b.d_recddate, a.o_cmpdate)) > = 6) y on z.name = y.o_name

--------------------------------------------------
S.Ahamed

pamit_1982
Starting Member

13 Posts

Posted - 2007-06-14 : 04:02:47

Thanks Ahmad.
though i am late..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources