September 27, 2012 at 2:01 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
(
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
September 27, 2012 at 3:56 am
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
September 27, 2012 at 6:30 am
percentage i made by calculating reponse for a question choiceid
id depend on this two table
@Response,@Responsechoice
related columns
Response
QuestionId
Choiceid
September 27, 2012 at 7:02 am
sivajii (9/27/2012)
percentage i made by calculating reponse for a question choiceidid 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?
September 27, 2012 at 7:58 am
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
September 27, 2012 at 8:19 am
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%
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply