Click here to Skip to main content
14,303,648 members
   

Database

 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 1:56
mveMadMyche11-Sep-19 1:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 4:15
memberFrankLepkowski11-Sep-19 4:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 6:28
mveMadMyche11-Sep-19 6:28 
QuestionNeed help mixing two queries in one... Pin
Joan M1-Sep-19 0:40
professionalJoan M1-Sep-19 0:40 
AnswerRe: Need help mixing two queries in one... Pin
Richard Deeming2-Sep-19 1:02
mveRichard Deeming2-Sep-19 1:02 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 1:39
protectorJörgen Andersson2-Sep-19 1:39 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 10:24
professionalJoan M2-Sep-19 10:24 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:27
protectorJörgen Andersson2-Sep-19 19:27 
So inline the CTEs then:
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


The problem I have with rounding is that you're rounding to early, and even using a product of two roundings in the sum.
That way you're creating a rounding error: Round-off error - Wikipedia[^]
Wrong is evil and must be defeated. - Jeff Ello

GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 19:29
professionalJoan M2-Sep-19 19:29 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:35
protectorJörgen Andersson2-Sep-19 19:35 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 10:21
professionalJoan M2-Sep-19 10:21 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 10:43
mvephil.o2-Sep-19 10:43 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 11:24
professionalJoan M2-Sep-19 11:24 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 12:25
mvephil.o2-Sep-19 12:25 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 18:52
professionalJoan M2-Sep-19 18:52 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes3-Sep-19 12:40
memberMycroft Holmes3-Sep-19 12:40 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 9:24
professionalJoan M4-Sep-19 9:24 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 9:25
professionalJoan M4-Sep-19 9:25 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes4-Sep-19 12:37
memberMycroft Holmes4-Sep-19 12:37 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes2-Sep-19 12:31
memberMycroft Holmes2-Sep-19 12:31 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 19:27
professionalJoan M2-Sep-19 19:27 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 19:29
protectorJörgen Andersson2-Sep-19 19:29 
AnswerMessage Closed Pin
16-Sep-19 20:25
membergentorify16-Sep-19 20:25 
QuestionAdvice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx30-Aug-19 8:16
professionaljkirkerx30-Aug-19 8:16 
AnswerRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
Afzaal Ahmad Zeeshan30-Aug-19 11:56
mveAfzaal Ahmad Zeeshan30-Aug-19 11:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.