CREATE INDEX IF NOT EXISTS idx1_firstcontact ON firstcontact (studentid); --create index
CREATE INDEX IF NOT EXISTS idx2_firstcontact ON firstcontact (studentid ,createddate,modifieddate); --create index
with
firstcontact_std
as
( select studentid
,date(createddate) createddate
,date(modifieddate) modifieddate
from firstcontact
group by studentid,date(createddate),date(modifieddate))
,firstcontact_res
as
( select studentid
,date(createddate) createddate
,date(modifieddate) modifieddate
,responselabel
,surveylabelnumber
from firstcontact
)
select
fc.studentid "V1"
,fc.createddate "V2"
,fc.modifieddate "V3"
,(select responselabel from firstcontact_res Q13_1 where Q13_1.surveylabelnumber ='Q13_1' and fc.studentid=Q13_1.studentid and fc.createddate=Q13_1.createddate and fc.modifieddate=Q13_1.modifieddate limit 1 ) "Q13_1"
,(select responselabel from firstcontact_res Q65_TEXT where Q65_TEXT.surveylabelnumber ='Q65_TEXT' and fc.studentid=Q65_TEXT.studentid and fc.createddate=Q65_TEXT.createddate and fc.modifieddate=Q65_TEXT.modifieddate limit 1 ) "Q65_TEXT"
from firstcontact_std fc
;