SELECT distinct top 100 percent convert(varchar(10),m1.BeginTime,101) as CaseDate, m1.meetingnumber, co1.InvID, co1.PlanQty as PlannedQty, co1.HaveAvailQty, PrimeDesc, LocalDesc, ImmsField, CatalogNo FROM dbo.meetings m1 inner join dbo.meetproc mp1 on m1.meetingnumber = mp1.meetingnumber inner join dbo.coSuper2 co1 on mp1.CardAssigned = co1.coSuperID inner join dbo.coInv on co1.InvID = dbo.coInv.InvID WHERE (m1.iscancelled = 0) AND (m1.iscompleted = 0) and m1.RoomClosed=0 and m1.isblock=0 and m1.begintime between @ttStartDate and @ttEndDate and m1.DeptID=@tiDeptID and mp1.CardAssigned>0 and co1.isactive=1 and (co1.planQty + co1.HaveAvailQty) = (select max(co2.PlanQty+co2.HaveAvailQty) as MaxNumber FROM dbo.meetings m2 inner join dbo.meetproc mp2 on m2.meetingnumber = mp2.meetingnumber inner join dbo.coSuper2 co2 on mp2.CardAssigned = co2.coSuperID WHERE (m2.iscancelled = 0) AND (m2.iscompleted = 0) and m2.meetingnumber=m1.meetingnumber and co2.invID=co1.InvID and co2.isactive=1)