Difference between revisions of "SQL example to testing data"

From Catglobe Wiki
Jump to: navigation, search
 
(5 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
<accesscontrol>Main:MyGroup</accesscontrol>
 +
[[Category:Miscellaneous]]
 
=== Version 5.8 ===
 
=== Version 5.8 ===
 
<source lang="sql">
 
<source lang="sql">
Line 33: Line 35:
 
select top 100 id,option_number,[value],answer_option_id,question_id,[user_id],resource_project_questionnaire_id from data
 
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
 
where resource_project_questionnaire_id = @RPQ_id
 +
</source>
 +
 +
=== get all cati appointments ===
 +
<source lang="sql">
 +
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
 
</source>
 
</source>
  
Line 66: Line 76:
 
where resource_project_questionnaire_id = 20595518
 
where resource_project_questionnaire_id = 20595518
 
</source>
 
</source>
 
=== Version 5.6 ===
 
<source lang="sql">
 
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 = 5344
 
 
-- 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 = 5521 and q.id = qq.question_id
 
 
-- lay ve cac answer option
 
select top 10 * from answer_option
 
where question_id = 248685
 
 
select id as 'rpqid',[user_id] from resource_project_questionnaire
 
where project_questionnaire_id = 5344
 
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 = 18104527
 
 
 
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 = 18104533
 
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 = 18104534
 
</source>
 
 
=== Version 5.5 ===
 
<source lang="sql">
 
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 = 5038
 
 
-- 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 = 5142 and q.id = qq.question_id
 
 
-- lay ve cac answer option
 
select top 10 * from answer_option
 
where question_id = 212547
 
 
 
-- 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 = 15785316
 
 
 
select top 10 * from data_open
 
where data_id = 127194938
 
 
</source>
 
 
  
 
=== get PQ has many RPQ ===
 
=== get PQ has many RPQ ===

Latest revision as of 11:14, 16 October 2013

<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