CODEWITHSUNDEEP
sqlvisual-studio-2008reporting-services
Alex McKenzie
Alex McKenzie

Reputation: 912

How to count items by day

I'm needing to make a report from our ticket database that has the number of tickets closed per day by tech. My SQL query looks something like this:

select 
   i.DT_CLOSED,
   rp.NAME,
from INCIDENTS i
join REPS rp on (rp.ID = i.id_assignee)
where i.DT_CLOSED > @StartDate

DT_CLOSED is the date and time in ISO format, and NAME is the rep name. I also have a calculated value in my dataset called TICKETSDAY that is calcualted using =DateValue(Fields!DT_CLOSED.Value) giving me the day without the time.

Right now I have a table set up that is grouped by NAME, then by TICKETSDAY, and I would like the last column to be a count of how many tickets there are. But when I set the last column to =Count(DT_CLOSED) it lists a 1 on each row for each ticket instead of aggregating so that my table looks like this:

┌───────────┬───────────┬──────────────┐
│Name       │Day        │Tickets Closed│
├───────────┼───────────┼──────────────┤
│JOHN SMITH │11/01/2013 │             1│
│           │           ├──────────────┤
│           │           │             1│
│           │           ├──────────────┤
│           │           │             1│
│           ├───────────┼──────────────┤
│           │11/02/2013 │             1│
└───────────┴───────────┴──────────────┘

And I need it to be:

┌───────────┬───────────┬──────────────┐
│Name       │Day        │Tickets Closed│
├───────────┼───────────┼──────────────┤
│JOHN SMITH │11/01/2013 │             3│
│           ├───────────┼──────────────┤
│           │11/02/2013 │             1│
└───────────┴───────────┴──────────────┘

Any idea what I'm doing wrong? Any help would be greatly appreciated.

Upvotes: 0

Views: 101

Answers (1)

sudoNebula
sudoNebula

Reputation: 319

I believe that Marc B is correct. You need to group by the non aggregate columns in your select statement. Try something along these lines.

select 
  i.DT_CLOSED,
  rp.NAME,

  COUNT(i.ID)

from INCIDENTS i
join REPS rp on (rp.ID = i.id_assignee)
where i.DT_CLOSED > @StartDate

GROUP BY rp.NAME, i.DT_CLOSED

Without a group by to aggregate your rows together your query is counting each row distinctly. I'm unfamiliar with how the report builder works, but try adding the group by clause manually and see what you get.

Let me know if I can clarify anything.

Upvotes: 1

Related Questions