Database

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

;WITH ctePrices AS...

while (!(success = Try()));

Quote:

Well I just don't know the limitations of MongoDb since it's a NoSQL or document based database.

Quote:

I've read the right way to do it is to use SQL Server for storing transactions because it's faster.

Quote:

FireBase and CosmoDB

Quote:

I really don't want to go back to SQL Server again.