Almost there, little help.... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Almost there, little help....

Author

Topic

Trent
Starting Member

39 Posts

Posted - 2004-09-10 : 08:50:12

I got as far as the code below:
*******************************
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
*************************************************
That outputs this:
GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available
-----------------------------------------------------------------
859 |2004-2-24 |Building A |15 |11
860 |2004-2-24 |Building A |10 |6
861 |2004-5-04 |Building A |10 |NULL
****************************************************
The @@Rowcount needs to be specific to the GroupID like below:
GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available
-----------------------------------------------------------------
859 |2004-2-24 |Building A |15 |12
860 |2004-2-24 |Building A |10 |9
861 |2004-5-04 |Building A |10 |NULL
*******************************************************
The difference between the two is that there are three people who are associated with GroupID 859 and one with 860 and zero with 861. Right now it's subtracting them all from 859 and 860.

Thank you for any help!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 08:55:54

show some sample data (before any queries) and the desired output... it is much easier to work with that way

My initial guess is don't join the filled spots

Select
groupId,
...
Available = NotToExceed - (Select count(*) From @filled where groupId = A.groupId)
From tGroup
...

Corey

Trent
Starting Member

39 Posts

Posted - 2004-09-10 : 09:04:49

Wow, that was quick, and it works-
Thank you very much Corey!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources