brettr
asked on
How to group by date?
In the following, how do I get a count for each unique day entry on a ticketid for an employee?
In the example below, empid=1 and ticketid=7 would have a count of 3, since it was worked on three different days. However, empid=1 and ticketid=9 is just one day, since both entries were on 9/17. empid=3 and ticketid=9 is also one day since both entries were on 9/17 as well.
In the example below, empid=1 and ticketid=7 would have a count of 3, since it was worked on three different days. However, empid=1 and ticketid=9 is just one day, since both entries were on 9/17. empid=3 and ticketid=9 is also one day since both entries were on 9/17 as well.
Id empid ticketid datecomplete ticketsession
4 1 9 2011-09-17 9:12:24.000 3
4 1 9 2011-09-17 9:12:24.000 3
10 1 7 2011-09-15 9:12:24.000 7
19 1 7 2011-09-16 9:12:24.000 7
20 1 7 2011-09-17 11:17:24.000 7
21 3 9 2011-09-17 9:12:24.000 3
21 3 9 2011-09-17 10:12:24.000 3ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Add the counts to your select query
select empid, count(empid) [Employees], count(tickectid) [tickectid], CONVERT(varchar(10), datecomplete, 101) datecomplete
from table1
group by empid, tickectid, CONVERT(varchar(10), datecomplete, 101)ASKER
That just counts each single row. So instead of getting 3 for 9/15, 9/16 and 9/17, which were all on the same ticket, you can 1 for each.
Right, you only need one count but group by the two fields
select empid, count(tickectid) [Tickect Count], CONVERT(varchar(10), datecomplete, 101) datecomplete
from table1
group by empid, tickectid, CONVERT(varchar(10), datecomplete, 101)ASKER
ok, thanks.
ASKER