SQL example to testing data

<accesscontrol>Main:MyGroup</accesscontrol>

Version 5.8

declare @PQ_name nvarchar (100)
declare @PQ_id int
declare @Questionnaire_id int
declare @Question_id int
declare @RPQ_id int

set @PQ_name = 'minh_questionnaire_For_testing'

-- get PQ id from the specified PQ name
set @PQ_id = (select id from project_questionnaire_resource where [short_name] like @PQ_name)
--select @PQ_id as 'PQ Id'

-- get questionnaire id cua PQ do 
set @Questionnaire_id = (select questionnaire_id from project_questionnaire where id = @PQ_id)
--select @Questionnaire_id as 'Questionnaire Id'

-- get questions of that questionnaire 
select top 100 q.id, label, sub_question_count, question_type 
from question q, questionnaire_question qq
where qq.questionnaire_id = @Questionnaire_id and q.id = qq.question_id

-- get answer option from a question
select top 100 * from answer_option
where question_id = @Question_id

-- get the all RPQ of that PQ
select top 100 * from resource_project_questionnaire
where project_questionnaire_id = @PQ_id

-- get the answers
select top 100 id,option_number,[value],answer_option_id,question_id,[user_id],resource_project_questionnaire_id from data
where resource_project_questionnaire_id = @RPQ_id

get all cati appointments

select top 100 * from resource_project_questionnaire
where next_contact_datetime is not null and deleted_date is null and cati_list = 3 
and dedicated_user_id = 9328774
and project_questionnaire_id = 5028

Version 5.7

select top 10 Id, [name] from project_questionnaire_resource
where [name] like '%minh_questionnaire_For_testing%'

-- lay ve questionnaire id cua PQ do ==>> 14
select top 10 id, questionnaire_id from project_questionnaire
where id = 6059

-- lay ve cac question cua PQ do 
select top 100 q.id, label, sub_question_count, question_type 
from question q, questionnaire_question qq
where qq.questionnaire_id = 6384 and q.id = qq.question_id

-- lay ve cac answer option
select top 10 * from answer_option
where question_id = 273102

select id as 'rpqid',[user_id] from resource_project_questionnaire
where project_questionnaire_id = 6059 
order by id desc

-- lay ve answer 
select top 100 id,option_number,[value],answer_option_id,question_id,[user_id],resource_project_questionnaire_id from data
where resource_project_questionnaire_id = 20595510

select top 100 id,option_number,[value],answer_option_id,question_id,[user_id],resource_project_questionnaire_id from data
where resource_project_questionnaire_id = 20595517
select top 100 id,option_number,[value],answer_option_id,question_id,[user_id],resource_project_questionnaire_id from data
where resource_project_questionnaire_id = 20595518

get PQ has many RPQ

select top 100 count(id),project_questionnaire_id
from resource_project_questionnaire
group by project_questionnaire_id
order by count(id) desc

get PQ has many question

SELECT count(qq.questionnaire_id ),questionnaire_id -- q.id, label, sub_question_count, question_type 
FROM question q, questionnaire_question qq
WHERE  q.id = qq.question_id
group by qq.questionnaire_id 
having count(qq.questionnaire_id ) > 500

select top 10 * from project_questionnaire_resource where questionnaire_id = 5517

get PQ has many question & many rpq

select top 100 count(id),project_questionnaire_id
from resource_project_questionnaire
where project_questionnaire_id in
(
	select id from project_questionnaire_resource
	where questionnaire_id in (
	SELECT questionnaire_id 
        FROM question q, questionnaire_question qq
        WHERE  q.id = qq.question_id
        group by qq.questionnaire_id 
        having count(qq.questionnaire_id ) > 500
	)
)
group by project_questionnaire_id
order by count(id) desc

Update XML from one DCS to another DCS

update data_cache_specification 
set [xml] = (select [xml] from data_cache_specification where resource_id = 33625464)
where resource_id = 34793015