How to modify query with joins

Ignition
1

The query below gives me the 3 category’s, the sum of hours for each category and the percentage. I have created new tables for a better table structure. I am trying to add joins to this current query for the new tables.

This is the original query

Select Category,
    Hours, 
    100.0*Hours/(SUM(Hours)  OVER (Order by Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours
    From WorkOrder
    Where DateTm Between  DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by Category
) subq

This is the query I am trying to get figured out. This has the new table structure.

Select c.Category,
    Hours, 
    100.0*Hours/(SUM(Hours)  OVER (Order by c.Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select c.Category, SUM(b.Hrs) Hours
    From EquipWorkOrder a
    	Join EquipWorkOrderHrs b
    		On b.EquipWorkOrderID = a.EquipWorkOrderID
    	Join Category c
    		On c.CategoryID = a.CategoryID
    	Join Equipment d
    		On d.EquipmentID = a.EquipmentID
    Where a.DateTm Between  DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by c.Category
) subq

When I run the new query I get a error which states " The multi-part identifier c.Category could not be bound"

2

Does your Category table have a Category column?

3

yes it is also named Category

4

Try fixing your join on EquipWorkOrderHrs first. That might be throwing off the query optimizer.

5

I fixed the join but I still get the same error

6

In the outer query, it should be subq.Category.

7

I tried this but It gives me a syntax for " .". I am assuming because It doesn’t know what table Category is in. But when I add the letter for what table it is in I still get a syntax error

Select c.Category,
    Hours, 
    100.0*Hours/(SUM(Hours)  OVER (Order by c.Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select c.Category, SUM(b.Hrs) Hours
    From EquipWorkOrder a
    	Join EquipWorkOrderHrs b
    		On b.EquipWorkOrderID = a.EquipWorkOrderID
    	Join Category c
    		On c.CategoryID = a.CategoryID
    	Join Equipment d
    		On d.EquipmentID = a.EquipmentID
    Where a.DateTm Between  DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by c.Category
) subq.Category
8

No, in the top three lines.

9

In the first Select?

10

Yes, your aliases ‘a’, ‘b’, ‘c’, and ‘d’ are only valid inside the subquery. The output columns of the subquery are then associated with alias you give the subquery itself.

11

I see! That worked

Select subq.Category,
    Hours, 
    100.0*Hours/(SUM(Hours)  OVER (Order by subq.Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select c.Category, SUM(b.Hrs) Hours
    From EquipWorkOrder a
    	Join EquipWorkOrderHrs b
    		On b.EquipWorkOrderID = a.EquipWorkOrderID
    	Join Category c
    		On c.CategoryID = a.CategoryID
    	Join Equipment d
    		On d.EquipmentID = a.EquipmentID
    Where a.DateTm Between  DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by c.Category
) subq