So, I have 3 tables that I am attempting to get counts for based on a groupid, and a task code. There are a few issues I am having as some of the relationships are many to one, which I think is somehow inflating my counts. I will list my 3 tables with the pertinent attributes.
task_code - would like to get the counts of each one in a group id, would like to use the latest instance basedon event date.
sol_id -used to join to worktable; many sol_id to one m_id is possible
edate -need to use to get one record
cur_id - where cur_id = 1 in the where clause
sol_id - used to join to task_table
m_id - used to join to grouptable
groupid- used to group the task_code to get count
I'd like the end result to look like:
I have been attempting to run a number of queries, but the counts I am getting back do not look correct. I am concerned that it is somehow returning more than one instance of the m_id somehow? Here is the query in question:
select c.groupid, count(c.groupid) group_count, a.task_code from task_table a join worktable b on a.sol_id = b.sol_id join grouptable c on b.m_id= c.m_id where a.cur_id = 1 and a.task_code is not null group by c.groupid, a.task_code;
If I add 'edate = (select max(edate) from task_table)' in the where clause, it returns an empty table.
I am unsure how to incorporate edate to get only the newest record that fits the criteria in the where clause. The reason I think I want to use this is because there could be more than one sol_id that is associated with a m_id, so i'd just like to include only the newest record with a cur_id in the count. Thank you for your time.
looking at these tables, the result should look like the following
A76 should only count
sol_id 23 and 42) (
Y23 should only count