Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Return a query with % values

Author  Topic 

afeets
Starting Member

10 Posts

Posted - 2007-11-07 : 06:39:43
Below is sample data from a table, that I have been asked to analyze

VisitId OfficerId ActivityId MethodId RoleId
1, 1, KeyStage3, Visit, Consultant,
2, 2, KeyStage4, Email, SIP
3, 3, KeyStage4, Email, Consultant
4, 1, KeyStage3, Visit, Consultant
5, 1, Interview, Visit, General Inspector
6, 2, KeyStage4, Telephone, SIP
7, 4, KeyStage3, Email, General Inspector
8, 1, KeyStage3, Other, Consultant

The purpose of this table is that it records the visits of officers to sites where they do inspections.

What I have been asked to do is to create a report giving me a return like below where I can group an officers visits and then view there selections as percentages for the selected fields.

So for example showing two selections from the three fields, I want
to achieve

Activity Method Role
----------------------------------------------------------
OfficerId Keystage3 KeyStage4 Visit Email Consultant SIP
----------------------------------------------------------
1, 25%, 25%, 10%, 50%, 25%, 75%,
2, 5%, 10%, 5%, 10%, 33%, 25%,
3, 10%, 5%, 33%, 10%, 10%, 10%,


Now I can achieve the following result

OfficerId ActivityId Percentage CountOfficerVisits
1, 1, 25%, 3
1, 2, 25%, 3
1, 3, 50%, 3
2, 1, 50%, 2
2, 2, 50%, 2
3, 4, 100%, 2

But then I add the MethodId field it obviously compares activity & method fields expanding the recordset, returning not six rows perhaps 10 instead.

What is the best way to achieve my goal. I am thinking I need to do a query for each of the fields export the recordset to excel and then try to marry the data together. I would like to automate the process though, so any advice much appreciated.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-07 : 06:58:51
i'm not clear from your example where you are getting your figures from.
what do you mean by "...view there selections as percentages for the selected fields"? percentages of what? i.e. x/y
can you post the sql you've used so far? show the expected output based on the sample data you gave originally?

in the 2nd resultset you posted where does activityid come from? originally it was in the form of 'keystage3' etc... now it is showing as 1,2,3,4

Em
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 07:01:19
[code]DECLARE @Sample TABLE (VisitID TINYINT, OfficerID TINYINT, ActivityID VARCHAR(40), MethodID VARCHAR(40), RoleID VARCHAR(40))

INSERT @Sample
SELECT 1, 1, 'KeyStage3', 'Visit', 'Consultant' UNION ALL
SELECT 2, 2, 'KeyStage4', 'Email', 'SIP' UNION ALL
SELECT 3, 3, 'KeyStage4', 'Email', 'Consultant' UNION ALL
SELECT 4, 1, 'KeyStage3', 'Visit', 'Consultant' UNION ALL
SELECT 5, 1, 'Interview', 'Visit', 'General Inspector' UNION ALL
SELECT 6, 2, 'KeyStage4', 'Telephone', 'SIP' UNION ALL
SELECT 7, 4, 'KeyStage3', 'Email', 'General Inspector' UNION ALL
SELECT 8, 1, 'KeyStage3', 'Other', 'Consultant'

SELECT OfficerID,
Keystage3 / Total AS Keystage3,
Keystage4 / Total AS Keystage4,
Visit / Total AS Visit,
Email / Total AS Email,
Consultant / Total AS Consultant,
SIP / Total AS SIP
FROM (
SELECT OfficerID,
SUM(CASE WHEN ActivityID = 'KeyStage3' THEN 100.0 ELSE 0.0 END) AS Keystage3,
SUM(CASE WHEN ActivityID = 'KeyStage4' THEN 100.0 ELSE 0.0 END) AS Keystage4,
SUM(CASE WHEN MethodID = 'Visit' THEN 100.0 ELSE 0.0 END) AS Visit,
SUM(CASE WHEN MethodID = 'Email' THEN 100.0 ELSE 0.0 END) AS Email,
SUM(CASE WHEN RoleID = 'Consultant' THEN 100.0 ELSE 0.0 END) AS Consultant,
SUM(CASE WHEN RoleID = 'SIP' THEN 100.0 ELSE 0.0 END) AS SIP,
COUNT(*) AS Total
FROM @Sample
GROUP BY OfficerID
) AS d
ORDER BY OfficerID[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

afeets
Starting Member

10 Posts

Posted - 2007-11-07 : 07:04:52
quote:
Originally posted by elancaster

i'm not clear from your example where you are getting your figures from.
what do you mean by "...view there selections as percentages for the selected fields"? percentages of what? i.e. x/y
can you post the sql you've used so far? show the expected output based on the sample data you gave originally?

in the 2nd resultset you posted where does activityid come from? originally it was in the form of 'keystage3' etc... now it is showing as 1,2,3,4

Em

Go to Top of Page

afeets
Starting Member

10 Posts

Posted - 2007-11-07 : 08:14:02
Apologies for failing to explain properly what I hope to achieve.
The reason I entered the sample table with txt rather than id's is so you could maybe comprehend what the database is for.

tblActivity
-----------
ActivityID Text
1 keystage3
2 keystage4
3 Interview
4 Key Issues

The second recordset was NOT the results of a query, just %'s entered so you could see that I wanted something similar to a cross tab query.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-07 : 09:21:58
Read the links in my signature, and ask again.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

afeets
Starting Member

10 Posts

Posted - 2007-11-07 : 10:41:43
Thank you very much for your answer, much more appreciated than some smart arse, sending you links on what SQL is.

I didn't know you could declare a variable as a table, and running your script does seem to give me the answers i'm looking for.

You can obviously guess that I am novice at this sort of stuff, but getting replies from you rather than the latter makes me want to develop.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 10:43:43
Huh?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-07 : 12:29:45
If you bothered to read the FIRST LINK in my sig, it tells you EXACTLY how to ask a question, so that you will get an answer fast. Dumbasses like you make me not want to post.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-07 : 16:14:29
quote:
Originally posted by afeets

getting replies from you rather than the latter makes me want to develop.



that's very funny



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
   

- Advertisement -