How do I alter the below code to pull specific activities that have been completed... This query is implimented in SSRS where the facility number (code) is selected and then the activity is selected from radio boxes, and then the results will be how many Registered / who have Completed the activity..... The current query pulls every registered and completed b/c there is no WHERE, but when I insert WHERE's i get no results!The current query returns the title of the activity, registered of 589716 and completed of 25113 . This returns ALL registered and completed. But the code i am trying to write needs to be able to understand what activity and facility i have selected and pull data only from those criteria. So the results will be specific to the people that have completed that activity within the facility.thx in advance this has been a painDECLARE @registered int, @completed int, @title nvarchar(50),@ActivityId int,@FacilityCode varchar SET @FacilityCode = '102'SET @ActivityId= '10'SELECT @registered = COUNT(*) FROM XREFEmployeesInvitations EE JOIN Employees E ON EE.EmployeeId = E.IDJOIN Activities EV ON EE.ActivityId = Ev.IdJOIN Locations L ON E.LocationId = L.IdJoin Facilities F ON F.Id = L.FacilityId /*WHERE EV.Id = @activityIdAND F.Code = @FacilityNumber*/??????SELECT @completed = COUNT(*) FROM XREFEmployeesInvitations EE JOIN Employees E ON EE.employeeId = E.IDJOIN Activities EV ON EE.ActivityId = Ev.IdJOIN Locations L ON E.LocationId = L.IdJoin Facilities F ON F.Id = L.FacilityId AND IsCompleted = 1SELECT @title = Title FROM Activities WHERE ID = @ActivityIdSELECT @title As Title, @registered AS Registered, @completed As Completed
T-sql problem
Posted - 2010-03-22 : 10:59:29
Glad tht you spotted it out------------------------------------------------------------------------------------------------------SQL Server MVP