to check if dates are the same in a group by varia

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)

to check if dates are the same in a group by varia

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-17 : 11:15:27

create table #TimeTemp(
PR int,
startDate datetime,
endDate datetime
)

insert into #TimeTemp(PR , startDate, endDate)
select 1, '2011-01-01', '2011-01-05' union all
select 2, '2011-01-05', '2011-02-04' union all
select 2, '2011-02-05', '2011-02-09' union all
select 2, '2011-03-05', '2011-02-09' union all
select 3, '2011-01-05', '2011-02-09' union all
select 3, '2011-02-05', '2011-02-10' union all
select 3, '2011-01-05', '2011-02-10' union all
select 4, '2011-01-10', '2011-01-15' union all
select 5, '2011-01-04', '2011-01-15' union all
select 6, '2011-01-16', '2011-01-31' union all
select 7, '2011-01-02', '2011-01-31' union all
select 8, '2011-02-02', '2011-02-15' union all
select 9, '2011-01-15', '2011-01-31' union all
select 10, '2011-02-15', '2011-02-21';

hi

in the above table i want to retrieve records where the enddate is the same within the PR variable. Does not matter what the startdate is. PR 2 has two records with same enddate and PR 3 has two records too.

So i want to retrieve 2 and 3, how can these be written in a function

Appreciate your help

THanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 13:13:25

Would something like this work for you?


SELECT 
   t1.* 
FROM 
   #TimeTemp t1
   INNER JOIN
   (
      SELECT pr,endDate FROM #TimeTemp GROUP BY pr,EndDate HAVING COUNT(*) > 1
   ) t2 ON t1.pr = t2.pr AND t1.endDate = t2.endDate;

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-17 : 14:14:09

Thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources