Database

MTM_ACTV_STAFF

ID | ACTV | STAFF | CYCLETEMPLATE
==================================
1  | 1    | 3     | 3
2  | 4    | 2     | Null

MTM_ACTV_STAFF_CYCLE  

ID | IDL  | CYCLE   
=========================
1  | 1    | 3     
2  | 4    | 2    

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[sp_Get_Staff_Cycles] 
(
@Subject_Descr varchar(30)
)
AS
--Check if the table Exists 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]
--Get the Cycle template description
Declare @CycletemplateTerm varchar(60)
set @CycletemplateTerm = (  select top 1 t.descr from MTM_ACTV_STAFF mtn
							inner join tbl_actv a on a.id  = MTN.actv
							inner join tbl_cntc cc on a.cntcID = cc.id
							inner join tbl_modl m on m.id = cc.modlid
							inner join tbl_term t on a.term = t.id 
							LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
							ON C.IDL = MTN.ID
							WHERE M.DESCR = @Subject_Descr
						)
 
SELECT [ID] = IDENTITY (int,1,1),A.DESCR AS [ACTIVITY], S.DESCR,
CASE MTN.CYCLETEMPLATE WHEN NULL THEN  C.CYCLES
ELSE @CycletemplateTerm
END AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
inner join tbl_actv a on a.id  = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr

CASE MTN.CYCLETEMPLATE WHEN NULL THEN  C.CYCLES
ELSE @CycletemplateTerm
END AS CYCLETEMPLATE

Tbl_Campaigns (table to be inserted to)

Key | field_1 | field_2 | field_3 | field_4 | field_5 etc

Tbl_Organisations (table to get the 5 random records from)

Key | field_1 

DECLARE @EnterTime datetime, @DischargeTime datetime

Select @EnterTime = convert(varchar,EnterTime,8) from visit

Select @DischargeTime = convert(varchar,NextTime,8) from visit

Select convert(varchar,@DischargeTime - @EnterTime,8)