69 lines
3.6 KiB
Transact-SQL
69 lines
3.6 KiB
Transact-SQL
-- =============================================
|
|
-- Description: Rudlof Kristóf - Tanár órarendi lefedettség
|
|
-- =============================================
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetTanarOrarendLefedettseg]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTanarOrarendLefedettseg]
|
|
END
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetTanarOrarendLefedettseg]
|
|
@intezmenyId int,
|
|
@tanevId int
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
declare @AktivtanevId int = (select Id from T_TANEV_OSSZES tanev where c_aktiv='T' and c_intezmenyId=@intezmenyId and Torolt='F')
|
|
|
|
declare @HetEleje date
|
|
declare @HetVege date
|
|
|
|
if (@tanevId=@AktivtanevId)
|
|
begin
|
|
set @HetEleje =(select distinct nh.c_hetkezdonapja from T_NAPTARIHET_OSSZES nh where getdate()>nh.c_hetkezdonapja and getdate()<nh.c_hetutolsonapja and nh.torolt='F' and nh.c_tanevid=@tanevid and nh.c_intezmenyId=@intezmenyId)
|
|
set @HetVege =(select distinct nh.c_hetutolsonapja from T_NAPTARIHET_OSSZES nh where getdate()>nh.c_hetkezdonapja and getdate()<nh.c_hetutolsonapja and nh.torolt='F' and nh.c_tanevid=@tanevid and nh.c_intezmenyId=@intezmenyId)
|
|
end
|
|
begin
|
|
declare @UtolsoNap date = (select c_napdatuma from t_naptarinap_osszes where c_naptipusa=1395 and c_intezmenyId=@intezmenyId and c_tanevId=@tanevid and torolt='F')
|
|
set @HetEleje =(select distinct nh.c_hetkezdonapja from T_NAPTARIHET_OSSZES nh where @UtolsoNap>nh.c_hetkezdonapja and @UtolsoNap<nh.c_hetutolsonapja and nh.torolt='F' and nh.c_tanevid=@tanevid and nh.c_intezmenyId=@intezmenyId)
|
|
set @HetVege =(select distinct nh.c_hetutolsonapja from T_NAPTARIHET_OSSZES nh where @UtolsoNap>nh.c_hetkezdonapja and @UtolsoNap<nh.c_hetutolsonapja and nh.torolt='F' and nh.c_tanevid=@tanevid and nh.c_intezmenyId=@intezmenyId)
|
|
end
|
|
|
|
--órarendi lefedettség
|
|
create table #csoportok (CsoportId int, Pedagogus nvarchar(max), CsoportTipus nvarchar(max), Oraszam float, Hetirend nvarchar(max))
|
|
insert into #csoportok
|
|
select distinct cs.id, f.c_nyomtatasinev, d.c_name, fog.c_oraszam, Hetirend.c_name from T_CSOPORT_OSSZES cs
|
|
inner join T_FOGLALKOZAS_OSSZES fog on fog.c_osztalycsoportid=cs.id and fog.c_tanevid=@tanevid and fog.torolt='F'
|
|
inner join T_ORARENDIORA_OSSZES oo on oo.c_foglalkozasid=fog.id and ((oo.c_oraervenyessegkezdete<=@HetEleje and oo.c_oraervenyessegkezdete>=@HetEleje) or
|
|
(oo.c_oraervenyessegkezdete<=@HetVege and oo.c_oraervenyessegvege>=@HetEleje))
|
|
and oo.c_tanevid=@tanevid and oo.torolt='F'
|
|
inner join T_FOGLALKOZASOK_TANAROK ft on ft.c_foglalkozasokid=fog.id
|
|
inner join T_FELHASZNALO_OSSZES f on f.id=ft.c_tanarokid and f.c_intezmenyid=@intezmenyId and f.c_tanevid=@tanevid and f.torolt='F'
|
|
inner join (select distinct d.id, d.c_name from T_DICTIONARYITEMBASE_OSSZES d)d on d.id=cs.c_tipusa
|
|
inner join (select distinct d.id, d.c_name from T_DICTIONARYITEMBASE_OSSZES d)Hetirend on Hetirend.id=oo.c_hetirend
|
|
where cs.torolt='F' and cs.c_alintezmenyid=@intezmenyId and cs.c_altanevid=@tanevId
|
|
|
|
select Pedagogus COLUMN456, CsoportTipus COLUMN444, sum(Oraszam) COLUMN3670 from #csoportok cs group by Pedagogus, CsoportTipus order by Pedagogus
|
|
|
|
--órarendi lefedettség hetirenden
|
|
declare @cols as nvarchar(max),
|
|
@query as nvarchar(max);
|
|
|
|
select distinct @cols= isnull(@cols + ',','') +quotename(Hetirend) from #csoportok
|
|
|
|
set @query='select Pedagogus as COLUMN456, CsoportTipus COLUMN444, '+@cols+' from #csoportok cs pivot (sum(Oraszam) for Hetirend in ('+@cols+')) pv'
|
|
|
|
EXEC sp_executesql @query;
|
|
drop table #csoportok
|
|
|
|
END
|
|
|
|
GO |