Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ljw121
Starting Member
3 Posts |
 Posted - 2009-06-27 : 10:41:31
|
| Hi,As an overview of what I'm trying to: I've got a table which has got the following columns:JobNumber | JobTitle | JobBy | JobDateAnd this could filled out as follows:JobNumber | JobTitle | JobBy | JobDate------------------------------------------1 | Example 1| Greg | 2009-07-012 | Example 2| Fred | 2009-07-023 | Example 3| Fred | 2009-07-014 | Example 4| Greg | 2009-07-035 | Example 5| Greg | 2009-07-016 | Example 6| Fred | 2009-07-017 | Example 7| Greg | 2009-07-02The table is self explanatory and shows when jobs are scheduled for completion and who by. Now what I want to know is how much work each person has scheduled on a certain day. My ideal output would be something like this:JobDate | Fred | Greg-------------------------2009-07-01 | 2 | 22009-07-02 | 1 | 12009-07-02 | 0 | 1So far by experimenting with Count and Group By I've been able to get this information by running two individual selects which is okay if there are only two people but if there are a lot of people then this becomes impractical. Is there a way to get this information as I've displayed it?Any help would be much appreciated!! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-27 : 10:53:02
|
[code]select JobDate, Fred = count(case when JobBy = 'Fred' then 1 end), Greg = count(case when JobBy = 'Greg' then 1 end)from Jobsgroup by JobDate[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
TheSQLGuru
SQL Server MVP
10 Posts |
Posted - 2009-07-05 : 10:53:18
|
| the given example works if the values you want to count by are known and fixed. Otherwise, search the web for sql server dynamic crosstab to find solutions for that problem.Kevin G BolesTheSQLGuruIndicium Resources, Inc. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-05 : 12:58:02
|
| here is onehttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx |
|
|
|
|
|
|
Multiple Counts