how to calculate percentage and count without passing parameter from this tables?

  • 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

    (

    Response uniqueidentifier,

    Record uniqueidentifier,

    crdate datetime,

    IsActive bit

    )

    insert into @Response

    select '943B4955-BF16-4DC8-869F-9907DFC95AF7','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',GETDATE(),1 union all

    select '72B15409-74B7-4185-A1DE-C3F2904E2787','CBC0CFE1-3EE9-4444-A6DA-C5B9554FF25D',GETDATE(),1 union all

    select 'A70FC1C6-8ED5-4777-9535-D8FB18146D17','DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323',GETDATE(),1

    DECLARE @Responsechoice table

    (

    Responsechoice int,

    Response 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

    by joining this four table i want to find reponsechoice count for a question depend upon the record it can be callculted

    and iam trying to get output like this

    record QuestionId questiondetailindexnumber Choiceidchoicedetailcountpercentage

    DF8BC368-68D7-4EC3

    -9E9A-5A4B19A6C3231serader 1 1a 2100

    DF8BC368-68D7-4EC3

    -9E9A-5A4B19A6C3231serader 3 3other 1 50

    CBC0CFE1-3EE9-4444

    -A6DA-C5B9554FF25D4rader1 1 7asertin 1 100

  • What "percentage" does represent? How it supposed to be calculated?

    The rest is here (you need to join just 3 of your tables):

    ;with agr_calc

    as

    (

    select q.Record, q.QuestionId, q.indexnumber, rc.Choiceid, COUNT(*) [count]

    from @question q

    join @Responsechoice rc on rc.QuestionId = q.QuestionId and rc.IsActive = 1

    join @Response r on r.Response = rc.Response and r.IsActive = 1 -- this join just to ensure that response is active

    group by q.Record, q.QuestionId, q.indexnumber, rc.Choiceid

    )

    select a.Record, a.QuestionId, q.questiondetail, a.indexnumber, a.Choiceid, c.choicedetail, a.[count]

    from agr_calc a

    join @question q on q.QuestionId = a.QuestionId

    join @choice c on c.Choiceid = a.Choiceid

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • percentage i made by calculating reponse for a question choiceid

    id depend on this two table

    @Response,@Responsechoice

    related columns

    Response

    QuestionId

    Choiceid

  • sivajii (9/27/2012)


    percentage i made by calculating reponse for a question choiceid

    id depend on this two table

    @Response,@Responsechoice

    related columns

    Response

    QuestionId

    Choiceid

    I do understand that you somehow did calculate it using data provided. But how?

    Can you provide a formula you have used, please?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • in table @Response

    in this table @Response the record id was caled two time

    u will find column Response and record

    943B4955-BF16-4DC8-869F-9907DFC95AF7 DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323

    A70FC1C6-8ED5-4777-9535-D8FB18146D17 DF8BC368-68D7-4EC3-9E9A-5A4B19A6C323

    depend on the Response the @Responsechoice

    follow a same questionid is choosen but diffrent choice are added

    in @Responsechoice

    1,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,1,1

    4,'A70FC1C6-8ED5-4777-9535-D8FB18146D17',1,1,1

    passing same questionid and same choice id

    the reponse 2 for question choice id thats what i is placed 100%

    but for this

    2,'943B4955-BF16-4DC8-869F-9907DFC95AF7',1,3,1 it is place 50%

    it depends upon the response for a question

    plz watch this percentage i mentioned in this

    and iam trying to get output like this

    record QuestionId questiondetailindexnumber Choiceidchoicedetailcountpercentage

    DF8BC368-68D7-4EC3

    -9E9A-5A4B19A6C3231serader 1 1a 2100

    DF8BC368-68D7-4EC3

    -9E9A-5A4B19A6C3231serader 3 3other 1 50

    CBC0CFE1-3EE9-4444

    -A6DA-C5B9554FF25D4rader1 1 7asertin 1 100

  • Could you simply specify a calculation formula?

    Some thing like:

    [Count Of Distinct Response Choices]/[Number Of Choices] * 100%

    I cannot see how you got your numbers of 100% and 50%

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply