How to combine 3 queries into one

Question

I have 3 queries that ultimately get me daily financial information. What i'd like to do is combine the columns I need as 1 input. I tried doing derived tables but it is extremely sloppy and didn't work. Does anyone have any suggestion on how I can combine these 3 to give me output on 1 line?

Here are my queries:

SELECT        ReportCategoryID, ReportCategoryName, PrintOrder, QTY, NetAmt, customers, Average
FROM            dbo.fRevenueSummary(@startdate, @enddate) AS fRevenueSummary_1

Second Query:

SELECT        COUNT(*) AS count, SUM(TicketsDetails.dblTotal) AS Merch_Total
FROM            TicketsDetails INNER JOIN
                         Surcharges ON TicketsDetails.Reference_ID = Surcharges.Surcharge_ID INNER JOIN
                         SurchargesTypes ON Surcharges.lSurchargeTypeID = SurchargesTypes.lSurchargeTypeID
WHERE        (TicketsDetails.dtCreated BETWEEN @startdate AND @enddate) AND (SurchargesTypes.lSurchargeTypeID = '2') AND (TicketsDetails.bPaid = 1) AND 
                         (TicketsDetails.Detail_Type_ID = 3) AND (NOT (TicketsDetails.sDescription = N'TIP'))

Third Query:

SELECT        SUM(dblRegLabor) AS Regular_Amount, SUM(dblOTLabor) AS OT_Amount
FROM            TimeClock
WHERE        (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '1') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '2') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '3') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '4') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '5') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '6') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '7')

Can you please explain what is the desired output and the how are the tables `TimeClock`, `TicketsDetails`, `Surcharges` and `SurchargesTypes ` are related? — Mahmoud Gamal, Dec 17 '12 at 15:04

Marc · Accepted Answer · 2012-12-17T15:08:33.580

The easiest way would be like this

SELECT *
FROM
(
    SELECT        ReportCategoryID, ReportCategoryName, PrintOrder, QTY, NetAmt, customers, Average
    FROM            dbo.fRevenueSummary(@startdate, @enddate) AS fRevenueSummary_1
)
,
(
    SELECT        COUNT(*) AS count, SUM(TicketsDetails.dblTotal) AS Merch_Total
    FROM            TicketsDetails INNER JOIN
                         Surcharges ON TicketsDetails.Reference_ID = Surcharges.Surcharge_ID INNER JOIN
                         SurchargesTypes ON Surcharges.lSurchargeTypeID = SurchargesTypes.lSurchargeTypeID
    WHERE        (TicketsDetails.dtCreated BETWEEN @startdate AND @enddate) AND (SurchargesTypes.lSurchargeTypeID = '2') AND (TicketsDetails.bPaid = 1) AND 
                         (TicketsDetails.Detail_Type_ID = 3) AND (NOT (TicketsDetails.sDescription = N'TIP'))
)
,
(
    SELECT        SUM(dblRegLabor) AS Regular_Amount, SUM(dblOTLabor) AS OT_Amount
    FROM            TimeClock
    WHERE        (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '1') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '2') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '3') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '4') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '5') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '6') OR
                         (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = '7')
)

but if there's no relation between them, you will return a Cartesian product.

I don't think there's a way to combine 3 different queries without a Cartesian product.

It's a cartesian product. I don't think there's a way to combine 3 different queries without. — Marc, Dec 17 '12 at 15:07

score 1 · Answer 2 · answered Dec 17 '12 at 15:03

This is not an answer to your question, but it may simplify your thinking to know that your third query could be greatly simplified:

SELECT  SUM(dblRegLabor) AS Regular_Amount, SUM(dblOTLabor) AS OT_Amount
FROM    TimeClock
WHERE   (dtTimeIn > @startdate) AND (dtTimeOut < @enddate) AND (sDept = IN('1', '2', '3', '4', '5', '6', '7'))

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

1

Well, if you want the ugly, messy solution, you can just put parentheses around each statement, then treat each one as a table in a giant JOIN (something similar to what Marc illustrates).

If you want a good solution, you will have to describe what you are actually trying to do. Without knowing your data and your actual task, we can't optimize your code.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 17 '12 at 15:09

dan1111

6,576
2
18
29

How to combine 3 queries into one

3 Answers3