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 analyzeVisitId OfficerId ActivityId MethodId RoleId1, 1, KeyStage3, Visit, Consultant,2, 2, KeyStage4, Email, SIP3, 3, KeyStage4, Email, Consultant4, 1, KeyStage3, Visit, Consultant5, 1, Interview, Visit, General Inspector6, 2, KeyStage4, Telephone, SIP7, 4, KeyStage3, Email, General Inspector8, 1, KeyStage3, Other, ConsultantThe 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 wantto 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 resultOfficerId ActivityId Percentage CountOfficerVisits 1, 1, 25%, 31, 2, 25%, 31, 3, 50%, 32, 1, 50%, 22, 2, 50%, 23, 4, 100%, 2But 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/ycan 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,4Em |
|
|
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 @SampleSELECT 1, 1, 'KeyStage3', 'Visit', 'Consultant' UNION ALLSELECT 2, 2, 'KeyStage4', 'Email', 'SIP' UNION ALLSELECT 3, 3, 'KeyStage4', 'Email', 'Consultant' UNION ALLSELECT 4, 1, 'KeyStage3', 'Visit', 'Consultant' UNION ALLSELECT 5, 1, 'Interview', 'Visit', 'General Inspector' UNION ALLSELECT 6, 2, 'KeyStage4', 'Telephone', 'SIP' UNION ALLSELECT 7, 4, 'KeyStage3', 'Email', 'General Inspector' UNION ALLSELECT 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 SIPFROM ( 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 dORDER BY OfficerID[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
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/ycan 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,4Em
|
|
|
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 Text1 keystage32 keystage43 Interview4 Key IssuesThe 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
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.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
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. |
|
|
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" |
|
|
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.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|