tSQL query
-
I have this code below, but I want the "Available" column to be the @@Rowcount specific to the GroupID. Right now with what I figured below, it subtracts the rowcount from both GroupID 859 and 860. CREATE PROCEDURE usp_Groups @StudyNo char(16) AS declare @Fill table (GroupID varchar(5), TesterID char(7)) declare @Ct table (GroupID varchar(5), TesterID char(7), Filled varchar(4)) declare @Out table (GroupID varchar(5), TesterID char(7), Filled varchar(4)) insert @Fill SELECT DISTINCT tGroup.GroupID, tResultQue.TesterID FROM tGroup INNER JOIN tResultQue ON tGroup.GroupID = tResultQue.GroupID where (tResultQue.QueStatusID >= '20') and (tResultQue.QueStatusID <= '22') and tGroup.StudyNo = @StudyNo insert @Ct select distinct F.GroupID, F.TesterID, @@Rowcount as Filled from @Fill F Group by F.GroupID, F.TesterID insert @Out select distinct F.GroupID, F.TesterID, Ct.Filled from @Fill F left join @Ct Ct on (F.GroupID = Ct.GroupID) SELECT distinct tGroup.GroupID, tGroup.ScheduleStamp, tFacility.FacilitiesDescription, tGroup.NotToExceed, (tGroup.NotToExceed- Gonzo.Filled) as Available FROM tGroup INNER JOIN tFacility ON tGroup.FacilityID = tFacility.FacilityID left join @Out Gonzo on (tGroup.GroupID = Gonzo.GroupID) left join @Fill J on (tGroup.GroupID = J.GroupID) left join @Ct Cet on (tGroup.GroupID = Cet.GroupID) where tGroup.StudyNo = @StudyNo GO ************************************************* This is what the output looks like: GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available ----------------------------------------------------------------- 859|2004-02-24|Building A|15|11 860|2004-02-24|Building A|10|6 861|2004-05-04|Building A|10|NULL *************************************************** This is what it should look like: GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available ----------------------------------------------------------------- 859|2004-02-24|Building A|15|12 860|2004-02-24|Building A|10|9 861|2004-05-04|Building A|10|NULL Thank You!
-
I have this code below, but I want the "Available" column to be the @@Rowcount specific to the GroupID. Right now with what I figured below, it subtracts the rowcount from both GroupID 859 and 860. CREATE PROCEDURE usp_Groups @StudyNo char(16) AS declare @Fill table (GroupID varchar(5), TesterID char(7)) declare @Ct table (GroupID varchar(5), TesterID char(7), Filled varchar(4)) declare @Out table (GroupID varchar(5), TesterID char(7), Filled varchar(4)) insert @Fill SELECT DISTINCT tGroup.GroupID, tResultQue.TesterID FROM tGroup INNER JOIN tResultQue ON tGroup.GroupID = tResultQue.GroupID where (tResultQue.QueStatusID >= '20') and (tResultQue.QueStatusID <= '22') and tGroup.StudyNo = @StudyNo insert @Ct select distinct F.GroupID, F.TesterID, @@Rowcount as Filled from @Fill F Group by F.GroupID, F.TesterID insert @Out select distinct F.GroupID, F.TesterID, Ct.Filled from @Fill F left join @Ct Ct on (F.GroupID = Ct.GroupID) SELECT distinct tGroup.GroupID, tGroup.ScheduleStamp, tFacility.FacilitiesDescription, tGroup.NotToExceed, (tGroup.NotToExceed- Gonzo.Filled) as Available FROM tGroup INNER JOIN tFacility ON tGroup.FacilityID = tFacility.FacilityID left join @Out Gonzo on (tGroup.GroupID = Gonzo.GroupID) left join @Fill J on (tGroup.GroupID = J.GroupID) left join @Ct Cet on (tGroup.GroupID = Cet.GroupID) where tGroup.StudyNo = @StudyNo GO ************************************************* This is what the output looks like: GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available ----------------------------------------------------------------- 859|2004-02-24|Building A|15|11 860|2004-02-24|Building A|10|6 861|2004-05-04|Building A|10|NULL *************************************************** This is what it should look like: GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available ----------------------------------------------------------------- 859|2004-02-24|Building A|15|12 860|2004-02-24|Building A|10|9 861|2004-05-04|Building A|10|NULL Thank You!