Difference between revisions of "SQL example to testing data"
Wikicatglobe (talk | contribs) |
|||
(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> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
=== get PQ has many RPQ === | === get PQ has many RPQ === |
Latest revision as of 11:14, 16 October 2013
<accesscontrol>Main:MyGroup</accesscontrol>
Contents
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