Author |
Topic |
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-11 : 10:33:04
|
I want to count tasks complete for staff members and list them by task, so the following;SELECT Task, count(*)FROM WorkloadWHERE Staff_member = "Joe Bloggs"GROUP BY TaskHow can I repeat this in a second column, with each column looking at only a specific month in my Date column (which contains months). |
|
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-11 : 11:14:47
|
To paraphrase, I have the following:SELECT Task, Count(Task) as MarchFROM CompletedWHERE datepart('m',Required_Date) = 3GROUP BY Task_Main_CatGives me a column with the names of the Tasks and a column with the count of those Tasks required in March.How do I put another column next to this with the same count but for April? |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-11 : 11:55:16
|
SELECT Task,DateName(month,getdate()) as [Month], Count(Task)FROM CompletedGROUP BY Task,DateName(month,getdate())CheersMIK |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-11 : 11:55:16
|
SELECT Task,DateName(month,getdate()) as [Month], Count(Task)FROM CompletedGROUP BY Task,DateName(month,getdate())CheersMIK |
|
|
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-11 : 15:44:42
|
Thanks but that's not quite what I was looking for.I want in column one a list of all the tasks, in column 2 the number of times those tasks were completed in month1, and in column 3 the number of times they were completed in month2. So the headers for the columns would read Task, February, March (for example) and the second and third columns contain counts of the tasks. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-11 : 16:26:45
|
It sounds like you want a Pivot (http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx). But it's hard to tell without sample data so, plesae post sample data and expected output in a consumable format. Here are some links to show you how to perpare your question so we can help you better:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-12 : 03:20:22
|
I've had a good look over those two links and I can't see any instruction as to how I insert a table into a forum post.. I can do a little html but I don't want to try inserting it straight in here only for it not to interpret. I want to be able to show you the table of data I'm starting with and then what I'd like to end up with. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-12 : 03:25:45
|
Generate script of that table from SSMS --> copy & Paste in the forum postRight-click on Database --> Generate Scripts --> Select only specific objects --> Tables (select only the specific table names) --> choose Schema and data under options |
|
|
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-12 : 03:32:44
|
I start with the following table:task date-------------------------Analysis JanuaryAnalysis FebruaryReport JanuaryReport JanuaryAnd I want to produce the following table:Task January February-----------------------Analysis 1 1Report 2 0 |
|
|
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-12 : 03:33:53
|
I'm not sure how to use SSMS, I'm linking tables from one excel file to another. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-12 : 03:38:55
|
[code]SELECT task ,COUNT(CASE WHEN date = 'January' then task END) JanCount ,COUNT(CASE WHEN date = 'February' then task END) FebCountFROM TableNameGROUP BY task[/code] |
|
|
stoolpidgeon
Starting Member
28 Posts |
Posted - 2013-04-12 : 04:36:41
|
Thanks but I get the following error:Incorrect column expression: 'COUNT(CASE WHEN datepart('m', date) = 1 then task END)' |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-12 : 04:42:10
|
quote: Originally posted by stoolpidgeon Thanks but I get the following error:Incorrect column expression: 'COUNT(CASE WHEN datepart('m', date) = 1 then task END)'
It should beCOUNT(CASE WHEN datepart(m, date) = 1 then task END) AS 'January'--Chandu |
|
|
|