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
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Counts and a score
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emailuser
Yak Posting Veteran

74 Posts

Posted - 02/17/2014 :  12:52:43  Show Profile  Reply with Quote
Hi , as always I appreciate any help .. heres my scenario

I have a table called enablers , with the following data

title Raiser Assignedto
book Fred John
Apple Peter Peter
Orange Bill Roger
Cup John Fred

For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)

I am looking how to get the output like below
Name Total Points
Fred 2
Peter 1
Bill 1
John 2
Roger 1

Any help greatly appreciated as always :)

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/17/2014 :  13:29:46  Show Profile  Reply with Quote
Here is one way:
DECLARE @Foo TABLE (title VARCHAR(50), Raiser VARCHAR(50), Assignedto VARCHAR(50))

INSERT @Foo
VALUES
('book', 'Fred', 'John'),
('Apple', 'Peter', 'Peter'),
('Orange', 'Bill', 'Roger'),
('Cup', 'John', 'Fred')

SELECT
	Name,
	SUM(Points) AS TotalPoints
FROM	
	(
		SELECT
			Raiser AS Name,
			COUNT(Raiser) AS Points
		FROM
			@Foo
		GROUP BY
			Raiser

		UNION ALL 

		SELECT
			AssignedTo AS Name,
			COUNT(AssignedTo)
		FROM
			@Foo
		WHERE
			CASE WHEN Raiser <> Assignedto THEN Assignedto ELSE NULL END IS NOT NULL
		GROUP BY
			AssignedTo
	) AS T
GROUP BY
	Name
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 02/18/2014 :  04:09:56  Show Profile  Reply with Quote
Hi Lamprey thank you for the answer .. the table has over 500 lines of data , and the above data , book, Fred , John is fictious .. what do I need to enter for values ?? the column names ? title, raiser , assignedto ???
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/18/2014 :  06:32:33  Show Profile  Reply with Quote
Just you have to replace @Foo with your table name. In the Lamprey's SQL, @Foo holds temporary table data...

SELECT
	Name,
	SUM(Points) AS TotalPoints
FROM	
	(
		SELECT
			Raiser AS Name,
			COUNT(Raiser) AS Points
		FROM
			@Foo YourTableName
		GROUP BY
			Raiser

		UNION ALL 

		SELECT
			AssignedTo AS Name,
			COUNT(AssignedTo)
		FROM
			@Foo YourTableName
		WHERE
			CASE WHEN Raiser <> Assignedto THEN Assignedto ELSE NULL END IS NOT NULL
		GROUP BY
			AssignedTo
	) AS T
GROUP BY
	Name



--
Chandu
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 02/18/2014 :  07:14:14  Show Profile  Reply with Quote
Hi Bandi , thanks I am getting somewhere now , just 2 more tweaks and I think I am there , forgot to add , that the data I have has another 2 columns called Type and CreateDate , so I need to trim the results to only ones created this year and then trim the results to have only Type='Kaizen' , I need to add a where statement for these but cannot see where in the code to add ... any help again really appreciated
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/18/2014 :  07:46:38  Show Profile  Reply with Quote
SELECT
	Name,
	SUM(Points) AS TotalPoints
FROM	
	(
		SELECT
			Raiser AS Name,
			COUNT(Raiser) AS Points
		FROM
			@Foo YourTableName
                WHERE Type='Kaizen' AND YEAR( CreateDate ) = YEAR(GETDATE())
		GROUP BY
			Raiser

		UNION ALL 

		SELECT
			AssignedTo AS Name,
			COUNT(AssignedTo)
		FROM
			@Foo YourTableName
		WHERE  Type='Kaizen' AND YEAR( CreateDate ) = YEAR(GETDATE()) AND 
			CASE WHEN Raiser <> Assignedto THEN Assignedto ELSE NULL END IS NOT NULL
		GROUP BY
			AssignedTo
	) AS T

GROUP BY
	Name


--
Chandu

Edited by - bandi on 02/18/2014 07:47:15
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 02/18/2014 :  09:20:41  Show Profile  Reply with Quote
Bandi ... many ,many thanks ... that's nearly there ... though I do receive an error message The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I guess its because my CreateDate is a char data type .... anyway to get around this ???
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/18/2014 :  10:08:49  Show Profile  Reply with Quote
Either change your Createdate to a datetime (the best answer) Or convert your Char to a datetime in the query (will affect performance slightly).
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/20/2014 :  03:35:45  Show Profile  Reply with Quote
If your CreateDate column data format is YYYY-MM-DD , you can do as follows:

CreateDate LIKE DATENAME( YYYY, getdate()) + '%'

The above code depends on CreateDate format...

Refer
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000