99 lines
3.0 KiB
Transact-SQL
99 lines
3.0 KiB
Transact-SQL
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_GetRiportTanuloKorfa] Script Date: 2016.09.07. 15:37:13 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[DBO].[sp_GetRiportTantargyiOktatok]') IS NOT NULL BEGIN
|
|
DROP PROCEDURE [DBO].[sp_GetRiportTantargyiOktatok]
|
|
END
|
|
GO
|
|
|
|
create PROCEDURE [sp_GetRiportTantargyiOktatok]
|
|
@fenntartoId int,
|
|
@intezmenyId int,
|
|
@targykategoriaId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
DECLARE @intezmenyTable table (
|
|
id int primary key,
|
|
tanevId int
|
|
)
|
|
|
|
IF @fenntartoId IS NOT NULL AND @intezmenyId IS NOT NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.C_FENNTARTOAZONOSITO = @fenntartoId
|
|
AND i.ID = @intezmenyId
|
|
AND i.TOROLT = 'F'
|
|
END
|
|
|
|
IF @fenntartoId IS NOT NULL AND @intezmenyId IS NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.C_FENNTARTOAZONOSITO = @fenntartoId
|
|
AND i.TOROLT = 'F'
|
|
END
|
|
|
|
IF @fenntartoId IS NULL AND @intezmenyId IS NOT NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.ID = @intezmenyId
|
|
AND i.TOROLT = 'F'
|
|
END
|
|
|
|
IF @fenntartoId IS NULL AND @intezmenyId IS NULL BEGIN
|
|
INSERT INTO @intezmenyTable (id)
|
|
SELECT i.ID FROM T_INTEZMENY i
|
|
WHERE i.TOROLT = 'F'
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM @intezmenyTable) BEGIN
|
|
RETURN
|
|
END
|
|
|
|
UPDATE i
|
|
SET i.tanevId = t.ID
|
|
FROM @intezmenyTable i
|
|
INNER JOIN T_TANEV t ON t.C_INTEZMENYID = i.id
|
|
|
|
AND t.TOROLT = 'F'
|
|
|
|
;WITH age AS (
|
|
SELECT 0 AS kor
|
|
UNION ALL
|
|
SELECT kor + 1 FROM age WHERE kor + 1 <= 100
|
|
)
|
|
|
|
SELECT 'TantargyiOktatokRiport' ReportName,
|
|
p.nev ReportKey1,
|
|
CASE f.C_NEME
|
|
WHEN 4 THEN 'Férfi'
|
|
WHEN 5 THEN 'Nő'
|
|
ELSE 'Nincs adat'
|
|
END ReportKey2
|
|
, COUNT(1) ReportValue
|
|
FROM T_FELHASZNALO f
|
|
LEFT JOIN (VALUES
|
|
('11-15', 11, 15), ('16-20', 16, 20), ('21-25', 21, 25), ('26-30', 26, 30), ('31-35', 31, 35), ('36-40', 36, 40), ('41-45', 41, 45),
|
|
('46-50', 46, 50), ('51-55', 51, 55), ('56-60', 56, 60), ('61-65', 61, 65), ('66-70', 66, 70), ('71-75', 71, 75),
|
|
('76-80', 76, 80), ('81-85', 81, 85), ('86-90', 86, 90), ('81-95', 81, 95), ('96-100', 96, 100), ('101-105', 101, 105)
|
|
) p (nev, also, felso) ON
|
|
CASE
|
|
WHEN DATEADD(YY, DATEDIFF(yy, f.C_SZULETESIDATUM, GETDATE()), f.C_SZULETESIDATUM) < GETDATE() THEN DATEDIFF(yy, f.C_SZULETESIDATUM, GETDATE())
|
|
ELSE DATEDIFF(yy, f.C_SZULETESIDATUM,GETDATE()) - 1
|
|
END BETWEEN p.also AND p.felso
|
|
INNER JOIN @intezmenyTable i ON f.C_INTEZMENYID = i.id AND f.C_TANEVID = i.tanevId
|
|
WHERE EXISTS (SELECT 1 FROM T_ALKALMAZOTT a WHERE a.ID = f.ID)
|
|
AND EXISTS (SELECT 1 FROM T_FOGLALKOZAS
|
|
INNER JOIN T_TANTARGY ON T_FOGLALKOZAS.C_TANTARGYID = T_TANTARGY.ID
|
|
WHERE T_TANTARGY.C_TARGYKATEGORIA = @targykategoriaId
|
|
AND T_FOGLALKOZAS.C_TANARID = f.ID
|
|
)
|
|
AND f.TOROLT = 'F'
|
|
GROUP BY f.C_NEME, p.nev
|
|
END
|