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 to calculate percentage and count without passing parameter from this tables? Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 2:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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  questiondetail	indexnumber Choiceid	choicedetail	count	percentage
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323 1 serader 1 1 a 2 100
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323 1 serader 3 3 other 1 50
CBC0CFE1-3EE9-4444
-A6DA-C5B9554FF25D 4 rader1 1 7 asertin 1 100

Post #1365081
Posted Thursday, September 27, 2012 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1365123
Posted Thursday, September 27, 2012 6:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
percentage i made by calculating reponse for a question choiceid
id depend on this two table
@Response,@Responsechoice
related columns

Response
QuestionId
Choiceid

Post #1365193
Posted Thursday, September 27, 2012 7:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1365216
Posted Thursday, September 27, 2012 7:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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  questiondetail	indexnumber Choiceid	choicedetail	count	percentage
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323 1 serader 1 1 a 2 100
DF8BC368-68D7-4EC3
-9E9A-5A4B19A6C323 1 serader 3 3 other 1 50
CBC0CFE1-3EE9-4444
-A6DA-C5B9554FF25D 4 rader1 1 7 asertin 1 100

Post #1365281
Posted Thursday, September 27, 2012 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1365297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse