how can i get count of sikped question depend record column and collectionofResponse column?

  • 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

  • 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;-)

  • 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

  • can any plz try to solve my issue in the proc

  • 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/

  • 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