Hierarchy in a query to get unique count

Question

I have a table that tracks the Current Work and Status of the work. So in my table column 'Current Work' captures the product name along with the different tasks being done on the product.

If the the Work has sub activities, the column 'Work Parent Name' is filled with the 'Current Work', else it remains blank if the Work is standalone. So the count needs to be taken only once either at the Work level or the sub activities level, hence cannot be used completely in an hierarchical path.

The 'Status' column is updated with the progress of either the work or the sub work.

Now, as the data is not properly structured, i am finding it a bit difficult to count the 'In Progress' status of Products. So if the status of any work or sub activity under the work is in progress, i want to count the Work to be in progress

So the output i am looking for is below :

I tried to do self join but as the sub activities are same, i am getting wrong results. Is their any way i can achieve my result from this type of data-set.

Answer 1

Ok...I am a little confused as to what you are asking. Shouldn't product 5 have in progress of 2 if you are doing a count? Or are you just trying to get a 1 if any are in progress.

Try this...

select w1.work as work, count(*) countInProgress
from work w1 
inner join work w2 on w1.work = w2.parent
where w2.status = 'In Progress'
group by w1.work

or this if you want to include the in progress of the parent as well.

select workItem, count(*) countInProgress from
(
select work, @workItem := if(parent is not null,parent,work) workItem
, status
from work
) t 
where status = 'In Progress'
group by workItem

Either way the question is a little unclear. How does product 2 have a wip task "In Progress" but is not in progress itself?

Build script

CREATE TABLE work (work varchar(20), parent varchar(20), status varchar(20));
INSERT INTO work VALUES
('Product 1',null,'In Progress'),
('Service', 'Product 1','Completed'),
('Delivery', 'Product 1', 'In Progress'),
('Transportation', 'Product 1', 'Completed'),
('Product 2',null,'In Progress'),
('Service', 'Product 2',''),
('Delivery', 'Product 2', 'In Progress'),
('Transportation', 'Product 2', ''),
('Product 3',null,'Completed'),
('Product 4',null,'In Progress'),
('Product 5',null,'In Progress'),
('Delivery', 'Product 5', 'In Progress'),
('Transportation', 'Product 5', 'In Progress')

sqlfiddle link if you want to play with it... http://sqlfiddle.com/#!9/e0fa02/15 Hope this helps...

EDIT AFTER COMMENT:

select w1.work as work
,if(w1.status = 'In Progress' or w2.status = 'In Progress',1,0) countInProgress
from work w1 
left join work w2 on w1.work = w2.parent
where w1.parent is null
group by w1.work

Give this a whirl...

Also, if you want to see where all of the sub items are marked as In Progress

select w1.work as work
,if(w1.status = 'In Progress' or w2.status = 'In Progress',1,0) countInProgress
,@allInProgress := case when @allInProgress is null and w2.status = 'In Progress' then 1
                        when @allInProgress is null and (w2.status <> 'In Progress' or w2.status is null) then 0
                        when w2.status = 'In Progress' then @allInProgress * 1
                        when w2.status <> 'In Progress' then @allInProgress * 0
                        end allInProgress
from work w1 
left join work w2 on w1.work = w2.parent
where w1.parent is null
group by w1.work

Answer 2

I was able to achieve my requirement using two sub queries and combining through union function. I am not sure if this is the best or only way, but it gives me what i wanted and fulfills my requirement.

SELECT c.work,
COUNT(
             CASE WHEN w2.status = 'In Progress'
                  THEN '1'
              END) AS inprogress_count
FROM WORK c
INNER JOIN WORK w2 ON c.work = w2.parent
GROUP BY c.work
HAVING  COUNT(
             CASE WHEN w2.status = 'In Progress'
                  THEN '1'
              END) <> 0
UNION 
SELECT w2.work,
COUNT(
            CASE WHEN w2.status = 'In Progress'
                  THEN '1'
              END) AS inprogress_count           
FROM WORK w2
WHERE WORK NOT IN (
SELECT c.work
FROM WORK c
INNER JOIN WORK w2 ON c.work = w2.parent
GROUP BY c.work
HAVING  COUNT(
             CASE WHEN w2.status = 'In Progress'
                  THEN '1'
              END) <> 0)
AND parent IS NULL
GROUP BY w2.work;