| Author |
 Topic  |
|
|
afeets
Starting Member
United Kingdom
10 Posts |
 Posted - 11/07/2007 : 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
United Kingdom
1208 Posts |
Posted - 11/07/2007 : 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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/07/2007 : 07:01:19
|
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
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
afeets
Starting Member
United Kingdom
10 Posts |
Posted - 11/07/2007 : 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
|
|
|
|
afeets
Starting Member
United Kingdom
10 Posts |
Posted - 11/07/2007 : 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. |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
afeets
Starting Member
United Kingdom
10 Posts |
Posted - 11/07/2007 : 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.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/07/2007 : 10:43:43
|
Huh?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
| |
Topic |
|
|
|
Return a query with % values
