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