How to Query Snapshot of Data in %
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 the percentages for the selected fields.
So
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. Any advice much appreciated.
|
