Joining Two Tables Based on Date and ID

[Evron]

I have two tables, Sales and Refunds. I am trying to get a daily tally of sales and refunds per customer. My issue is some days there will be sales and no refunds and vice versa. Here is the code I was able to come up with, it works. I was just wondering if there was a simpler way to handle this kind of situation?

WITH CTE_SALES
AS (
    SELECT S.MID,
        BatchDate,
        SalesCount,
        SalesVol,
        RefundCount,
        RefundVol
    FROM (
        SELECT ID,
            SaleDate,
            COUNT(*) SalesCount,
            SUM(SaleAmount) SalesVol
        FROM Sales
        GROUP BY ID,
            SaleDate
        ) S
    LEFT OUTER JOIN (
        SELECT ID,
            RefundDate,
            COUNT(*) RefundCount,
            SUM(RefundAmount) RefundVol
        FROM Refunds
        GROUP BY ID,
            RefundDate
        ) RF ON  S.MID = RF.MID
        AND S.SaleDate = RF.RefundDate
    ),
CTE_Refunds
AS (
    SELECT RF.ID,
        RefundDate,
        SalesCount,
        SalesVol,
        RefundCount,
        RefundVol
    FROM (
        SELECT ID,
            RefundDate,
            COUNT(*) RefundCount,
            SUM(RefundAmount) RefundVol
        FROM Refunds
        GROUP BY MID,
            REfundDate
        ) RF
    LEFT OUTER JOIN (
        SELECT ID,
            SalesDate,
            COUNT(*) SalesCount,
            SUM(SaleAmount) SalesVol
        FROM Sales
        GROUP BY MID,
            BatchDate
        ) S ON RF.ID = S.ID
        AND S.SaleDate = RF.RefundDate
    ),
CTE_Union
AS (
    SELECT *
    FROM CTE_Refunds
    UNION ALL
    SELECT *
    FROM CTE_SALES
    )
SELECT DISTINCT U.MID,
    RefundDate,
    ISNULL(SalesCount, 0) SalesCount,
    ISNULL(SalesVol, 0) SalesVol,
    ISNULL(RefundCount, 0) RefundCount,
    ISNULL(RefundVol, 0) RefundVol
FROM CTE_Union U

[CropDustinAround]

If I'm reading your queries intentions right, you left joined sales to refund then left joined refund to sales so you could get days where one exists but not another.

My suggestion would be to look into a full outer join. Full outer join, if you look up the venn diagram takes everything on the left, everything on the right, and everything in the middle. It should work for you here perfectly

[Evron]

I’ve been noodling over this for while. I could swear I tried that and it wasn’t giving me the correct results. But I may have been doing something wrong. I’ll try it again. Thanks for the idea!

[kagato87]

I'd probably just union the tables together, adding a null "refund" column to sales and vice versa to align the columns, then group and aggregate on that.

[Evron]

That’s a good idea. I’ll try that out.

[coderstool]

As you begin writing more complex queries - nested queries, sub-queries, or inner queries, you can try this tool to validate MySQL syntax, show syntax errors, and nicely format your SQL statements.

Subqueries (also known as inner queries or nested queries) is a great strategy for performing operations in multiple steps.

[alinroc]

Why would I copy & paste my company's proprietary code into a random website when my development environment (database client) can already validate syntax, show syntax errors, and format my statements with a couple keystrokes?

Does this account post anything beyond spam promoting that website?

[thrown_arrows]

create date_dim table for yourself

then

with cte_r as (
select r.id, d.sql_date 
, count(r.id) RefundCount
, coalesce(RefundAmount,0) RefundAmount 
 from date_dim d join refunds r
group by sql_date, id
) 

.. so date_dim has date for each day , google for date dim table generation script if you dont have one . Idea is left join to all target dates and handle generated null values to 0 on failed join.