Rows repeating - 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

General SQL Server Forums

New to SQL Server Programming

Rows repeating

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2010-02-19 : 11:03:11

Hi Guys,

This is probablly really simple, but my query below is duplicating rows:

select c.irn,
convert (varchar, oe.lastevent, 103) as LAST_EVENT,
convert (varchar, ce.eventdate, 103) as EVENT_DATE,
max(ce.cycle)
from cases c
left outer join openaction oe on oe.caseid = c.caseid and oe.lastevent = 163
left outer join caseevent ce on ce.caseid = oe.caseid and ce.eventno = 163
left outer join status s on s.statuscode = c.statuscode and s.liveflag =1
where oe.lastevent = ce.eventno
group by c.irn, oe.lastevent, ce.eventdate

This is because there is more than one event date associated in the caseevent table. I only want to return the date that goes with the maximum cycle no.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 11:08:23

[code]
select columns..
from
(
select row_number() over (partition by c.irn,
dateadd(dd,datediff(dd,0,oe.lastevent),0) order by ce.cycle desc) as seq,
c.irn,
dateadd(dd,datediff(dd,0,oe.lastevent),0) as LAST_EVENT,
dateadd(dd,datediff(dd,0,ce.eventdate),0) as EVENT_DATE,
ce.cycle
from cases c
left outer join openaction oe on oe.caseid = c.caseid and oe.lastevent = 163
left outer join caseevent ce on ce.caseid = oe.caseid and ce.eventno = 163
left outer join status s on s.statuscode = c.statuscode and s.liveflag =1
where oe.lastevent = ce.eventno
)t
where seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/