SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Return a query with % values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

afeets
Starting Member

United Kingdom
10 Posts

Posted - 11/07/2007 :  06:39:43  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2007 :  07:01:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

afeets
Starting Member

United Kingdom
10 Posts

Posted - 11/07/2007 :  07:04:52  Show Profile  Reply with Quote
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

United Kingdom
10 Posts

Posted - 11/07/2007 :  08:14:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 11/07/2007 :  09:21:58  Show Profile  Reply with Quote
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

United Kingdom
10 Posts

Posted - 11/07/2007 :  10:41:43  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 11/07/2007 :  10:43:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Huh?



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

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 11/07/2007 :  12:29:45  Show Profile  Reply with Quote
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 - 11/07/2007 :  16:14:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000