Finding tickets assigned to 'root' on any given day

[gbeers]

Hi everybody,

This is my first post here so I apologize in advance if I don't provide as much information as needed.

What I'm looking to do is find out how many tickets were assigned to the root user on any given day in the past via a SQL Query. I've got everything else that I need figured out but I can't come up with a reliable way to determine which tickets are assigned to the root on a date in the past. A simple query such as:
SELECT COUNT(*) FROM otrs.ticket as ticket_0 WHERE (((ticket_0.ticket_state_id=4 OR ticket_0.ticket_state_id = 1) AND ticket_0.queue_id=2) and responsible_user_id = 1) returns how many tickets are assigned to the root as of right now in any given queue. But, as I mentioned I don't want to always necessarily know the information for only today. I'm keeping a spreadsheet with various statistics on it and don't want to have to run my query every day if I can avoid it. So, if I want to run it once per week I'd need to figure out how many tickets were assigned to the root on Monday night, Tuesday night, etc. I have all the rest of the code I need except this query.

If anybody can provide some insight it would be appreciated. I've dabbled around with the ticket_history table but I don't think I'm getting the right results. For example, I know that history_type_id of 23 is the owner update listing so I can take a distinct list of items on a given day and see which of them don't have a history_type_id of 23 but that often produces a number that is higher than the amount of ticket remaining in the queue for that day (ie 5 tickets remain but my query would say that there are 8 tickets assigned to root which obviously isn't correct).

If you need more information please let me know.

Thanks in advance

[crythias]

For any other information, you'd probably also want to SELECT the field that holds the create date. You could SELECT DAYNAME(createdatefield) as gd WHERE createdatefield between range group by gd

I apologize if this is pseudocode at the moment. I think you might be close to figuring out what I mean..
http://dev.mysql.com/doc/refman/5.1/en/ ... on_dayname

[gbeers]

Yes, I've used those style for pretty much all of my other queueries. The actual query I'm working with now is:

Select count(distinct ticket_id) from otrs.ticket_history WHERE create_time < '2010-07-17' and create_time > '2010-07-16' and owner_id = 1 and queue_id = 2

I'm pulling in the dates from variables provided by my excel sheet to give me the tickets created on any given date but the problem is the responsible_user_id on pretty much any date in the past will not be '1' any more because one of the users has taken ownership of the ticket and most likely it's closed. The only way I could think of was to try to take all the tickets opened on a given day and finding out which ones haven't had an ownership update done to them on that date. That's the query that I can't figure out though. To give you specifics on my case the query above returns 37.

This query returns 30:
Select count(distinct ticket_id) from otrs.ticket_history WHERE create_time < '2010-07-17' and create_time > '2010-07-16' and owner_id = 1 and queue_id = 2 and history_type_id = '23'

Theoretically that should mean that 37 tickets were opened and 7 of them were unassigned. The problem with that is that at the end of that day there were only 4 tickets open (this I know is accurate from running daily stats). So, there couldn't have possibly be 7 tickets assigned to the root when only 4 were open. I'm clearly missing something but can't quite figure out what it is.

[gbeers]

Thanks for the help Crythias. I was able to figure out what I wanted. To help others along that may be looking for the same thing the code is:

select max(id),max(owner_id), ticket_id, max(queue_id) from ticket_history
where create_time between '2010-08-12' and '2010-08-13' group by ticket_id having max(owner_id) = 1 and max(queue_id) = 2