Click here to Skip to main content
15,794,236 members
Home / Discussions / Database
   

Database

 
QuestionLooking for some assistance with a query Pin
FrankLepkowski10-Sep-19 10:50
FrankLepkowski10-Sep-19 10:50 
AnswerRe: Looking for some assistance with a query Pin
MadMyche10-Sep-19 12:12
professionalMadMyche10-Sep-19 12:12 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 2:56
professionalMadMyche11-Sep-19 2:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 5:15
FrankLepkowski11-Sep-19 5:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 7:28
professionalMadMyche11-Sep-19 7:28 
QuestionNeed help mixing two queries in one... Pin
Joan M1-Sep-19 1:40
professionalJoan M1-Sep-19 1:40 
AnswerRe: Need help mixing two queries in one... Pin
Richard Deeming2-Sep-19 2:02
mveRichard Deeming2-Sep-19 2:02 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 2:39
professionalJörgen Andersson2-Sep-19 2:39 
The first CTE can probable be simplified to:
SQL
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

I would look over the roundings though
Wrong is evil and must be defeated. - Jeff Ello

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
Jörgen Andersson2-Sep-19 20:27
professionalJörgen Andersson2-Sep-19 20:27 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 20:29
professionalJoan M2-Sep-19 20:29 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 20:35
professionalJörgen Andersson2-Sep-19 20:35 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 11:21
professionalJoan M2-Sep-19 11:21 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 11:43
professionalphil.o2-Sep-19 11:43 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 12:24
professionalJoan M2-Sep-19 12:24 
GeneralRe: Need help mixing two queries in one... Pin
phil.o2-Sep-19 13:25
professionalphil.o2-Sep-19 13:25 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 19:52
professionalJoan M2-Sep-19 19:52 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes3-Sep-19 13:40
professionalMycroft Holmes3-Sep-19 13:40 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 10:24
professionalJoan M4-Sep-19 10:24 
GeneralRe: Need help mixing two queries in one... Pin
Joan M4-Sep-19 10:25
professionalJoan M4-Sep-19 10:25 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes4-Sep-19 13:37
professionalMycroft Holmes4-Sep-19 13:37 
GeneralRe: Need help mixing two queries in one... Pin
Mycroft Holmes2-Sep-19 13:31
professionalMycroft Holmes2-Sep-19 13:31 
GeneralRe: Need help mixing two queries in one... Pin
Joan M2-Sep-19 20:27
professionalJoan M2-Sep-19 20:27 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 20:29
professionalJörgen Andersson2-Sep-19 20:29 
QuestionAdvice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx30-Aug-19 9:16
professionaljkirkerx30-Aug-19 9:16 

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.