Database

SELECT m.LoopCode
,       m.Chopper
,       Total_BRKS  = IsNull(Sum(s.BRKS), 0)
,       Total_BBOH  = IsNull(Sum(s.BBOH), 0)
,       Avg_DTAB        = IsNull(Avg(s.DTAB), 0)

FROM        @ShiftData          s
RIGHT JOIN @MapBushingData  m ON s.[Position] = m.LoopCode

WHERE ( s.ShiftDate >= cast('02/03/2011' as date)
    AND     s.ShiftDate <= cast('03/04/2013' as date)
)
OR  s.ShiftDate IS NULL

GROUP BY m.Chopper,m.LoopCode

DECLARE @ShiftData TABLE (ndx int identity(1,1) not null, Position int, ShiftDate Date, BRKS int, BBOH int, DTAB int)
INSERT @ShiftData
VALUES (1, '10/11/2012', 1, 2, 3)
,  (2, '12/11/2013', 3, 4, 5)
,  (3, '10/11/2011', 1, 2, 3)

DECLARE @MapBushingData TABLE (ndx int identity(1,1) not null, LoopCode int, Chopper varchar(10))
INSERT @MapBushingData
VALUES (1, 'Chopper 1')
, (2, 'Chopper 2')
, (3, 'Chopper 3')

SELECT m.LoopCode
,       m.Chopper
,
		SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
         THEN s.BRKS ELSE 0
    END) AS Total_BRKS
,
    		SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
             THEN s.BBOH ELSE 0
        END) AS Total_BBOH
,
    		AVG(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
             THEN s.DTAB ELSE 0
        END) AS Avg_DTAB

FROM        ShiftData          s
RIGHT JOIN MapBushingData  m ON s.[Position] = m.LoopCode

GROUP BY m.Chopper,m.LoopCode
ORDER BY m.LoopCode asc

WITH ctePrices As
(
    SELECT
        tTasks.invoiceId,
        (
            CASE WHEN tTasks.taskUseId = 1 THEN
            (
                CASE WHEN price IS NULL THEN
                (
                    SELECT tReferencePricesForTasks.price
                    FROM tReferencePricesForTasks
                    WHERE tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId 
                    AND tReferencePricesForTasks.projectId = tTasks.projectId 
                    AND tReferencePricesForTasks.userId = tTasks.userId
                ) 
                ELSE 
                    price
                END
            )
            ELSE
                0
            END
        ) 
        *
        (
            ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)
        ) 
        AS subTotal 
    FROM
        tTasks
        LEFT JOIN tTasksTypes ON tTasks.taskTypeId = tTasksTypes.id
    WHERE 
        tTasks.taskUseId <> 3
    
    UNION ALL 
    
    SELECT
        tExpenses.invoiceId,
        (
            CASE WHEN price IS NULL THEN
            (
                SELECT ROUND(tReferencePricesForExpenses.preu,2)
                FROM tReferencePricesForExpenses
                WHERE tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId 
                AND tReferencePricesForExpenses.projectId = tExpenses.projectId 
                AND tReferencePricesForExpenses.userId = tExpenses.userId
            ) 
            ELSE 
                ROUND(cost, 2)
            END
        ) 
        *
          round(tExpenses.quantity, 2)
        AS subTotal
    FROM
        tExpenses
        LEFT JOIN tExpensesTypes ON tExpenses.expenseTypeId = tExpensesTypes.id
),
cteTotals As
(
    SELECT
        invoiceId,
        SUM(subTotal) As total
    FROM
        ctePrices
    GROUP BY
        invoiceId
)
SELECT 
    tInvoices.*, 
    tCustomers.name,
    cteTotals.total
FROM 
    tInvoices 
    INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
    INNER JOIN cteTotals ON cteTotals.invoiceId = tInvoices.id
;

SELECT  tTasks.invoiceId
       ,CASE
            WHEN tTasks.taskUseId = 1
                THEN COALESCE(tTasks.price,tReferencePricesForTasks.price)
            ELSE 0
        END * (ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal
FROM    tTasks
JOIN    tReferencePricesForTasks
    ON  tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
    AND tReferencePricesForTasks.projectId = tTasks.projectId
    AND tReferencePricesForTasks.userId = tTasks.userId
WHERE   tTasks.taskUseId <> 3

UNION ALL

SELECT  tExpenses.invoiceId
       ,ROUND(COALESCE(tExpenses.price,tReferencePricesForExpenses.preu), 2) * round(tExpenses.quantity, 2) AS subTotal
FROM    tExpenses
JOIN    tReferencePricesForExpenses
    ON  tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId
    AND tReferencePricesForExpenses.projectId = tExpenses.projectId
    AND tReferencePricesForExpenses.userId = tExpenses.userId

SELECT  tInvoices.*
       ,tCustomers.name
       ,cteTotals.total
FROM    tInvoices
INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
INNER JOIN (
    SELECT  invoiceId
           ,SUM(subTotal) AS total
    FROM    (
        SELECT  tTasks.invoiceId
               ,CASE 
                    WHEN tTasks.taskUseId = 1
                        THEN COALESCE(tTasks.price, tReferencePricesForTasks.price)
                    ELSE 0
                END * (ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal
        FROM    tTasks
        JOIN    tReferencePricesForTasks ON tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
            AND tReferencePricesForTasks.projectId = tTasks.projectId
            AND tReferencePricesForTasks.userId = tTasks.userId
        WHERE   tTasks.taskUseId <> 3
        
        UNION ALL
        
        SELECT  tExpenses.invoiceId
               ,ROUND(COALESCE(tExpenses.price, tReferencePricesForExpenses.preu), 2) * round(tExpenses.quantity, 2) AS subTotal
        FROM    tExpenses
        JOIN    tReferencePricesForExpenses ON tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId
            AND tReferencePricesForExpenses.projectId = tExpenses.projectId
            AND tReferencePricesForExpenses.userId = tExpenses.userId
        ) ctePrices
    GROUP BY invoiceId
    ) cteTotals ON cteTotals.invoiceId = tInvoices.id