Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how can i get count of sikped question depend record column and collectionofResponse column? Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 2:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:11 AM
Points: 212, Visits: 1,385
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
DF8BC368 1 serader 1 1 a 2 2 0
DF8BC368 1 serader 1 2 b 0 2 0
DF8BC368 1 serader 1 3 others 1 2 0
DF8BC368 2 serader1 2 4 rader1 1 2 1
DF8BC368 2 serader1 2 5 rader2 0 2 1
DF8BC368 3 serader2 3 6 a 0 2 2



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



Post #1409881
Posted Tuesday, January 22, 2013 4:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1409919
Posted Tuesday, January 22, 2013 4:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:11 AM
Points: 212, Visits: 1,385
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
Post #1409928
Posted Tuesday, January 22, 2013 7:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:11 AM
Points: 212, Visits: 1,385
can any plz try to solve my issue in the proc
Post #1410032
Posted Tuesday, January 22, 2013 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1410056
Posted Wednesday, January 23, 2013 8:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:11 AM
Points: 212, Visits: 1,385
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
Post #1410629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse