Click here to Skip to main content
15,112,453 members
Home / Discussions / Database
   

Database

 
AnswerRe: I need this windows odbc driver Pin
Maciej Los11-Sep-19 9:47
mveMaciej Los11-Sep-19 9:47 
QuestionLooking for some assistance with a query Pin
FrankLepkowski10-Sep-19 10:50
MemberFrankLepkowski10-Sep-19 10:50 
AnswerRe: Looking for some assistance with a query Pin
MadMyche10-Sep-19 12:12
mveMadMyche10-Sep-19 12:12 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 2:56
mveMadMyche11-Sep-19 2:56 
GeneralRe: Looking for some assistance with a query Pin
FrankLepkowski11-Sep-19 5:15
MemberFrankLepkowski11-Sep-19 5:15 
GeneralRe: Looking for some assistance with a query Pin
MadMyche11-Sep-19 7:28
mveMadMyche11-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 
Try something like this:
SQL
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
;




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 2:39
professionalJörgen Andersson2-Sep-19 2:39 
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
mvephil.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
mvephil.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 

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.