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 2008 Forums

Transact-SQL (2008)

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:34:16

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

can it be done without using Rank or row over functions

Appreciate your help

THanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-17 : 11:44:39

select t1.*
from tbl t1
join (select pr, enddate
from tbl
group by pr, enddate
having COUNT(*) > 1
) a
on a.pr = t1.pr
and a.enddate = t1.enddate
order by t1.pr, t1.enddate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-05-17 : 14:14:25

Thank you!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-17 : 14:18:31

Dupe: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160704

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources