Divide selected value by count(*)

Question

I have a Microsoft SQL Server with the following tables:

• Projects
• BookedHours (with fk_Project = Projects.ID)
• Products
• ProjectsToProducts (n:m with fk_Projects = Projects.ID and fk_Products = Products.ID)

I now want to select how many hours are booked to which product per month. The problem is, that one project can have multiple products (that's why I need the n:m table).

If I do the following, it will count the hours twice if a project has two products.

SELECT 
    P.ID AS fk_Product, MONTH(B.Datum) AS Monat, SUM(B.Hours) AS Stunden
FROM 
    tbl_BookedHours AS B 
INNER JOIN 
    tbl_Projects AS M on B.fk_Project = M.ID            
INNER JOIN 
    tbl_ProjectProduct AS PP ON PP.fk_Project = M.ID
INNER JOIN 
    tbl_Products AS P ON PP.fk_Product = P.ID               
WHERE 
    YEAR(B.Datum) = 2020
GROUP BY 
    P.ID, MONTH(B.Datum)
ORDER BY 
    P.ID, MONTH(B.Datum)

I can get the number of products for each project with this SQL:

SELECT fk_Project, COUNT(*) AS Cnt
FROM tbl_ProjectProduct
GROUP By fk_MainProject

But how can I now divide the hours for each project by its individual factor and add it all up per product and month?

I could do it in my C# program or I could use a cursor and iterate through all projects, but I think there should be an more elegant way.

Edit with sample data:

|----------------|   |----------------|   |------------------------------| 
| tbl_Projects   |   | tbl_Products   |   | tbl_ProjectProduct           |
|----------------|   |----------------|   |------------------------------|
| ID | Name      |   | ID | Name      |   | ID | fk_Project | fk_Product |
|----+-----------|   |----+-----------|   |------------------------------|
|  1 | Project 1 |   |  1 | Product 1 |   |  1 | 1          | 1          |
|  2 | Project 2 |   |  2 | Product 2 |   |  2 | 1          | 2          |
|  3 | Project 3 |   |  3 | Product 3 |   |  3 | 2          | 1          |
|  4 | Project 4 |   |  4 | Product 4 |   |  4 | 3          | 3          |
|----------------|   |----------------|   |  5 | 4          | 1          |
                                          |  6 | 4          | 2          |
                                          |  7 | 4          | 4          |
                                          |------------------------------|

|--------------------------------------|
| tbl_BookedHours                      |
|--------------------------------------|
| ID | fk_Project | Hours | Date       |
|--------------------------------------|
|  1 | 1          | 10    | 2020-01-15 |
|  2 | 1          | 20    | 2020-01-20 |
|  3 | 2          | 10    | 2020-01-15 |
|  4 | 3          | 30    | 2020-01-18 |
|  5 | 2          | 20    | 2020-01-20 |
|  6 | 4          | 30    | 2020-01-25 |
|  7 | 1          | 10    | 2020-02-15 |
|  8 | 1          | 20    | 2020-02-20 |
|  9 | 2          | 10    | 2020-02-15 |
| 10 | 3          | 30    | 2020-03-18 |
| 11 | 2          | 20    | 2020-03-20 |
| 12 | 4          | 30    | 2020-03-25 |
|--------------------------------------|

The Result should be:

|----------------------------|
| fk_Product | Month | Hours |
|----------------------------|
| 1          | 1     | 55    |
| 2          | 1     | 25    |
| 3          | 1     | 30    |
| 4          | 1     | 10    |
| 1          | 2     | 25    |
| 2          | 2     | 15    |
| 1          | 3     | 30    |
| 2          | 3     | 10    |
| 3          | 3     | 30    |
| 4          | 3     | 10    |
|----------------------------|

For example booking Nr. 1 has to be divided by 2 (because Project 1 has two products) and one half of amount added to Product 1 and the other to Product 2 (Both in January). Booking Nr. 4 should not be divided, because Project 3 only has one product. Booking Numer 12 for example has to be divided by 3. So that in total the Hours in the end add up to the same total. I hope it's clearer now. *** EDIT 2***

DECLARE @tbl_Projects TABLE (ID INT, [Name] VARCHAR(MAX))
INSERT INTO @tbl_Projects VALUES
(1,'Project 1'),
(2,'Project 2'),
(3,'Project 3'),
(4,'Project 4')

DECLARE @tbl_Products TABLE (ID INT, [Name] VARCHAR(MAX))
INSERT INTO @tbl_Products VALUES
(1,'Product 1'),
(2,'Product 2'),
(3,'Product 3'),
(4,'Product 4')

DECLARE @tbl_ProjectProduct TABLE (ID INT, fk_Project int, fk_Product int)
INSERT INTO @tbl_ProjectProduct VALUES
(1,1,1),
(2,1,2),
(3,2,1),
(4,3,3),
(5,4,1),
(6,4,2),
(7,4,4)

DECLARE @tbl_BookedHours TABLE (ID INT, fk_Project int, Hours int, [Date] Date)
INSERT INTO @tbl_BookedHours VALUES
(1,1,10,'2020-01-15'),
(2,1,20,'2020-01-20'),
(3,2,10,'2020-01-15'),
(4,3,30,'2020-01-18'),
(5,2,20,'2020-01-20'),
(6,4,30,'2020-01-25'),
(7,1,10,'2020-02-15'),
(8,1,20,'2020-02-20'),
(9,2,10,'2020-02-15'),
(10,3,30,'2020-03-18'),
(11,2,20,'2020-03-20'),
(12,4,30,'2020-03-25')

SELECT P.ID AS fk_Product, MONTH(B.Date) AS Month, SUM(B.Hours) AS SumHours
    FROM @tbl_BookedHours AS B INNER JOIN @tbl_Projects AS M on B.fk_Project = M.ID           
                                INNER JOIN @tbl_ProjectProduct AS PP ON PP.fk_Project = M.ID
                                INNER JOIN @tbl_Products AS P ON PP.fk_Product = P.ID                
    GROUP BY P.ID,MONTH(B.Date)
    ORDER BY P.ID, MONTH(B.Date)

This gives me the wrong result, because it Counts the hours for both products:

| fk_Product | Month | SumHours |
|-------------------------------|
| 1          | 1     | 90       |
| 1          | 2     | 40       |
| 1          | 3     | 50       |
| 2          | 1     | 60       |
| 2          | 2     | 30       |
| 2          | 3     | 30       |
| 3          | 1     | 30       |
| 3          | 3     | 30       |
| 4          | 1     | 30       |
| 4          | 3     | 30       |
|-------------------------------|

Answer 1

Consider the following query. I modified your table variables to temp tables so it was easier to debug.

;WITH CTE AS
    (
    SELECT fk_Project, count(fk_Product) CNT 
    FROM #tbl_ProjectProduct
    GROUP BY fk_Project
    )
    ,CTE2 AS
        (
        SELECT t1.Date, t2.fk_Project, Hours/CNT NewHours
        FROM #tbl_BookedHours t1
        INNER JOIN CTE t2 on t1.fk_Project = t2.fk_Project
        )
SELECT t4.ID fk_Product, MONTH(date) MN, SUM(NewHours) HRS
FROM CTE2 t1
INNER JOIN #tbl_Projects t2 on t1.fk_Project = t2.id
INNER JOIN #tbl_ProjectProduct t3 on t3.fk_Project = t2.ID
INNER JOIN #tbl_Products t4 on t4.ID = t3.fk_Product
GROUP BY t4.ID,MONTH(date)