none
expanding on working query ....

    Question

  • I have this query that will run and give me the results for facility 888... I have about 255 facilities i need to run the same query on......

    This query will select all of the facilities

     

    SELECT Code, Name
    FROM Facilities
    ORDER BY Code
    
    This is the working query that will select a single facility report..

     

     

    DECLARE 
    @haCount int, 
    @oneOrMore int, 
    @twentyFiveOrMore int, 
    @fiftyOrMore int, 
    @seventyFiveOrMore int, 
    @hundredOrMore int, 
    @registered int, 
    @activities int,
    @facilityCode int
    
    
    	select @facilityCode = '888'
    	
    
    
    
    	SELECT @HACount = COUNT(EE.EmployeeId) 
    	FROM XREFEmployeesInvitations EE 
    	JOIn Activities EV ON EE.ActivityId = EV.id 
    	Join Employees E ON EE.employeeid = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	AND IsCompleted = 1 AND EV.SectionId = 1
    
    	DECLARE @results table (id int, credits int, IsSpouse bit)
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	
    	
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1
    
    	SELECT @oneOrMore = @@ROWCOUNT
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25
    
    	SELECT @twentyFiveOrMore = @@rowCount
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
    	SELECT @fiftyOrMore = @@rowCount
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , ISSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75
    
    	SELECT @seventyFiveOrMore = @@rowCount
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100
    
    	SELECT @hundredOrMore = @@rowCount
    
    	SELECT @registered = COUNT(E.ID) 
    	FROM Employees E
    	Join Locations L 
    	ON E.LocationId = L.Id
    	JOIN Facilities F ON L.FacilityID = F.ID
    	WHERE UserId IS NOT NULL AND 
    	F.Code = COALESCE(@facilityCode, F.Code)
    
    	SELECT @registered = @registered + COUNT(E.ID) 
    	FROM Employees E
    	Join Locations L 
    	ON E.LocationId = L.Id
    	JOIN Facilities F ON L.FacilityID = F.ID
    	WHERE SpouseId IS NOT NULL 
    	AND F.Code = COALESCE(@facilityCode, F.Code)
    
    	SELECT @activities = COUNT(Ev.ID) 
    	FROM Activities EV 
    	Join Employees E ON Ev.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code)
    
    	SELECT @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
    	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount
    

     

    I am pretty new to variables in sql

    Thanks in advance!!!

     

    Thursday, December 09, 2010 3:39 PM

Answers

  • Try putting you logic inside a cursor, like this:

     

     

    declare @mycursor cursor, @Code int, @Name varchar(100)
    
    
    
    DECLARE 
    
    @haCount int, 
    
    @oneOrMore int, 
    
    @twentyFiveOrMore int, 
    
    @fiftyOrMore int, 
    
    @seventyFiveOrMore int, 
    
    @hundredOrMore int, 
    
    @registered int, 
    
    @activities int,
    
    @facilityCode int
    
    
    
    set @mycursor=cursor for
    
    SELECT Code, Name
    
    FROM Facilities
    
    ORDER BY Code
    
    
    
    open @mycursor
    
    fetch next from @mycursor into @Code, @Name
    
    while @@fetch_status=0
    
    begin
    
    	select @facilityCode = @Code
    
    
    
    SELECT @HACount = COUNT(EE.EmployeeId) 
    
    	FROM XREFEmployeesInvitations EE 
    
    	JOIn Activities EV ON EE.ActivityId = EV.id 
    
    	Join Employees E ON EE.employeeid = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	AND IsCompleted = 1 AND EV.SectionId = 1
    
    
    
    	DECLARE @results table (id int, credits int, IsSpouse bit)
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	
    
    	
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1
    
    
    
    	SELECT @oneOrMore = @@ROWCOUNT
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25
    
    
    
    	SELECT @twentyFiveOrMore = @@rowCount
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
    
    	SELECT @fiftyOrMore = @@rowCount
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , ISSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75
    
    
    
    	SELECT @seventyFiveOrMore = @@rowCount
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100
    
    
    
    	SELECT @hundredOrMore = @@rowCount
    
    
    
    	SELECT @registered = COUNT(E.ID) 
    
    	FROM Employees E
    
    	Join Locations L 
    
    	ON E.LocationId = L.Id
    
    	JOIN Facilities F ON L.FacilityID = F.ID
    
    	WHERE UserId IS NOT NULL AND 
    
    	F.Code = COALESCE(@facilityCode, F.Code)
    
    
    
    	SELECT @registered = @registered + COUNT(E.ID) 
    
    	FROM Employees E
    
    	Join Locations L 
    
    	ON E.LocationId = L.Id
    
    	JOIN Facilities F ON L.FacilityID = F.ID
    
    	WHERE SpouseId IS NOT NULL 
    
    	AND F.Code = COALESCE(@facilityCode, F.Code)
    
    
    
    	SELECT @activities = COUNT(Ev.ID) 
    
    	FROM Activities EV 
    
    	Join Employees E ON Ev.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code)
    
    
    
    	insert into anotherTable -- create this table at the top
    
    	SELECT @Code, @Name, @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
    
    	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount
    
     
    
    	fetch next from @mycursor into @Code, @Name
    
    end
    
    close @mycursor
    
    deallocate @mycursor
    
    select * from anotherTable

    I haven't tested this... but this should give you an idea.
    The new table will have your all calculated items/fields.

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    • Marked as answer by SBolton Thursday, December 09, 2010 4:44 PM
    Thursday, December 09, 2010 3:58 PM

All replies

  • You must moved all the condition (WHERE F.Code = COALESCE(@facilityCode, F.Code) )

    DECLARE 
    @haCount int, 
    @oneOrMore int, 
    @twentyFiveOrMore int, 
    @fiftyOrMore int, 
    @seventyFiveOrMore int, 
    @hundredOrMore int, 
    @registered int, 
    @activities int,
    @facilityCode int
    select @facilityCode = '888'
    SELECT @HACount = COUNT(EE.EmployeeId) 
    FROM XREFEmployeesInvitations EE 
    JOIn Activities EV ON EE.ActivityId = EV.id 
    Join Employees E ON EE.employeeid = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    Where IsCompleted = 1 AND EV.SectionId = 1
    DECLARE @results table (id int, credits int, IsSpouse bit)
    INSERT INTO @results (id, credits, IsSpouse) 
    SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    FROM vwUserCredits V 
    JOIN Employees E ON V.EmployeeId = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1
    SELECT @oneOrMore = @@ROWCOUNT
    INSERT INTO @results (id, credits, IsSpouse) 
    SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    FROM vwUserCredits V 
    JOIN Employees E ON V.EmployeeId = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25
    SELECT @twentyFiveOrMore = @@rowCount
    INSERT INTO @results (id, credits, IsSpouse) 
    SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    FROM vwUserCredits V 
    JOIN Employees E ON V.EmployeeId = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
    SELECT @fiftyOrMore = @@rowCount
    INSERT INTO @results (id, credits, IsSpouse) 
    SELECT V.EmployeeId, SUM(Credits) , ISSpouse
    FROM vwUserCredits V 
    JOIN Employees E ON V.EmployeeId = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75
    SELECT @seventyFiveOrMore = @@rowCount
    INSERT INTO @results (id, credits, IsSpouse) 
    SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    FROM vwUserCredits V 
    JOIN Employees E ON V.EmployeeId = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100
    SELECT @hundredOrMore = @@rowCount
    SELECT @registered = COUNT(E.ID) 
    FROM Employees E
    Join Locations L 
    ON E.LocationId = L.Id
    JOIN Facilities F ON L.FacilityID = F.ID
    WHERE UserId IS NOT NULL 
    SELECT @registered = @registered + COUNT(E.ID) 
    FROM Employees E
    Join Locations L 
    ON E.LocationId = L.Id
    JOIN Facilities F ON L.FacilityID = F.ID
    WHERE SpouseId IS NOT NULL 
    SELECT @activities = COUNT(Ev.ID) 
    FROM Activities EV 
    Join Employees E ON Ev.EmployeeId = E.ID 
    JOIN Locations L ON E.LocationId = L.ID 
    Join Facilities F ON L.FacilityID = F.Id
    SELECT @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
    @seventyFiveOrMore As SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount
    

    Best regards
    Thursday, December 09, 2010 3:56 PM
  • Try putting you logic inside a cursor, like this:

     

     

    declare @mycursor cursor, @Code int, @Name varchar(100)
    
    
    
    DECLARE 
    
    @haCount int, 
    
    @oneOrMore int, 
    
    @twentyFiveOrMore int, 
    
    @fiftyOrMore int, 
    
    @seventyFiveOrMore int, 
    
    @hundredOrMore int, 
    
    @registered int, 
    
    @activities int,
    
    @facilityCode int
    
    
    
    set @mycursor=cursor for
    
    SELECT Code, Name
    
    FROM Facilities
    
    ORDER BY Code
    
    
    
    open @mycursor
    
    fetch next from @mycursor into @Code, @Name
    
    while @@fetch_status=0
    
    begin
    
    	select @facilityCode = @Code
    
    
    
    SELECT @HACount = COUNT(EE.EmployeeId) 
    
    	FROM XREFEmployeesInvitations EE 
    
    	JOIn Activities EV ON EE.ActivityId = EV.id 
    
    	Join Employees E ON EE.employeeid = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	AND IsCompleted = 1 AND EV.SectionId = 1
    
    
    
    	DECLARE @results table (id int, credits int, IsSpouse bit)
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	
    
    	
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1
    
    
    
    	SELECT @oneOrMore = @@ROWCOUNT
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25
    
    
    
    	SELECT @twentyFiveOrMore = @@rowCount
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
    
    	SELECT @fiftyOrMore = @@rowCount
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , ISSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75
    
    
    
    	SELECT @seventyFiveOrMore = @@rowCount
    
    
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    
    	FROM vwUserCredits V 
    
    	JOIN Employees E ON V.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100
    
    
    
    	SELECT @hundredOrMore = @@rowCount
    
    
    
    	SELECT @registered = COUNT(E.ID) 
    
    	FROM Employees E
    
    	Join Locations L 
    
    	ON E.LocationId = L.Id
    
    	JOIN Facilities F ON L.FacilityID = F.ID
    
    	WHERE UserId IS NOT NULL AND 
    
    	F.Code = COALESCE(@facilityCode, F.Code)
    
    
    
    	SELECT @registered = @registered + COUNT(E.ID) 
    
    	FROM Employees E
    
    	Join Locations L 
    
    	ON E.LocationId = L.Id
    
    	JOIN Facilities F ON L.FacilityID = F.ID
    
    	WHERE SpouseId IS NOT NULL 
    
    	AND F.Code = COALESCE(@facilityCode, F.Code)
    
    
    
    	SELECT @activities = COUNT(Ev.ID) 
    
    	FROM Activities EV 
    
    	Join Employees E ON Ev.EmployeeId = E.ID 
    
    	JOIN Locations L ON E.LocationId = L.ID 
    
    	Join Facilities F ON L.FacilityID = F.Id
    
    	WHERE F.Code = COALESCE(@facilityCode, F.Code)
    
    
    
    	insert into anotherTable -- create this table at the top
    
    	SELECT @Code, @Name, @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
    
    	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount
    
     
    
    	fetch next from @mycursor into @Code, @Name
    
    end
    
    close @mycursor
    
    deallocate @mycursor
    
    select * from anotherTable

    I haven't tested this... but this should give you an idea.
    The new table will have your all calculated items/fields.

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    • Marked as answer by SBolton Thursday, December 09, 2010 4:44 PM
    Thursday, December 09, 2010 3:58 PM
  • Cursors are boggling my mind right now.... I have tried this below and the error is at the very bottom" insert into AllFacilityTable -- create this table at the top"   I have created the table soooo I do not understand yet this concept, can you please provide some more help....

    error

    Invalid object name 'FacilityTable'.

    Thanks!!!

     

    Create table #FacilityTable(
    
    Code int, 
    Name nvarchar (100), 
    haCount int , 
    oneOrMore int, 
    twentyFiveOrMore int, 
    fiftyOrMore int, 
    seventyFiveOrMore int, 
    hundredOrMore int,
    registered int, 
    activities int 
    )
    
    declare @mycursor cursor, @Code int, @Name varchar(100)
    
    DECLARE 
    @haCount int, 
    @oneOrMore int, 
    @twentyFiveOrMore int, 
    @fiftyOrMore int, 
    @seventyFiveOrMore int, 
    @hundredOrMore int, 
    @registered int, 
    @activities int,
    @facilityCode int
    
    set @mycursor=cursor for
    SELECT Code, Name
    FROM Facilities
    ORDER BY Code
    
    open @mycursor
    fetch next from @mycursor into @Code, @Name
    while @@fetch_status=0
    begin
    	select @facilityCode = @Code
    SELECT @HACount = COUNT(EE.EmployeeId) 
    	FROM XREFEmployeesInvitations EE 
    	JOIn Activities EV ON EE.ActivityId = EV.id 
    	Join Employees E ON EE.employeeid = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	AND IsCompleted = 1 AND EV.SectionId = 1
    
    	DECLARE @results table (id int, credits int, IsSpouse bit)
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 1
    
    	SELECT @oneOrMore = @@ROWCOUNT
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 25
    
    	SELECT @twentyFiveOrMore = @@rowCount
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId , IsSpouse HAVING SUM(Credits) >= 50
    	SELECT @fiftyOrMore = @@rowCount
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , ISSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 75
    
    	SELECT @seventyFiveOrMore = @@rowCount
    
    	INSERT INTO @results (id, credits, IsSpouse) 
    	SELECT V.EmployeeId, SUM(Credits) , IsSpouse
    	FROM vwUserCredits V 
    	JOIN Employees E ON V.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code) 
    	GROUP BY V.EmployeeId, IsSpouse HAVING SUM(Credits) >= 100
    
    	SELECT @hundredOrMore = @@rowCount
    
    	SELECT @registered = COUNT(E.ID) 
    	FROM Employees E
    	Join Locations L 
    	ON E.LocationId = L.Id
    	JOIN Facilities F ON L.FacilityID = F.ID
    	WHERE UserId IS NOT NULL AND 
    	F.Code = COALESCE(@facilityCode, F.Code)
    
    	SELECT @registered = @registered + COUNT(E.ID) 
    	FROM Employees E
    	Join Locations L 
    	ON E.LocationId = L.Id
    	JOIN Facilities F ON L.FacilityID = F.ID
    	WHERE SpouseId IS NOT NULL 
    	AND F.Code = COALESCE(@facilityCode, F.Code)
    
    	SELECT @activities = COUNT(Ev.ID) 
    	FROM Activities EV 
    
    	Join Employees E ON Ev.EmployeeId = E.ID 
    	JOIN Locations L ON E.LocationId = L.ID 
    	Join Facilities F ON L.FacilityID = F.Id
    	WHERE F.Code = COALESCE(@facilityCode, F.Code)
    
    	insert into FacilityTable -- create this table at the top
    	SELECT @Code, @Name, @haCount AS HaCount, @oneOrMore As OneOrMore, @twentyFiveOrMore As TwentyFiveOrMore, @fiftyOrMore As FiftyOrMore, 
    	@seventyFiveOrMore As 	SeventyFiveOrMore, @hundredOrMore As HundredOrMore, @registered As Registered, @activities As ActivityCount
    	fetch next from @mycursor into @Code, @Name
    
    end
    close @mycursor
    deallocate @mycursor
    select * from FacilityTable
    

     

    Thursday, December 09, 2010 4:25 PM
  • You have created a temp table #FacilityTable.

    So, use this table inside this cursor, add # sign with the table name.

    insert into #FacilityTable -- create this table at the top

    and again at the end... 
    select * from #FacilityTable

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Thursday, December 09, 2010 4:30 PM
  • manub- your amazing.. i am going to have to look up Cursors this saved me SOOOO much time!!!!

    Thanks again


    Thursday, December 09, 2010 4:44 PM
  • >manub- your amazing.. i am going to have to look up Cursors this saved me SOOOO much time!!!!

    If you do, then please be aware that cursors are a suboptimal solution
    for 99.9% of the database problems. At least in the SQL Server world.
    SQL Server is optimized for set-based processing, not for iterative
    processing (which is what cursors do).
    I did not check the details of this duscussion, so the cursor might be
    the best solution in this case - but in general terms, cursors are
    best avoided.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Friday, December 10, 2010 11:09 PM
  • Yes, Hugo is right.

    We should avoid using CURSORS whenever we can. But as you mentioned that your criteria will run for 255 time, so its not a bid trade-off with cursors. Avoid using cursors if you have a bigger record-set to iterate on where you are doing DMLs. Instead of cursors you could use WHILE loop with temp tables.

    Some useful links:

    http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d13c99f7-0178-47ed-9ffc-34bc27334174

    http://blogs.techrepublic.com.com/datacenter/?p=412

    http://searchsqlserver.techtarget.com/tip/Avoid-cursors-in-SQL-Server-with-these-methods-to-loop-over-records


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Saturday, December 11, 2010 3:35 AM
  • >Instead of cursors you could use WHILE loop with temp tables.

    Ugh! That's even worse!

    If you have to iterate over rows, use a cursor. And do it sensibly,
    that is: with the right options. The default options for cursors suck
    big time. Specify to use a FAST_FORWARD cursor when the total amount
    of data returned by the cursor exceeds the available cache, or a
    STATIC cursor if you do have enough cache to hold the entire
    resultset. I have never seen any T-SQL code beat this for performance
    if row-based iteration is required. (But looping over a SqlDataReader
    in SQLCLR code does beat it hands-down).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, December 12, 2010 11:45 PM
  • Hi Hugo,

    Thanks for the insight on CURSORS.

    It may be true that information on internet is not always right. Early I used to use cursors very often for iterating records but I avoided and decreased on using CURSORS when I came through these kind of articles on net:

    http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/

    http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx

    http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

    ... there are lot more articles on net that suggests to use WHILE loops instead of CURSORS.

    Can you through some light on the use of WHILE loops or I misunderstood them.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, December 13, 2010 3:35 AM
  • Hi manub22,

    It may be true that information on internet is not always right.

    Actually, I'd go a step further and say that the internet is littered
    with ____. Everybody can publish everything, without any checking - so
    you should always verify for yourself everything you read.

    Some comments on the articles you reference:

    http://blog.sqlauthority.com/2008/05/21/sql-server-2005-twelve-tips-for-optimizing-sql-server-2005-query-performance/

    Pinal Dave suggests to avoid cursors, but fails to give you an
    alternative. That's like telling people it's dangerous to run while
    holding scissors without adding that they should walk instead. People
    might misinterpret the advice and proceed to skip while holding
    scissors.

    The suggestion to avoid cursors is good, but should be interpreted as
    a suggestion to avoid any kind of iteration in T-SQL code. SQL Server
    is heavily optimized for set-based processing, so a single set-based
    query almost always runs rings around any kind of iteration. In a test
    I did three years ago, I found that using proper options on a cursor
    can save 50% - 70% of execution time - but replacing the cursor with a
    single set-based query saves almost 95% of execution time when
    compared to the fastest cursor option!

    http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx

    This is a fine example of the nonsense you can easily find on
    internet. The author simply claims that the WHILE loop uses less IO
    then the cursor loop, but obviously never ran the code with STATISTICS
    IO enabled. And he never bothered to check the documentation of the
    DECLARE CURSOR statement, or he would have known that there are four
    base types of cursor in SQL Server, that all operate very differently.

    He uses the default cursor options in his article (which, as I have
    already told you, are awful). And his alternative is also one of the
    worst possible. If you check the code, you'll see that the table
    variable used for the looping has no indexes. That means that you get
    a table scan for every execution of the loop. No problem for 10
    iterations over a 10-row table variable, but I can assure you that the
    execution time for 10 million iterations over a 10-millioin-row table
    variable is brutal.

    The killer is in the final paragraphs, where the author manages to
    produce no less than four incorrect statements about the difference
    between temporary tables and table variables. Nuff said.

    http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

    And another ____-filled article. The author claims that cursors can
    lock the entire table, preventing concurrent updates. Since the four
    base cursor types differ in how concurrent updates are handled, just
    checking the documentation would suffice to see that this is
    incorrect. And it's also easy to run a cursor, then (from a different
    connection) actually TRY to do some updates. Or query
    sys.dm_tran_locks.

    Incidentally, this author makes the EXACT same mistakes in his code as
    the author of the previous article - default options for the cursor,
    and no indexes for the temporary table.

    And this author also includes with an unrelated major mistake. Since
    user-defined functions can't be inlined (except inline table-valued
    functions), using a user-defined function is a known performance
    killer, because it forces the optimizer to create a hidden cursor and
    execute the function once for each row - which is often not required
    if you don't use a user-defined function. The example code in the
    article could be sped up enormously by using a single set-based query
    with a CASE expression to find the discount percentage.

    You had a bunch of links in your previous reply as well - I'm sure if
    you read those articles critically, you'll find issues similar to the
    ones described above.

    And now I urge you to fire up your test server, create some test
    tables and test all the various alternatives for yourself. For, as I
    already wrote at the start of my reply, everybody can publish
    everything, without any checking - so you should always verify for
    yourself everything you read.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Monday, December 13, 2010 10:24 AM
  • Hey Hugo! you are an eye opener.

     

    People have this misconception of not using CURORS with correct options, providing ineffective alternates and believing everything whats on internet. And sure one (everyone) needs to check on its own by getting one step ahead before believing whats on internet.

     

    I really love this forum, lot more to learn from you guys!!!


    Thanks again for the detailed clarification.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Monday, December 13, 2010 12:20 PM