Search found 177 matches

by robinwilson16
Thu Mar 02, 2017 10:28 pm
Forum: Table Valued Functions
Topic: TVF_AcademicYear
Replies: 0
Views: 185

TVF_AcademicYear

Returns the current academic year/session. Input is a date

Code: Select all

CREATE FUNCTION [dbo].[TVF_AcademicYear] (@CurrentDate Date)
RETURNS TABLE
AS
RETURN

SELECT
	Value = SES.s_name
FROM ulive.dbo.capd_session SES
WHERE
	SES.s_type = 'FY'
	AND SES.s_start <= @CurrentDate
	AND SES.s_end >= @CurrentDate
by robinwilson16
Wed Mar 01, 2017 9:07 pm
Forum: Bugs and Issues
Topic: UNIT-e 4.35 Bug - Student Periodic ILR Creation Tool Error - May Exclude Learners from ILR
Replies: 1
Views: 991

Re: UNIT-e 4.35 Bug - Student Periodic ILR Creation Tool Error - May Exclude Learners from ILR

Capita have confirmed this is an issue and it has been raised as a critical PCR but unfortunately won't be fixed until 4.36 due at the end of March. However for anyone who has upgraded as long as you search by student then enrolment rather than searching by course as we normally do then it works nor...
by robinwilson16
Mon Feb 20, 2017 6:17 pm
Forum: Stored Procedures
Topic: SPR_SequenceCustom - Returning the next Capita sequence to use for insert statements where tables have custom sequences
Replies: 0
Views: 857

SPR_SequenceCustom - Returning the next Capita sequence to use for insert statements where tables have custom sequences

Procedure to set and retrieve the next sequence value (not yet used this one!): ALTER PROCEDURE [dbo].[SPR_SequenceCustom] @reference AS VARCHAR(50) AS BEGIN DECLARE @Seq AS VARCHAR(50) = '0' UPDATE caps_sequences WITH (TABLOCK) SET seq_value = seq_value + 1 WHERE seq_ref = @reference; SELECT S.seq_...
by robinwilson16
Mon Feb 20, 2017 6:14 pm
Forum: Stored Procedures
Topic: SPR_Sequence - Returning the next Capita sequence to use for insert statements
Replies: 0
Views: 1031

SPR_Sequence - Returning the next Capita sequence to use for insert statements

Procedure to set and retrieve the next sequence value: CREATE PROCEDURE [dbo].[SPR_Sequence](@Seq numeric(16, 0) OUTPUT) AS BEGIN SET NOCOUNT ON; UPDATE ulive.dbo.caps_id SET nextval = nextval + 1; SELECT @Seq = CAST ( FORMAT ( ID.nextval, '727000000000000' ) AS numeric(16, 0) ) FROM ulive.dbo.caps_...
by robinwilson16
Mon Feb 20, 2017 6:07 pm
Forum: Shared Datasets
Topic: SSRS_WEEK_CUR
Replies: 0
Views: 1441

SSRS_WEEK_CUR

Lists the current week number: CREATE FUNCTION [dbo].[SSRS_WEEK_CUR] ( @Session VARCHAR(7) ) RETURNS TABLE AS RETURN --DECLARE @Session VARCHAR(7) = '2016-17' SELECT Code = WN.WeekNum, Description = FORMAT ( WN.WeekNum, '0#' ) + ' (' + FORMAT ( WN.StartDate, 'dd/MM/yyyy' ) + ' - ' + FORMAT ( WN.EndD...
by robinwilson16
Mon Feb 20, 2017 6:06 pm
Forum: Shared Datasets
Topic: SSRS_WEEK
Replies: 0
Views: 1005

SSRS_WEEK

Lists the week numbers and dates: CREATE FUNCTION [dbo].[SSRS_WEEK] ( @Session VARCHAR(7) ) RETURNS TABLE AS RETURN --DECLARE @Session VARCHAR(7) = '2016-17' SELECT Code = WN.WeekNum, Description = FORMAT ( WN.WeekNum, '0#' ) + ' (' + FORMAT ( WN.StartDate, 'dd/MM/yyyy' ) + ' - ' + FORMAT ( WN.EndDa...
by robinwilson16
Mon Feb 20, 2017 6:05 pm
Forum: Shared Datasets
Topic: SSRS_PROG_MULTI
Replies: 0
Views: 1058

SSRS_PROG_MULTI

Lists programmes for multi-select: CREATE FUNCTION [dbo].[SSRS_PROG_MULTI] ( @Session VARCHAR(7), @Fac VARCHAR(MAX), @Team VARCHAR(MAX) ) RETURNS TABLE AS RETURN /*DECLARE @Session VARCHAR(7) = '2015-16' DECLARE @Fac VARCHAR(4) = 'CNEB' DECLARE @Team VARCHAR(20) = 'BISM-BAF100'*/ SELECT Code = PRG.m...
by robinwilson16
Mon Feb 20, 2017 6:05 pm
Forum: Shared Datasets
Topic: SSRS_PROG
Replies: 0
Views: 973

SSRS_PROG

Lists programmes: CREATE FUNCTION [dbo].[SSRS_PROG] ( @Session VARCHAR(7), @Fac VARCHAR(4), @Team VARCHAR(20) ) RETURNS TABLE AS RETURN /*DECLARE @Session VARCHAR(7) = '2015-16' DECLARE @Fac VARCHAR(4) = 'CNEB' DECLARE @Team VARCHAR(20) = 'BISM-BAF100'*/ SELECT Code = PRG.m_reference, Description = ...
by robinwilson16
Mon Feb 20, 2017 6:04 pm
Forum: Shared Datasets
Topic: SSRS_COST_CENTRE_MULTI
Replies: 0
Views: 1054

SSRS_COST_CENTRE_MULTI

Lists cost centres for multi-select: CREATE FUNCTION [dbo].[SSRS_COST_CENTRE_MULTI] ( @Session VARCHAR(7), @Fac VARCHAR(MAX), @Team VARCHAR(MAX) ) RETURNS TABLE AS RETURN --DECLARE @Session VARCHAR(7) = '2015-16' --DECLARE @Fac VARCHAR(4) = 'CNEB' --DECLARE @Team VARCHAR(4) = 'BISM' SELECT Code = '-...
by robinwilson16
Mon Feb 20, 2017 6:03 pm
Forum: Shared Datasets
Topic: SSRS_COST_CENTRE
Replies: 0
Views: 983

SSRS_COST_CENTRE

Lists cost centres: CREATE FUNCTION [dbo].[SSRS_COST_CENTRE] ( @Session VARCHAR(7), @Fac VARCHAR(4), @Team VARCHAR(20) ) RETURNS TABLE AS RETURN --DECLARE @Session VARCHAR(7) = '2015-16' --DECLARE @Fac VARCHAR(4) = 'CNEB' --DECLARE @Team VARCHAR(4) = 'BISM' SELECT Code = RIGHT ( TEAM.s_reference, LE...