JIRA - Two Separate Queries

*Report 1 - SC status duration*
{CODE:SQL}
Declare @year as integer
SET @year = 2017

Declare @qualuid as integer
SET @qualuid = -1

Declare @scStatus as varchar(2)
SET @scStatus = 'ER'


SELECT
-- ptr.ptreguid
--,
sc.STCOSHORTNAMETEXT as study_contract_short_name
,sc.STCOSTATUSIND as study_contract_status
,p.PERSTUDENTID as [student_id]
,p.PERFULLNAMETEXT as [full_name]
,format(isnull(sc.STCOAPPLICATIONDATE,scsh_min_date.min_scstatdate),'yyyy-MM-dd')  as application_date
,format(sc.STCOSTARTDATE,'yyyy-MM-dd') as study_contract_startdate
,DATEDIFF(DAY, isnull(sc.STCOAPPLICATIONDATE,scsh_min_date.min_scstatdate), getdate()) as count_days_from_application_date
,DATEDIFF(DAY, scsh.max_scstatdate , getdate()) as count_days_at_status
,DATEDIFF(DAY, getdate(), sc.STCOSTARTDATE) as count_days_before_start

FROM ST_CONTRACT sc
LEFT JOIN OT_ENTRY ote on ote.OTEUID = sc.OTEUID
LEFT JOIN PT_REGISTER ptr on ptr.PTREGUID = ote.PTREGUID
LEFT JOIN PERSON p on p.PERSONUID = sc.PERSONUID
LEFT JOIN ( SELECT scsh1.STUDYCONTRACTUID  , scsh1.SCSTATUSIND, MAX(scsh1.SCSTATDATE) as max_scstatdate FROM ST_SCSTATUSHISTORY scsh1 WHERE 1=1 AND scsh1.SCCHANGETYPE in ('1','3') GROUP BY scsh1.STUDYCONTRACTUID, scsh1.SCSTATUSIND ) scsh on scsh.STUDYCONTRACTUID = sc.STUDYCONTRACTUID and scsh.SCSTATUSIND = sc.STCOSTATUSIND
LEFT JOIN ( SELECT scsh2.STUDYCONTRACTUID  , MIN(scsh2.SCSTATDATE) as min_scstatdate FROM ST_SCSTATUSHISTORY scsh2 WHERE 1=1 AND scsh2.SCCHANGETYPE in ('1','3') GROUP BY scsh2.STUDYCONTRACTUID) scsh_min_date on scsh_min_date.STUDYCONTRACTUID = sc.STUDYCONTRACTUID

WHERE 1=1
AND year(sc.STCOSTARTDATE) <= @year and year(sc.STCOFINISHDATE) >= @year
--AND sc.STCOSTATUSIND in (@scStatus)
AND (sc.STCOSTATUSIND like 'A_' OR sc.STCOSTATUSIND in ('EP','ER', 'EC'))
ORDER BY
DATEDIFF(DAY, getdate(), sc.STCOSTARTDATE) asc
,sc.STCOSHORTNAMETEXT
,p.PERSTUDENTID{CODE}


*Report 2 - Overview of all OTEs*
{CODE:SQL}
Declare @year as integer
SET @year = 2018


SELECT
ote.OTESHORTNAMETEXT as offered_entry_short_name
,format(ote.OTEOFFICIALSTARTDATE,'yyyy-MM-dd') as offered_entry_startdate
,sum(iif(sc.STCOSTATUSIND = 'AP',1,0)) as sum_AP
,sum(iif(sc.STCOSTATUSIND = 'AR',1,0)) as sum_AR
,sum(iif(sc.STCOSTATUSIND = 'AA',1,0)) as sum_AA
,sum(iif(sc.STCOSTATUSIND like 'A_' AND sc.STCOSTATUSIND not in ('AP','AR','AA'),1,0)) as sum_A_other
,sum(iif(sc.STCOSTATUSIND = 'EP',1,0)) as sum_EP
,sum(iif(sc.STCOSTATUSIND = 'ER',1,0)) as sum_ER
,sum(iif(sc.STCOSTATUSIND = 'EA',1,0)) as sum_EA
,sum(iif(sc.STCOSTATUSIND like 'E_' AND sc.STCOSTATUSIND not in ('EP','ER','EA'),1,0)) as sum_E_other

FROM ST_CONTRACT sc
LEFT JOIN OT_ENTRY ote on ote.OTEUID = sc.OTEUID
LEFT JOIN PT_REGISTER ptr on ptr.PTREGUID = ote.PTREGUID
WHERE 1=1
AND year(sc.STCOSTARTDATE) <= @year and year(sc.STCOFINISHDATE) >= @year
GROUP BY
ote.OTESHORTNAMETEXT
,ote.OTEOFFICIALSTARTDATE
ORDER BY
iif( year(ote.OTEOFFICIALSTARTDATE) < @year,1,0)
,ote.OTEOFFICIALSTARTDATE
,ote.OTESHORTNAMETEXT
{CODE}