>meetingnumber CardAssigned InvID PlanQty HaveAvailQty >------------- ------------ ----------- ----------- ------------ >27338 220 230 1 1 >27338 224 230 1 1 >27338 224 670 1 1 >27338 220 670 1 1 >27338 224 977 1 0 >27338 220 977 1 0 >27338 220 1013 5 5 >27338 224 1013 5 5 >27338 224 3038 2 2 >27338 220 3038 2 2 >========= >* This next 2 records are an example where the same item has a variance >* on the PlanQty, and I only want to get the record with the highest >* PlanQty >========= >27338 224 3677 1 0 >27338 220 3677 0 1 >>
>meetingnumber CardAssigned InvID PlanQty HaveAvailQty >------------- ------------ ----------- ----------- ------------ >27338 220 230 1 1 >27338 220 670 1 1 >27338 220 977 1 0 >27338 220 1013 5 5 >27338 220 3038 2 2 >27338 224 3677 1 0 >>
>SELECT dbo.meetings.meetingnumber, > dbo.meetproc.CardAssigned, > dbo.coSuper2.InvID, > dbo.coSuper2.PlanQty, > dbo.coSuper2.HaveAvailQty >FROM dbo.meetings inner join > dbo.meetproc on dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber inner join > dbo.coSuper2 on dbo.meetproc.CardAssigned = dbo.coSuper2.coSuperID >WHERE (dbo.meetings.iscancelled = 0) AND (dbo.meetings.iscompleted = 0) >and dbo.meetings.RoomClosed=0 >and dbo.meetings.isblock=0 >and dbo.meetings.begintime between @ttStartDate and @ttEndDate >and dbo.meetings.DeptID=@tiDeptID >and dbo.meetproc.CardAssigned>0 >and dbo.coSuper2.isactive=1 >order by dbo.meetings.meetingnumber,InvID >>
SELECT dbo.meetings.meetingnumber, > dbo.meetproc.CardAssigned, > dbo.coSuper2.InvID, > dbo.coSuper2.PlanQty, > dbo.coSuper2.HaveAvailQty >FROM dbo.meetings inner join > dbo.meetproc on dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber inner join > dbo.coSuper2 on dbo.meetproc.CardAssigned = dbo.coSuper2.coSuperID >WHERE (dbo.meetings.iscancelled = 0) AND (dbo.meetings.iscompleted = 0) >and dbo.meetings.RoomClosed=0 >and dbo.meetings.isblock=0 >and dbo.meetings.begintime between @ttStartDate and @ttEndDate >and dbo.meetings.DeptID=@tiDeptID >and dbo.meetproc.CardAssigned>0 >and dbo.coSuper2.isactive=1 inner join (SELECT dbo.meetings.meetingnumber, > dbo.meetproc.CardAssigned, > dbo.coSuper2.InvID, > max(dbo.coSuper2.PlanQty) as MaxNumber, > FROM dbo.meetings inner join > dbo.meetproc on dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber inner join > dbo.coSuper2 on dbo.meetproc.CardAssigned = dbo.coSuper2.coSuperID >WHERE (dbo.meetings.iscancelled = 0) AND (dbo.meetings.iscompleted = 0) >and dbo.meetings.RoomClosed=0 >and dbo.meetings.isblock=0 >and dbo.meetings.begintime between @ttStartDate and @ttEndDate >and dbo.meetings.DeptID=@tiDeptID >and dbo.meetproc.CardAssigned>0 >and dbo.coSuper2.isactive=1 group by 1,2,3) ) InnerSQL on dbo.Meetings.MeetingNumber = InnerSQL.MeetingNumber and dbo.meetproc.CardAssigned = InnerSQL.CardAssigned AND dbo.coSuper2.InvID = InnerSQL.InvID AND dbo.coSuper2.PlanQty = InnerSQL.MaxNumber order by dbo.meetings.meetingnumber,dbo.coSuper2.InvID