complicated query... vincent? :)

Anon

Here is a very very simplified schema i have, it deals with all the data i'll be using

table: group

group_id INT primary key auto_inc
group_name varchar(128)

table: request

request_id INT primary key auto_inc
start_date datetime
close_date datetime
status enum('open','closed')

table: thread

thread_id INT primary key auto_inc
request_id INT
group_id

OK... I need a query that will show all groups (so i guess it will be a LEFT JOIN),

it should return, group_name, then the number of requests that are open and have been for 0-2 days, the number of requests that are open and have been for 3-5 days, 6-10, 10+, then the same 4 for closed requests.

so a group can have multiple threads of a given request... it should only could as 1.

I'm using MySQL. is this possible??

I mentioned vincent, because he wrote a FAQ article about hotel rooms that was similar but only used 2 tables.

bfranco

you want to count all that using just 1 query???
and you want to join all the counts with the group table??? (by the way, group is a reserved word).

Vincent, I wanna see how you solve that. It's easy, at least michael didn't ask for any ordering : )

brandonschnell

this might be easier and faster with a couple of seperate queries.

if you want one query then create an aliased table for each of the WHERE statements on the dates. use a natural join between the aliased tables and the original table on the group name/id.

http://www.mysql.com/doc/M/u/Multiple_tables.html

tasistro

you could start with something like this:

select if(c_fecha>"2001-02-02",1,0) as date1 , if(c_fecha<"2001-02-02",1,0) as date2, if(c_fecha="2001-02-02",1,0) as date3 from movimientos order by date1,date2,date3

so you create a column with a one that states in which time frame it is in (like date>%datemin% and date<%datemax% then set 1 in column for 2 days old or 5 or whatever) then add the join to the group and finally add the group by clause.

Saludos
Gerardo

tomhath

Can't see how to do it in one query without a subselect to get rid of duplicate group-id to request_id pairs in thread. This does everything else, just replace the thread request join with the temp table and add a couple more cases to get the four counts:

select g.group_name, r.status,
sum(case when TO_DAYS(r.close_date) - TO_DAYS(r.start_date) <= 2 then 1 else 0 end) '0-2',
sum(case when TO_DAYS(r.close_date) - TO_DAYS(r.start_date) > 2 then 1 else 0 end) '>2'
from groups g
left join thread t
on g.group_id = t.group_id
left join request r
on t.request_id = r.request_id
group by g.group_name, r.status

tomhath

I suppose instead of close_date it should have an isnull to handle open requests:

TO_DAYS(isnull(curdate(),r.close_date)) - TO_DAYS(r.start_date)

Anon

thanks for the help so far...

this is what i'm trying:

select g.group_name,
sum(case when TO_DAYS(r.close_date) - TO_DAYS(r.start_date) <= 2 then 1 else 0 end) '02',
sum(case when (TO_DAYS(r.close_date) - TO_DAYS(r.start_date) > 2) AND (TO_DAYS(r.close_date) - TO_DAYS(r.start_date) < 6) then 1 else 0 end) '35',
sum(case when (TO_DAYS(r.close_date) - TO_DAYS(r.start_date) > 5) AND (TO_DAYS(r.close_date) - TO_DAYS(r.start_date) < 10) then 1 else 0 end) '610',
sum(case when TO_DAYS(r.close_date) - TO_DAYS(r.start_date) >= 10 then 1 else 0 end) '10plus'
FROM help_desk_group g left join help_desk_thread t on g.help_desk_group_id = t.help_desk_group_id left join help_desk_request r on t.request_id = r.request_id
WHERE r.status='closed' OR r.status='completed' OR r.status IS NULL
GROUP BY g.group_name

to just get the closed requests... the problem is it is returning to high numbers in each category... remember that there can be multiple threads for each request within one group. could that be the problem?

thanks

tomhath

Your "where" clause looks like it will get open, closed, and null requests, that might be part of it.

But your counts will be high unless you get the distinct group/request pairs from teh thread table. In mysql I think you are going to have to create a temporary table because you can use EXISTS.

In SQLServer SQL it would be something like this (didn't try it out in mysql):

select distinct help_desk_group_id, request_id into #temp
from help_desk_thread

Then use #temp in the join instead of help_desk_thread

Anon

actually i think it is working now...

here is my code that i'm using now:

$result = mysql_query("select g.help_desk_group_id,
sum(case when 0 - TO_DAYS(r.start_date) <= 2 then 1 else 0 end) '02',
sum(case when (0 - TO_DAYS(r.start_date) > 2) AND (0 - TO_DAYS(r.start_date) < 6) then 1 else 0 end) '35',
sum(case when (0 - TO_DAYS(r.start_date) > 5) AND (0 - TO_DAYS(r.start_date) < 10) then 1 else 0 end) '610',
sum(case when 0 - TO_DAYS(r.start_date) >= 10 then 1 else 0 end) '10plus'
FROM help_desk_group g left join help_desk_thread t on g.help_desk_group_id = t.help_desk_group_id left join help_desk_request r on t.request_id = r.request_id
WHERE r.start_date IS NOT NULL AND r.close_date IS NULL
GROUP BY g.help_desk_group_id") or die(mysql_error());
while(list($help_desk_group_id, $v1, $v2, $v3, $v4) = mysql_fetch_row($result)) {
$group_array[$help_desk_group_id][1] = $v1;
$group_array[$help_desk_group_id][2] = $v2;
$group_array[$help_desk_group_id][3] = $v3;
$group_array[$help_desk_group_id][4] = $v4;
}

this should get all the open requests, and seems to sum up correctly.

my problem before is that i was looking at the wrong table... 1 group had 12 threads open, but they had 8 other threads closed where the request was still open (multiple groups were working on the same request)

this is all for a very cool help desk system i made for our companies intranet... the IT director just wanted a report to see how each department was handling requests, and how they were being rated. there is a feedback system tied into it, and i used a similar query to get stats on how each department did on average, and then broke it down by rating category (timeliness, results, attitude, etc...)