Combine the output of two queries into a single query

Artie 0
Viewed 45

I want to combine the below queries into a single output, I am not sure on how to use union as I have bunch of conditions on the queries as well. In the final output I also need to Subtract the result of negative_sales column from query 2 from Net value column from query 1

SELECT storenumber,
       bbbtendertypetext,
       SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender]
WHERE  transactiondatetime > '2022-09-21 00:00:00.000'
       AND transactiondatetime < '2022-09-22 00:03:00.000'
GROUP  BY storenumber, bbbtendertypetext
ORDER  BY storenumber 

SELECT storenumber,
       bbbtendertypetext,
       SUM(financialamounttendered) AS negative_Net_Sales,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender_correct]
       where transactiondatetime > '2022-09-21 00:00:00.000'
       AND transactiondatetime < '2022-09-22 00:03:00.000'
GROUP  BY storenumber, bbbtendertypetext
ORDER  BY storenumber
sql-server sql
2 Answers

For example so

select s1.storenumber storenumber
     , s2.storenumber storenumber2
     , s1.bbbtendertypetext bbbtendertypetext
     , s2.bbbtendertypetext bbbtendertypetext2
     , s1.NET_VALUE  NET_VALUE
     , s1.Transaction_Count Transaction_Count
     , s2.Transaction_Count Transaction_Count2
     , s2.negative_Net_Sales negative_Net_Sales
     , s1.NET_VALUE - s2.negative_Net_Sales differnt
from (
    SELECT storenumber,
           bbbtendertypetext,
           SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
           COUNT(transactionid) AS Transaction_Count
    FROM   [AceTLogData].[Tlog].[tender]
    WHERE  transactiondatetime > '2022-09-21 00:00:00.000'
           AND transactiondatetime < '2022-09-22 00:03:00.000'
    GROUP  BY storenumber, bbbtendertypetext
    ORDER  BY storenumber 
) s1 left join (
    SELECT storenumber,
           bbbtendertypetext,
           SUM(financialamounttendered) AS negative_Net_Sales,
           COUNT(transactionid) AS Transaction_Count
    FROM   [AceTLogData].[Tlog].[tender_correct]
           AND transactiondatetime > '2022-09-21 00:00:00.000'
           AND transactiondatetime < '2022-09-22 00:03:00.000'
    GROUP  BY storenumber, bbbtendertypetext
    ORDER  BY storenumber
) s2 on s1.storenumber = s2.storenumber

Note that there is no where section in the second query

SwaD0

Assuming you want to combine the SUM values of query A, and query B above (unique values GROUPED BY storenumber and bbbtendertypetext), you can use two CTE's then INNER JOIN them on storenumber to get your result set and do an overall SUM from there.

If you want a more non-aggregated result set, remove the GROUP BY and SUM's from the main query below the CTE's.

WITH a AS (SELECT storenumber,
       bbbtendertypetext,
       transactiondatetime,
       SUM(financialchangeamount) AS charge_amount,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender]
  GROUP  BY storenumber, bbbtendertypetext, transactiondatetime),
     b AS (SELECT storenumber,
       bbbtendertypetext,
       transactiondatetime,
       SUM(financialamounttendered) AS negative_Net_Sales,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender_correct]
   GROUP  BY storenumber, bbbtendertypetext, transactiondatetime)
SELECT a.storenumber,
       a.bbbtendertypetext,
       SUM(a.charge_amount) AS charge_amount,
       SUM(b.negative_Net_Sales) AS negative_Net_Sales ,
       SUM(a.charge_amount - b.negative_Net_Sales) AS NET_VALUE,
       SUM(a.Transaction_Count) AS Transaction_Count
FROM a INNER JOIN b ON a.storenumber = b.storenumber
WHERE  a.transactiondatetime 
  BETWEEN '2022-09-21 00:00:00.000' AND  '2022-09-22 00:03:00.000'
GROUP BY a.storenumber, a.bbbtendertypetext

See Fiddle.

GRIV0
Related