-- C) Vnorene kurzory (2 tabulky)
-- 1) Vyberte pre seminar z tabulky Udalosti vhodnu miestnost z tabulky Izby.
-- Pozri: Vyskusaj postupne:
--    Imbedded cursors (two tables)
--    Choose for every seminar from table Udalosti suitable hall from table Izby.
--    See: Try successive:
--
--    Izby: idIz, pocStoliciek = Halls: idIz, countChair 
--    Udalosti: idUd, popisUd, pocUcastnikov = Programs: idUd, descriptionProgram, countAttendants
--
-- 2) Zistite aj pocet volnych stoliciek.
--    Compute also the count of free chairs

USE tempdb;
GO
SET NOCOUNT ON

IF OBJECT_ID('dbo.Udalosti') IS NOT NULL DROP TABLE dbo.Udalosti;
GO
IF OBJECT_ID('dbo.Izby') IS NOT NULL DROP TABLE dbo.Izby;
GO

CREATE TABLE dbo.Izby
(
  idIz VARCHAR(10) NOT NULL PRIMARY KEY,
  pocStoliciek INT NOT NULL
);
INSERT INTO dbo.Izby(idIz, pocStoliciek) VALUES('A1', 55);
INSERT  dbo.Izby VALUES('A2',  55);
INSERT  dbo.Izby VALUES('B2', 110);
INSERT  dbo.Izby VALUES('B1', 110);
INSERT  dbo.Izby VALUES('B3', 600);
INSERT  dbo.Izby VALUES('C1',1010);
INSERT  dbo.Izby VALUES('C2',1500);

-- Vyskusaj postupne:
--DELETE FROM dbo.Izby WHERE pocStoliciek > 1100;
--DELETE FROM dbo.Izby WHERE pocStoliciek > 110; -- kvoli BREAK

CREATE TABLE dbo.Udalosti
(
  idUd INT NOT NULL PRIMARY KEY,
  popisUd VARCHAR(25) NOT NULL,
  pocUcastnikov INT NOT NULL
);
INSERT dbo.Udalosti(idUd, popisUd, pocUcastnikov) 
					VALUES(1, 'Seminar 1', 203);
INSERT dbo.Udalosti VALUES(2, 'Seminar 2',  46);
INSERT dbo.Udalosti VALUES(3, 'Seminar 3', 200);
INSERT dbo.Udalosti VALUES(4, 'Seminar 4',  98);
INSERT dbo.Udalosti VALUES(5, 'Seminar 5', 890);
INSERT dbo.Udalosti VALUES(6, 'Seminar 6',  46);
GO

DECLARE
  @idIz AS VARCHAR(10), @pocStoliciek AS INT,
  @idUd AS INT, @pocUcastnikov AS INT;

DECLARE @Result TABLE(idIz  VARCHAR(10), idUd INT);
--odkomentuj1 DECLARE  @volnych INT;
--odkomentuj2 DECLARE @Result TABLE(idIz  VARCHAR(10), idUd INT, volStol INT);

-- 2 kurzory:
DECLARE curIz CURSOR FAST_FORWARD FOR
  SELECT idIz, pocStoliciek FROM dbo.Izby
  ORDER BY pocStoliciek, idIz;
DECLARE curUd CURSOR FAST_FORWARD FOR
  SELECT idUd, pocUcastnikov FROM dbo.Udalosti
  ORDER BY pocUcastnikov, idUd;

OPEN curIz;
OPEN curUd;

FETCH NEXT FROM curUd INTO @idUd, @pocUcastnikov;
WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM curIz INTO @idIz, @pocStoliciek;
  WHILE @@FETCH_STATUS = 0 AND @pocStoliciek < @pocUcastnikov
    FETCH NEXT FROM curIz INTO @idIz, @pocStoliciek;
--odkomentuj3    SET @volnych = @pocStoliciek - @pocUcastnikov;

  IF @@FETCH_STATUS = 0
    INSERT INTO @Result(idIz, idUd) VALUES(@idIz, @idUd);
--odkomentuj4    INSERT INTO @Result(idIz, idUd, volStol) VALUES(@idIz, @idUd, @volnych);
  ELSE
  BEGIN
    RAISERROR('Malo izieb pre udalosti.', 16, 1); 
    INSERT INTO @Result(idIz, idUd) VALUES('--', @idUd);
    BREAK;
  END

  FETCH NEXT FROM curUd INTO @idUd, @pocUcastnikov;
END

CLOSE curIz;
CLOSE curUd;

DEALLOCATE curIz;
DEALLOCATE curUd;

SELECT * FROM @Result;
GO

-- SELECT * FROM Udalosti;
-- SELECT * FROM Izby;
GO