45 lines
1.1 KiB
Transact-SQL
45 lines
1.1 KiB
Transact-SQL
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('sp_GetDokumentumTanuloNaploSorszam') IS NOT NULL BEGIN
|
|
DROP PROCEDURE [sp_GetDokumentumTanuloNaploSorszam]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetDokumentumTanuloNaploSorszam
|
|
@osztalyId int
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @students TABLE (ID INT, NaploSorszam INT, Rendez INT)
|
|
|
|
INSERT INTO @students
|
|
SELECT DISTINCT
|
|
C_TANULOID
|
|
, tanugy.C_NAPLOSORSZAM
|
|
, IIF(MONTH(MIN(C_BELEPESDATUM)) BETWEEN 9 AND 10, 1, 2)
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
INNER JOIN T_TANULO_OSSZES t on t.id=tcs.C_TANULOID
|
|
INNER JOIN T_TANULOTANUGYIADATOK tanugy ON tanugy.C_TANULOCSOPORTID = tcs.ID AND tanugy.TOROLT = 'F'
|
|
WHERE C_OSZTALYCSOPORTID = @osztalyId
|
|
AND tcs.TOROLT = 'F'
|
|
GROUP BY C_TANULOID, tanugy.C_NAPLOSORSZAM
|
|
|
|
DECLARE @max INT = (SELECT MAX(NaploSorszam) FROM @students)
|
|
|
|
SELECT
|
|
s.ID Tanulo
|
|
, ISNULL(NaploSorszam, ISNULL(@max, 0) + ROW_NUMBER() OVER (partition by NaploSorszam ORDER BY Rendez, C_NYOMTATASINEV)) Sorszam
|
|
FROM @students s
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID=s.ID
|
|
GROUP BY s.Id, NaploSorszam, C_NYOMTATASINEV, Rendez
|
|
ORDER BY Sorszam
|
|
|
|
END
|
|
GO |