Author |
Topic |
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2014-02-17 : 12:52:43
|
Hi , as always I appreciate any help .. heres my scenarioI have a table called enablers , with the following datatitle Raiser Assignedtobook Fred JohnApple Peter PeterOrange Bill RogerCup John FredFor 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 belowName Total PointsFred 2Peter 1Bill 1John 2Roger 1 Any help greatly appreciated as always :) |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-02-17 : 13:29:46
|
Here is one way:DECLARE @Foo TABLE (title VARCHAR(50), Raiser VARCHAR(50), Assignedto VARCHAR(50))INSERT @FooVALUES('book', 'Fred', 'John'),('Apple', 'Peter', 'Peter'),('Orange', 'Bill', 'Roger'),('Cup', 'John', 'Fred')SELECT Name, SUM(Points) AS TotalPointsFROM ( 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 TGROUP BY Name |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2014-02-18 : 04:09:56
|
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 ??? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-02-18 : 06:32:33
|
[code]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 TotalPointsFROM ( 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 TGROUP BY Name [/code]--Chandu |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2014-02-18 : 07:14:14
|
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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-02-18 : 07:46:38
|
[code]SELECT Name, SUM(Points) AS TotalPointsFROM ( 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 TGROUP BY Name[/code]--Chandu |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2014-02-18 : 09:20:41
|
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 ??? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2014-02-18 : 10:08:49
|
Either change your Createdate to a datetime (the best answer) Or convert your Char to a datetime in the query (will affect performance slightly). |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|