ORDER

ALTER PROCEDURE PocetJizd
AS
BEGIN
DECLARE kurzor CURSOR FOR SELECT S.idStaff, S.fname, S.lname, COUNT(S.idStaff) AS Pocet FROM Staff AS S
                           JOIN TrainCrew AS Tr ON (S.idStaff = Tr.idStaff) GROUP BY S.idStaff, S.fname, S.lname ORDER BY Pocet DESC;
DECLARE
    @v_idStaff INT, @v_fname VARCHAR(30), @v_lname VARCHAR(50), @v_count INT, @v_max INT, @v_topWorkerId INT;
    SET @v_max = 0;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_idStaff, @v_fname, @v_lname, @v_count;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@v_count > @v_max)
                BEGIN
                    SET @v_max = @v_count;
                    SET @v_topWorkerId = @v_idStaff;
                END
            PRINT @v_fname + ' ' + @v_lname + ' ' + CAST(@v_count AS VARCHAR);
        FETCH NEXT FROM kurzor INTO @v_idStaff, @v_fname, @v_lname, @v_count;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
DECLARE tKurzor CURSOR FOR SELECT T.idTrain FROM Staff AS S JOIN TrainCrew AS Tr ON (S.idStaff = Tr.idStaff)
                           JOIN TrainRide AS T ON (Tr.idTrainRide = T.idTrainRide) WHERE Tr.idStaff = @v_topWorkerId;
DECLARE
    @v_idTrain INT, @v_name VARCHAR(30);
    SET @v_name = (SELECT fname FROM Staff WHERE idStaff = @v_topWorkerId);
OPEN tKurzor;
    FETCH NEXT FROM tKurzor INTO @v_idTrain;
    PRINT 'TOP EMPLOYER: ' + @v_name;
    PRINT 'RIDES:';
    WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'TRAIN WITH ID: ' + CAST(@v_idTrain AS VARCHAR);
        FETCH NEXT FROM tKurzor INTO @v_idTrain;
        END
CLOSE tKurzor;
DEALLOCATE tKurzor;
END

EXECUTE PocetJizd;