Combining Select Statements

Transact-SQL
1

Hi Folks.

I have two relatively simple select statements that on their own work perfectly and provide accurate results. However when I try to combine them using either a Union or mashing them into a single statement my results are way off and I'm not sure what I'm doing wrong (not very well versed in SQL).

Here are my individual working statements...

SELECT        receive.Name, SUM(recvcart.NetWght) AS SWeight, receive.RecvDate
FROM            master118.receive AS receive INNER JOIN
                         master118.recvcart AS recvcart ON receive.ID = recvcart.RecvID
WHERE        (receive.Code = 'RNY') AND (receive.AcctCode = 'RENT') AND (receive.Name = 'Good Customer') AND (receive.RecvDate BETWEEN '1685620800' AND '1685620800')
GROUP BY receive.RecvDate, receive.Name

SELECT        delivery.Name, SUM(delvcart.NetWght) AS CWeight, delivery.DelvDate
FROM            master118.delivery AS delivery INNER JOIN
                         master118.delvcart AS delvcart ON delivery.ID = delvcart.DelvID
WHERE        (delivery.Code = 'RNY') AND (delivery.AcctCode = 'RENT') AND (delivery.Name = 'Good Customer') AND (delivery.DelvDate BETWEEN '1685620800' AND '1685620800')
GROUP BY delivery.DelvDate, delivery.Name
2

Can you provide some sample data and expected results? They should also show the actual results you're getting and what the discrepancy is to the expected.

3

When run individually I get data that looks like this...

Name SWeight RecvDate
Good Customer 7008 1685620800

Name CWeight DelvDate
Good Customer 4575 1685620800

If I simply add a union in between the statements I get this...

Name SWeight RecvDate
Good Customer 4575 1685620800
Good Customer 7008 1685620800

If I try to combine the two statements into one like this...

SELECT        receive.Name AS Name, SUM(recvcart.NetWght) AS SWeight, receive.RecvDate, delivery.Name as Name, SUM(delvcart.NetWght) AS CWeight, delivery.DelvDate
FROM            master118.receive AS receive INNER JOIN
                         master118.recvcart AS recvcart ON receive.RcID = recvcart.RecvID CROSS JOIN
                         master118.delivery AS delivery INNER JOIN
                         master118.delvcart AS delvcart ON delivery.ID = delvcart.DelvID
WHERE        (receive.Code = 'RNY') AND (receive.AcctCode = 'RENT') AND (receive.Name = 'Good Customer') AND (receive.RecvDate BETWEEN '1685620800' AND '1685620800') AND 
                         (delivery.Code = 'RNY') AND (delivery.AcctCode = 'RENT') AND (delivery.Name = 'Good Customer') AND (delivery.DelvDate BETWEEN '1685620800' AND '1685620800')
GROUP BY receive.RecvDate, delivery.DelvDate, receive.Name, delivery.Name

I get this...
Name SWeight RecvDate Name CWeight DelvDate
Good Customer 189216 1685620800 Good Customer 146400 1685620800

If I try Select Distinct I get the same results. What I am looking to accomplish is this...

Name SWeight CWeight
Good Customer 7008 4575

Truth be told I don't really need to output the dates but was doing so for testing.

4

This should do it:

;WITH CTE(Name, Weight,Type) AS (
SELECT        receive.Name, SUM(recvcart.NetWght), 'R' Type
FROM            master118.receive AS receive INNER JOIN
                         master118.recvcart AS recvcart ON receive.ID = recvcart.RecvID
WHERE        (receive.Code = 'RNY') AND (receive.AcctCode = 'RENT') AND (receive.Name = 'Good Customer') AND (receive.RecvDate BETWEEN '1685620800' AND '1685620800')
GROUP BY receive.Name
UNION ALL
SELECT        delivery.Name, SUM(delvcart.NetWght), 'D' Type
FROM            master118.delivery AS delivery INNER JOIN
                         master118.delvcart AS delvcart ON delivery.ID = delvcart.DelvID
WHERE        (delivery.Code = 'RNY') AND (delivery.AcctCode = 'RENT') AND (delivery.Name = 'Good Customer') AND (delivery.DelvDate BETWEEN '1685620800' AND '1685620800')
GROUP BY delivery.Name)
SELECT Name, SUM(Case WHEN Type='R' THEN Weight END) SWeight,
SUM(Case WHEN Type='D' THEN Weight END) CWeight
FROM cte
GROUP BY Name
1 Like
5

I'd leave the dates in the combining query since your original queries allow a date range (although the specific query above uses a fixed date);


;WITH cte_receive AS (
    SELECT        receive.Name, SUM(recvcart.NetWght) AS SWeight, receive.RecvDate
    FROM            master118.receive AS receive INNER JOIN
                             master118.recvcart AS recvcart ON receive.ID = recvcart.RecvID
    WHERE        (receive.Code = 'RNY') AND (receive.AcctCode = 'RENT') AND (receive.Name = 'Good Customer') AND (receive.RecvDate BETWEEN '1685620800' AND '1685620800')
    GROUP BY receive.RecvDate, receive.Name
),
cte_delivery AS (
    SELECT        delivery.Name, SUM(delvcart.NetWght) AS CWeight, delivery.DelvDate
    FROM            master118.delivery AS delivery INNER JOIN
                             master118.delvcart AS delvcart ON delivery.ID = delvcart.DelvID
    WHERE        (delivery.Code = 'RNY') AND (delivery.AcctCode = 'RENT') AND (delivery.Name = 'Good Customer') AND (delivery.DelvDate BETWEEN '1685620800' AND '1685620800')
    GROUP BY delivery.DelvDate, delivery.Name
)
SELECT 
    ISNULL(cr.Name, cd.Name) AS Name, ISNULL(cr.RecvDate, cd.DelvDate) AS Date,
    SUM(cr.SWeight) AS SWeight, SUM(cd.CWeight) AS CWeight
FROM cte_receive cr
FULL OUTER JOIN cte_delivery cd ON cd.Name = cr.Name AND cd.DelvDate = cr.RecvDate
GROUP BY ISNULL(cr.Name, cd.Name), ISNULL(cr.RecvDate, cd.DelvDate)
ORDER BY Name, Date /*optional, of course*/
1 Like
6

Thank you both. I went with Robert's solution since it was easier for me to understand. Just for my own knowledge... if I'm understanding correctly you created a third query that wraps up the results of the original two in a single dataset. Is that accurate to say?

7

In this case, that's correct. The Common Table Expression (CTE) does a simple UNION ALL with your original 2 queries, with a slightly modified GROUP BY. The outer SELECT...FROM the CTE uses a CASE expression to SUM the weights for each type as separate columns.

1 Like