DECLARE @SKOLNI_ROK_ID VARCHAR(1000); SET @SKOLNI_ROK_ID = '2012';
DECLARE @ORGANIZACE_ID VARCHAR(1000); SET @ORGANIZACE_ID = '12345';

DECLARE @Vysvedceni TABLE (
                ORGANIZACE_ID VARCHAR(255),
                OSOBA_ID VARCHAR(255),
                VYSVEDCENI_ID VARCHAR(255)
);

INSERT INTO @Vysvedceni VALUES ('12345', '215560', '1436')
INSERT INTO @Vysvedceni VALUES ('12345', '283073', '2749')
INSERT INTO @Vysvedceni VALUES ('12345', '215726', '488')
INSERT INTO @Vysvedceni VALUES ('12345', '215590', '494')
INSERT INTO @Vysvedceni VALUES ('12345', '215338', '497')
INSERT INTO @Vysvedceni VALUES ('12345', '215346', '498')
INSERT INTO @Vysvedceni VALUES ('12345', '240583', '499')
INSERT INTO @Vysvedceni VALUES ('12345', '215316', '500')
INSERT INTO @Vysvedceni VALUES ('12345', '215394', '501')
INSERT INTO @Vysvedceni VALUES ('12345', '215740', '502')
INSERT INTO @Vysvedceni VALUES ('12345', '215646', '503')
INSERT INTO @Vysvedceni VALUES ('12345', '215700', '504')
INSERT INTO @Vysvedceni VALUES ('12345', '215646', '815')
INSERT INTO @Vysvedceni VALUES ('12345', '283085', 'A2754')
INSERT INTO @Vysvedceni VALUES ('12345', 'D507861', 'D12185')
INSERT INTO @Vysvedceni VALUES ('12345', 'D507873', 'D12338')
INSERT INTO @Vysvedceni VALUES ('12345', 'D507965', 'D12378')
INSERT INTO @Vysvedceni VALUES ('12345', 'D507921', 'D12410')
INSERT INTO @Vysvedceni VALUES ('12345', 'D487681', 'D12465')
INSERT INTO @Vysvedceni VALUES ('12345', 'D507911', 'D12559')
INSERT INTO @Vysvedceni VALUES ('12345', 'D527122', 'D17431')
INSERT INTO @Vysvedceni VALUES ('12345', '283085', 'D2756')
INSERT INTO @Vysvedceni VALUES ('12345', '215356', 'D2757')
INSERT INTO @Vysvedceni VALUES ('12345', '215346', 'D2758')
INSERT INTO @Vysvedceni VALUES ('12345', '215348', 'D2759')
INSERT INTO @Vysvedceni VALUES ('12345', '215342', 'D2760')
INSERT INTO @Vysvedceni VALUES ('12345', '215360', 'D2761')
INSERT INTO @Vysvedceni VALUES ('12345', '215352', 'D2762')
INSERT INTO @Vysvedceni VALUES ('12345', '215324', 'D2763')
INSERT INTO @Vysvedceni VALUES ('12345', '215362', 'D2764')
INSERT INTO @Vysvedceni VALUES ('12345', '283016', 'D2765')
INSERT INTO @Vysvedceni VALUES ('12345', '283012', 'D2766')
INSERT INTO @Vysvedceni VALUES ('12345', '283040', 'D2767')
INSERT INTO @Vysvedceni VALUES ('12345', '282999', 'D2768')
INSERT INTO @Vysvedceni VALUES ('12345', '215474', 'D2769')
INSERT INTO @Vysvedceni VALUES ('12345', '215482', 'D2770')
INSERT INTO @Vysvedceni VALUES ('12345', '215504', 'D2771')
INSERT INTO @Vysvedceni VALUES ('12345', '215588', 'D2772')
INSERT INTO @Vysvedceni VALUES ('12345', '215596', 'D2773')
INSERT INTO @Vysvedceni VALUES ('12345', '283034', 'D2789')
INSERT INTO @Vysvedceni VALUES ('12345', '215356', 'D2797')
INSERT INTO @Vysvedceni VALUES ('12345', '283112', 'D2832')
INSERT INTO @Vysvedceni VALUES ('12345', 'A467854', 'D2888')
INSERT INTO @Vysvedceni VALUES ('12345', '199498', 'D3961')
INSERT INTO @Vysvedceni VALUES ('12345', '465608', 'D3971')
INSERT INTO @Vysvedceni VALUES ('12345', '466273', 'D4028')
INSERT INTO @Vysvedceni VALUES ('12345', '215496', 'D5452')
INSERT INTO @Vysvedceni VALUES ('12345', '466552', 'D5453')
INSERT INTO @Vysvedceni VALUES ('12345', 'D483235', 'D5463')
INSERT INTO @Vysvedceni VALUES ('12345', 'D487481', 'D6736')
INSERT INTO @Vysvedceni VALUES ('12345', 'A467365', 'D6813')
INSERT INTO @Vysvedceni VALUES ('12345', '215556', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215558', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215560', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215562', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215564', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215566', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215568', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215570', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215572', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215574', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215576', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215578', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215580', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215582', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215584', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215586', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215588', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215590', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215592', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215594', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215596', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215598', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215600', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215602', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215604', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215606', '124')
INSERT INTO @Vysvedceni VALUES ('12345', '215608', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215610', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215612', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215614', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215616', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215618', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215620', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215622', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215624', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215626', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215628', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215630', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215632', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215634', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215636', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215638', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215640', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215642', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215644', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215646', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215648', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215650', '125')
INSERT INTO @Vysvedceni VALUES ('12345', '215652', '125')

SELECT (pr.PRIZNAK_DRUH_PREDMETU) AS [DruhPredmetu],
(vysv.OBDOBI_ID_R) AS [OBDOBI_ID_R],
(vysv.ORGANIZACE_ID) AS [ORGANIZACE_ID],
(vysv.OSOBA_ID) AS [OSOBA_ID],
(pr.PORADI_NA_VYSVEDCENI) AS [PoradiPredmetuNaVysvedceni],
(pr.REALIZACE_ID) AS [REALIZACE_ID],
(vysv.VYSVEDCENI_ID) AS [VYSVEDCENI_ID]
FROM (
SELECT
  os.ORGANIZACE_ID,
  os.OSOBA_ID,
  vysv.OBDOBI_ID_R,
  vysv.OBDOBI_ID_P,
  vysv.VYSVEDCENI_ID,
  vysv.SKUPINA_ID,
  vysv.SYMBOL_PROSKRTNUTI,
  vysv.DATUM_VYSV_1_POL,
  vysv.DATUM_VYSV_2_POL
FROM CCAK_VYSVEDCENI vysv
INNER JOIN CCAK_OSOBA os
  ON vysv.ORGANIZACE_ID = os.ORGANIZACE_ID
  AND vysv.OSOBA_ID = os.OSOBA_ID
WHERE vysv.OSOBA_ID IS NOT NULL
 UNION ALL
 SELECT DISTINCT
  ovs.ORGANIZACE_ID,
  ovs.OSOBA_ID,
  vysv.OBDOBI_ID_R,
  vysv.OBDOBI_ID_P,
  vysv.VYSVEDCENI_ID,
  vysv.SKUPINA_ID,
  vysv.SYMBOL_PROSKRTNUTI,
  vysv.DATUM_VYSV_1_POL,
  vysv.DATUM_VYSV_2_POL
FROM CCAK_VYSVEDCENI vysv
INNER JOIN CCAK_OSOBY_VE_SKUPINE ovs
  ON vysv.ORGANIZACE_ID = ovs.ORGANIZACE_ID
  AND vysv.SKUPINA_ID = ovs.SKUPINA_ID
  AND vysv.OBDOBI_ID_R = ovs.OBDOBI_ID
INNER JOIN CCAK_STUDIJNI_SKUPINA trida
  ON ovs.ORGANIZACE_ID = trida.ORGANIZACE_ID
  AND ovs.OBDOBI_ID = trida.OBDOBI_ID
  AND ovs.SKUPINA_ID = trida.SKUPINA_ID
INNER JOIN CCAK_CIS_UIV ukon
  ON ovs.DUVOD_ID = ukon.UIV_ID
WHERE vysv.OSOBA_ID IS NULL
AND trida.PRIZNAK_DRUH_SKUPINY = 'T'
AND ukon.KOD IN ('1', '2', '3', '4', '5')
AND ukon.UIV_CIS_ID = 'RAPV'
) vysv
CROSS APPLY dbo.CCAK_VYSV_PredmetyStudenta(vysv.ORGANIZACE_ID, vysv.OBDOBI_ID_R, vysv.OBDOBI_ID_P, vysv.SKUPINA_ID, vysv.OSOBA_ID, vysv.VYSVEDCENI_ID) pr
INNER JOIN @Vysvedceni AS Vysvedceni_4bbc30e1a4824eedb0c8b6e218d3d304 ON vysv.ORGANIZACE_ID = Vysvedceni_4bbc30e1a4824eedb0c8b6e218d3d304.ORGANIZACE_ID AND vysv.OSOBA_ID = Vysvedceni_4bbc30e1a4824eedb0c8b6e218d3d304.OSOBA_ID AND vysv.VYSVEDCENI_ID = Vysvedceni_4bbc30e1a4824eedb0c8b6e218d3d304.VYSVEDCENI_ID
WHERE ((vysv.OBDOBI_ID_R) = (@SKOLNI_ROK_ID))
AND ((vysv.ORGANIZACE_ID) = (@ORGANIZACE_ID))