Code: Select all
CREATE FUNCTION [dbo].[SSRS_FAC_MULTI] (
@Session VARCHAR(7)
)
RETURNS TABLE
AS
RETURN
--DECLARE @Session VARCHAR(7) = '2016-17'
SELECT
Code = '-1',
Description = '-- All Faculties --'
UNION ALL
SELECT
Code = FAC.d_reference,
Description = FAC.d_reference + ' - ' + FAC.d_name
FROM ulive.dbo.capd_department FAC
INNER JOIN (
SELECT DISTINCT
FacID = PRG.m_moduledept
FROM ulive.dbo.capd_module PRG
WHERE
PRG.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
AND PRG.m_end >= LEFT ( @Session, 4 ) + '-08-01'
AND PRG.m_type = 'P'
AND PRG.m_status IN ( 'R', 'P' )
) CRS On CRS.FacID = FAC.d_id
WHERE
COALESCE ( FAC.d_start, LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31' ) <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
AND COALESCE ( FAC.d_end, LEFT ( @Session, 4 ) + '-08-01' ) >= LEFT ( @Session, 4 ) + '-08-01'