January 22, 2013 at 2:45 am
here i am having four table
DECLARE @question table
(
QuestionId int,
Record uniqueidentifier,
indexnumber int,
questiondetail text,
IsActive bit
)
-- select NEWID()
insert into @question
select 1,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',1,'serader',1 union all
select 2,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',2,'serader1',1 union all
select 3,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',3,'serader2',1 union all
select 4,'CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',1,'rader1',1 union all
select 5,'CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',2,'rader2',1
--select *from @question
DECLARE @Choice table
(
Choiceid int,
QuestionId int,
indexnumber int,
choicedetail varchar(50),
IsActive bit
)
insert into @Choice
select 1,1,1,'a',1 union all
select 2,1,2,'b',1 union all
select 3,1,3,'others',1 union all
select 4,2,1,'rader1',1 union all
select 5,2,2,'rader2',1 union all
select 6,3,1,'a',1 union all
select 7,4,1,'asertin',1 union all
select 8,5,1,'ser123',1
DECLARE @Response table
(
Responseid uniqueidentifier,
Record uniqueidentifier,
collectionofResponse uniqueidentifier,
crdate datetime,
IsActive bit
)
insert into @Response
select '943B4955-BF16-4DC8-869F-9907DFC95AF7','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323','BEE0C69B-630F-40AB-8875-6E05D4ED2A29',GETDATE(),1 union all
select '72B15409-74B7-4185-A1DE-C3F2904E2787','CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D','4BA209D9-0244-4F37-AB47-49BB6C3D33AC',GETDATE(),1 union all
select 'A70FC1C6-8ED5-4777-9535-D8FB18146D17','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323','6CCE2683-245D-458A-B1F5-D9E1D4EA198A',GETDATE(),1
DECLARE @Responsechoice table
(
Responsechoice int,
Responseid uniqueidentifier,
QuestionId int,
Choiceid int,
IsActive bit
)
insert into @Responsechoice
select 1,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,1,1 union all
select 2,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,3,1 union all
select 3,'943B4955-BF16-4DC8-869F-9907DFC95AF7',4,7,1 union all
select 4,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',1,1,1 union all
select 5,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',2,4,1
by joining this four table i want to find reponsechoice count for a question depend upon the record it can be callculted
with the of Eugene Elutin i found the count of question in this link
http://www.sqlservercentral.com/Forums/Topic1365081-391-1.aspx
now i am just passing only one parameter @Record uniqueidentifier
for this proc now i am trying to get skipped count
declare @Record uniqueidentifier='DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323'
declare @TotalResponse int
select @TotalResponse= COUNT(1) from @Response where Record = @Record
;with cte
as
(
select a.Record, a.QuestionId,a.questiondetail, a.indexnumber, c.Choiceid, c.choicedetail
from
@question a
left join @choice c on c.QuestionId = a.QuestionId
where a.Record = @Record
), cte2 as(
select q.Record, q.QuestionId, q.indexnumber, rc.Choiceid, COUNT(*) [count]
from @question q
left join @Responsechoice rc on rc.QuestionId = q.QuestionId and rc.IsActive = 1
join @Response r on r.Responseid = rc.Responseid and r.IsActive = 1 -- this join just to ensure that response is active
where q.Record = @Record
group by q.Record, q.QuestionId, q.indexnumber, rc.Choiceid
), cte3 as(
select a.Record, a.QuestionId, q.questiondetail, a.indexnumber, a.Choiceid, c.choicedetail, a.[count]
from cte2 a
join @question q on q.QuestionId = a.QuestionId
join @choice c on c.Choiceid = a.Choiceid
where a.Record = @Record
)
select a.Record, a.QuestionId,a.questiondetail, a.indexnumber, a.Choiceid, a.choicedetail,isnull(b.[count],0)as [countofresponse],
@TotalResponse as TotalResponse
from
cte a
left join
cte3 b
on
a.ChoiceId=b.ChoiceId
i am trying for a output like this
Record QuestionId questiondetail indexnumber ChoiceidchoicedetailcountofresponseTotalResponseskipedquestion
DF8BC3681serader 1 1a220
DF8BC3681serader 1 2b020
DF8BC3681serader 1 3others120
DF8BC3682serader1 2 4rader1121
DF8BC3682serader1 2 5rader2021
DF8BC3683serader2 3 6a022
i think it was not clear i want get output column with solution for this @Record uniqueidentifier='DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323' parameter
skipedquestion
0
0
0
1
1
2
January 22, 2013 at 4:18 am
i am not getting what u actually need , your CTE query giving result as you require (you have shown above)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 22, 2013 at 4:29 am
reponse i made depend upon the collection key
for response ' 943B4955-BF16-4DC8-869F-9907DFC95AF7'
'A70FC1C6-8ED5-4777-9535-D8FB18146D17'
for this record
'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323
for the first response ' 943B4955-BF16-4DC8-869F-9907DFC95AF7'
question 1
and skipeed question 2,3
for the second response
'A70FC1C6-8ED5-4777-9535-D8FB18146D17'
question 1,2
and
skipeed question 3
i just want the count of skipedquestion as a another column in the same proc
January 22, 2013 at 7:31 am
can any plz try to solve my issue in the proc
January 22, 2013 at 8:07 am
Your question is extremely vague but I think you want something like this.
Add this above your cte.
declare @QuestionCount int
select @QuestionCount = count(*) from @Question where Record = @Record
Now you have the number or responses and the number of questions.
Add a new column to the final select.
@QuestionCount - @TotalResponse as NumSkipped
There is my shot in the dark. If that is not correct you are going to have to try to clarify what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2013 at 8:41 am
i just alter some changes in the table
i just find the solution for skipped like this when the data was like this and i add another question for the record which i checked in this proc
DECLARE @question table
(
QuestionId int,
Record uniqueidentifier,
indexnumber int,
questiondetail text,
IsActive bit
)
-- select NEWID()
insert into @question
select 1,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',1,'serader',1 union all
select 2,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',2,'serader1',1 union all
select 3,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',3,'serader2',1 union all
select 4,'CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',1,'rader1',1 union all
select 5,'CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',2,'rader2',1 union all
select 6,'DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',4,'serader3',1
--select *from @question
DECLARE @Choice table
(
Choiceid int,
QuestionId int,
indexnumber int,
choicedetail varchar(50),
IsActive bit
)
insert into @Choice
select 1,1,1,'a',1 union all
select 2,1,2,'b',1 union all
select 3,1,3,'others',1 union all
select 4,2,1,'rader1',1 union all
select 5,2,2,'rader2',1 union all
select 6,3,1,'a',1 union all
select 7,4,1,'asertin',1 union all
select 8,5,1,'ser123',1 union all
select 9,6,1,'ser123',1
DECLARE @Response table
(
Responseid uniqueidentifier,
Record uniqueidentifier,
collectionofResponse uniqueidentifier,
crdate datetime,
IsActive bit
)
insert into @Response
select '943B4955-BF16-4DC8-869F-9907DFC95AF7','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323','BEE0C69B-630F-40AB-8875-6E05D4ED2A29',GETDATE(),1 union all
select '72B15409-74B7-4185-A1DE-C3F2904E2787','CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D','4BA209D9-0244-4F37-AB47-49BB6C3D33AC',GETDATE(),1 union all
select 'A70FC1C6-8ED5-4777-9535-D8FB18146D17','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323','6CCE2683-245D-458A-B1F5-D9E1D4EA198A',GETDATE(),1
DECLARE @Responsechoice table
(
Responsechoice int,
Responseid uniqueidentifier,
QuestionId int,
Choiceid int,
IsActive bit
)
insert into @Responsechoice
select 1,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,1,1 union all
select 2,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,3,1 union all
select 3,'943B4955-BF16-4DC8-869F-9907DFC95AF7',2,0,1 union all
select 4,'943B4955-BF16-4DC8-869F-9907DFC95AF7',3,0,1 union all
select 5,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',1,1,1 union all
select 6,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',2,4,1 union all
select 7,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',3,0,1
declare @Record uniqueidentifier='DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323'
declare @TotalResponse int
select @TotalResponse= COUNT(1) from @Response where Record = @Record
;with cte
as
(
select a.Record, a.QuestionId,a.questiondetail, a.indexnumber, c.Choiceid, c.choicedetail
from
@question a
left join @choice c on c.QuestionId = a.QuestionId
where a.Record = @Record
), cte2 as(
select q.Record, q.QuestionId, q.indexnumber, rc.Choiceid, COUNT(*) [count]
from @question q
left join @Responsechoice rc on rc.QuestionId = q.QuestionId and rc.IsActive = 1
join @Response r on r.Responseid = rc.Responseid and r.IsActive = 1 -- this join just to ensure that response is active
where q.Record = @Record
group by q.Record, q.QuestionId, q.indexnumber, rc.Choiceid
), cte3 as(
select a.Record, a.QuestionId, q.questiondetail, a.indexnumber, a.Choiceid, c.choicedetail, a.[count]
from cte2 a
join @question q on q.QuestionId = a.QuestionId
join @choice c on c.Choiceid = a.Choiceid
where a.Record = @Record
), cte4 as(
select a.Record, a.QuestionId,a.questiondetail, a.indexnumber, a.Choiceid, a.choicedetail,isnull(b.[count],0)as [countofresponse],
@TotalResponse as TotalResponse
from
cte a
left join
cte3 b
on
a.ChoiceId=b.ChoiceId
), cte5 as(
select q.Record, q.QuestionId,q.indexnumber,rc.Choiceid,COUNT(*) Skipped
from @question q
left join @Responsechoice rc on rc.QuestionId = q.QuestionId
join @Response r on r.Responseid = rc.ResponseId
where
q.QuestionId in (select QuestionId from @question where Record=@Record)
and rc.ChoiceId=0
and r.IsActive=1
and rc.IsActive=1
group by q.Record, q.QuestionId, q.indexnumber, rc.ChoiceId
)
select
a.Record, a.QuestionId,a.questiondetail, a.indexnumber, a.Choiceid, a.choicedetail,a.countofresponse,
a.TotalResponse
,isnull(b.Skipped,0)as Skipped
from
cte4 a
left join
cte5 b
on
a.QuestionId=b.QuestionId
in this the question 6 was added in question table for thi record but it was not responsed in responsechoice table
when i run this proc means store proc solution the 6 must have count of 2 as sikkpped
i need output like this
Skipped
0
0
0
1
1
2
2
can u plz tell me how can i get this output
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply