==========================
select g.help_desk_group_id,
sum(case when TO_DAYS(CURRENT_DATE) - TO_DAYS(r.start_date) <= 2 then 1 else 0 end) '02',
sum(case when (TO_DAYS(CURRENT_DATE) - TO_DAYS(r.start_date) > 2) AND (TO_DAYS(CURRENT_DATE) - TO_DAYS(r.start_date) < 6) then 1 else 0 end) '35',
sum(case when (TO_DAYS(CURRENT_DATE) - TO_DAYS(r.start_date) > 5) AND (TO_DAYS(CURRENT_DATE) - TO_DAYS(r.start_date) < 10) then 1 else 0 end) '610',
sum(case when TO_DAYS(CURRENT_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.start_date IS NOT NULL AND r.close_date IS NULL
GROUP BY g.help_desk_group_id
this query basically just counts the open requests, and how old they are, and groups them into one of 4 categories. the problem is that if one group has 2 threads (or more) for a given request, it counts all the threads, when all i want is just to count the request once.
anyone want to help? please?! thanks.
