DyQL Online Demo

<CommentTime>
to_char(COMMENTS.COMMENTTIME, 'DD-Mon-YYYY HH24:Mi')
</CommentTime>

<CommentType>
case COMMENTS.COMMENTTYPE 
	when 0 then 'Form' 
	when 1 then 'Item' 
	else 'Unknown' end
</CommentType>

<sitefilter>SITEID in {siteid}</sitefilter>
<siteid>(
select SITEID from IRV_USERS_SITES where USERNAME = #sq ($account.personalInfo.userName)#
)</siteid>

<CommentFields>(
select
	SUBJECTID, SITEID, ITEMID, ITEMREV, FORMID, FORMREV
,	COMMENTUSERID, COMMENTTIME, COMMENTTYPE, COMMENTTEXT
from
	IRV_CUR_COMMENT
where
	{sitefilter}
)</CommentFields>

<UserFields>(
select USERID, USERNAME, RIGHTSGROUP from IRV_CUR_USER
)</UserFields>

<SiteFields>(
select
	SITEID, SITECOUNTRY, SITENAME
from
	IRV_CUR_SITE
where
	{sitefilter}
)</SiteFields>

<SubjectFields>(
select
	SUBJECTID, SUBJECTNUMBERSTR
from
	IRV_CUR_SUBJECT_DIM
where
	{sitefilter}
)</SubjectFields>

<FormFields>(select FORMID, FORMREV, FORMNAME from IRV_FORM_REVS)</FormFields>
<ItemFields>(select ITEMID, ITEMREV, ITEMQUESTION from IRV_ITEM_REVS)</ItemFields>

var PrematurelyDiscontinued = TERMINATION.C_RDCTERMYN_ITMTERMDISC;
var LastAttendedVisit = TERMINATION.PLW6_ITMTERMDISC;
var StudyTerminationDate = TERMINATION.DT_ITMTERMDATE;
var FollowUp = EOT.C_RDCEOT3_ITMEOT3;
var ScreeningDate = ENROLMENT.DT_ITMSCRDAT;
var InformedConsentDate = ENROLMENT.DT_ITMENRIC;
var subfilter=and SUBJECTNUMBERSTR not like 'SCR%';

select
	SUBJECTS.*
,	{sel, week=1}
,	{sel, week=3}
,	{sel, week=5}
,	{sel, week=7}
,	{sel, week=9}
,	{sel, week=11}
,	{sel, week=13}
,	{sel, week=15}
,	{sel, week=17}
,	{sel, week=19}
from 
	({subject}) SUBJECTS 
,	{from_treatment, week=1}		-- use variable "from_treatment" with attribute "week"
,	{from_treatment, week=3}		-- variables can be set as xml declaration 
,	{from_treatment, week=5}		-- a xml declaration is always within <extends>...</extends>
,	{from_treatment, week=7}		-- <extends> can be set more than once in a script
,	{from_treatment, week=9}		-- attributes are separated by comma
,	{from_treatment, week=11}		-- a comma in an attribute value must be declared as ,#
,	{from_treatment, week=13}		-- quotes or double quotes are usually not part of a syntax declaration
,	{from_treatment, week=15}
,	{from_treatment, week=17}
,	{from_treatment, week=19}
where 
	{where_treatment, week=1}
and	{where_treatment, week=3}
and	{where_treatment, week=5}
and	{where_treatment, week=7}
and	{where_treatment, week=9}
and	{where_treatment, week=11}
and	{where_treatment, week=13}
and	{where_treatment, week=15}
and	{where_treatment, week=17}
and	{where_treatment, week=19}

<sel>		--- [fields for each week] ---
--	TREATMENT{week}."VisitMnemonic Week{week}",
--	TREATMENT{week}."Administered Week{week}",
	to_char(TREATMENT{week}."Admin Date Week{week}", 'DD-MON-YYYY') "Admin Date Week{week}",
--	TREATMENT{week}."Cycle Number Week{week}",
	TREATMENT{week}."Total Dose Week{week}"
</sel>

<subject>	--- [select fields for subject data] ---
(
select * from (
select distinct 
	substr(tSITE.SITECOUNTRY, 2, length(tSITE.SITECOUNTRY)-2) as "Country"
,	tSITE.SITENAME as "SiteName"
--,	tSITE.SITEID as "SITEID"
--,	tSubjectDim.SUBJECTID as "SUBJECTID"
,	tSubjectDim.SUBJECTNUMBERSTR as "Subject Number"
,	DEMOGRAPHY.DT_ITMDOB as "Date of Birth"
,	substr(DEMOGRAPHY.RDCSEX_ITMDEMSEX,1,1) as "Gender"
,	{ScreeningDate} as "Screening Date"
,	{status} as "Status"
,	{Last_Admin_Date} as "Last Admin Date"
--,	TERMINATION.PLW6_ITMTERMDISC as "Last Attended Visit"
,	{reason_for_discontinuation} as "Reason For Discontinuation"
,	EOT.DT_ITMEOT1 as "Last Study Treatment"
from
	(select * from IRV_CUR_SITE where {sitefilter})						tSITE
,	(select * from IRV_CUR_SUBJECT_DIM where {sitefilter} {subfilter})	tSubjectDim
,	(select SUBJECTID, SITEID from IRV_CUR_SUBJECT where {sitefilter})	tSubjectFact
,	(select * from "CV_FRMDEM"  where {sitefilter})						DEMOGRAPHY
,	(select * from "CV_FRMENR"  where {sitefilter})						ENROLMENT
,	(select * from "CV_FRMTERM" where {sitefilter})						TERMINATION
,	(select * from "CV_FRMEOT"  where {sitefilter})						EOT
where
	tSubjectFact.SITEID = tSITE.SITEID
	and tSubjectFact.SUBJECTID = tSubjectDim.SUBJECTID
	and tSubjectDim.SUBJECTID = DEMOGRAPHY.SUBJECTID (+)
	and tSubjectDim.SUBJECTID = ENROLMENT.SUBJECTID (+)
	and tSubjectDim.SUBJECTID = TERMINATION.SUBJECTID (+)
	and tSubjectDim.SUBJECTID = EOT.SUBJECTID (+)
)
)
</subject>

<status>
case
when ({PrematurelyDiscontinued} = 1 and {LastAttendedVisit} is null) then ('Screen.Fail.')
when ({PrematurelyDiscontinued} = 1)	then ('Prematurely Discontinued')
when ({StudyTerminationDate} is not null)	then ('Complete')
when ({FollowUp} = 1)				then ('Follow UP')
when ({Last_Admin_Date} is not null)	then ('Treatment')
when ({ScreeningDate} is not null)		then ('Screening')
when ({InformedConsentDate} is not null)	then ('Enroled')
else (null)
end
</status>

<reason_for_discontinuation>
case
when (TERMINATION.C_RDCTERMYN_ITMTERMDISC is null) then (null)
when (TERMINATION.C_RDCTERMYN_ITMTERMDISC = 0)     then (null) -- Early Withdrawn: Discontinue = No
else -- Yes
coalesce(
	TERMINATION."TXTTERMREASON1_ITMTERMREASON", 
	TERMINATION."TXTTERMREASON2_ITMTERMREASON", 
	TERMINATION."TXTTERMREASON5_ITMTERMREASON", 
	TERMINATION."TXTTERMREASON6_ITMTERMREASON", 
	'NA')
end
</reason_for_discontinuation>

<Last_Admin_Date>	--- [select data for one column in subjects table] ---
(
select 
	max(StudyTreatment.DT_DTMBEVADMYES_ITMBEVADM) "Last Admin Date"
from 
	(select * from "CV_FRMBEV" where {sitefilter}) StudyTreatment
where
	StudyTreatment.SUBJECTID = tSubjectDim.SUBJECTID
group by
	StudyTreatment.SUBJECTID
)
</Last_Admin_Date>

<from_treatment>	--- [define table for data of week 1 to week 13] ---
(
select * from (
select 
	tSubjectDim2.SUBJECTNUMBERSTR Subject{week}
,	tBEV.VISITMNEMONIC "VisitMnemonic Week{week}"
,	to_char(
	case
		when (substr(tBEV.RDCBEVADM_ITMBEVADM,1,3)='Yes') THEN ('Yes')
		when (substr(tBEV.RDCBEVADM_ITMBEVADM,1,2)='No') THEN ('No')
		else (null)
	end) "Administered Week{week}"
,	tBEV.DT_DTMBEVADMYES_ITMBEVADM "Admin Date Week{week}"
,	sum(tBEV.ITMBEVCYCLEV) "Cycle Number Week{week}"
,	sum(tBEV.TXTBEVADMYES_ITMBEVADM) "Total Dose Week{week}"
,	to_char(case when (tBEV.VISITMNEMONIC is not null) then ('1') else (null) end) n{week}
from 
	(select * from "IRV_CUR_SUBJECT_DIM" where {sitefilter}) tSubjectDim2, 
	(select * from "CV_FRMBEV" where {sitefilter}) tBEV
where 
	tSubjectDim2.SUBJECTID = tBEV.SUBJECTID (+)
	and tSubjectDim2.SUBJECTNUMBERSTR not like 'SCR%'
	and tBEV.VISITMNEMONIC = 'Week {week}'
group by 
	tSubjectDim2.SUBJECTNUMBERSTR, 
	tBEV.VISITMNEMONIC, 
	tBEV.RDCBEVADM_ITMBEVADM, 
	tBEV.DT_DTMBEVADMYES_ITMBEVADM
)
) TREATMENT{week}
</from_treatment>

<where_treatment>
SUBJECTS."Subject Number" = TREATMENT{week}.Subject{week} (+)
</where_treatment>

<sitefilter>SITEID in {siteid}</sitefilter>
<siteid>(
select SITEID from IRV_USERS_SITES where USERNAME = #sq ($account.personalInfo.userName)#
)</siteid>