Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Query Help Please
Message
General information
Forum:
Microsoft SQL Server
Category:
Other
Environment versions
SQL Server:
SQL Server 2000
Miscellaneous
Thread ID:
01312887
Message ID:
01313013
Views:
13
Naomi, thank you for the link, I was able to use that information and with tweaks get what I needed. Below was my final query
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)
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform