I have two tables, each with non-distinct IDs, products, and sales dates. I want a combined table where the rows are the distinct IDs, and the columns are the earliest sales date of each product.

The tables look like the following:

Table_1

Table_2

And my desired end table would be:

Merged_table

I have tried the following code to merge the tables:

create table merged_table as
select member_id, min(a.sales_date) as Product_A_earliest_date, min(b.sales_date) as Product_B_earliest_date from(
select member_id from table_1 as a
UNION
select member_id from table_2 as b);

But this provides 'missing at EOF' errors. Am I incorrectly using the UNION function?

CodePudding user response：

assuming sql server or some other engine that supports ctes...

the first challenge is getting all the ids... it cant be the case that every sales person will sell both products

once you have that, its fairly straight forward. i did not test this out, but this is the approach.

    ;with ids as ( select distinct member_Id from table_1 
                      union
                     select distinct member_id from table_2
                 )
    , uniqueIds as ( select distinct member_id from ids)
    ;with t1  as (  select member_id, product, min(sales_date) [sales_date] 
                 from Table_1
                  group by member_id,product) 
    ,  t2  as (  select member_id, product, min(sales_date) [sales_date] 
                 from Table_2
                   group by member_id,product) 
    
    select ui.member_Id, t1.salesDate [EarlyDate_a], t2.salesDate [earlydate_b]
    from uniqueIds ui
    left join t1_ on ui.memberId = t1.memberId
    left join t2_ on ui.memberId = t2.memberId

CodePudding user response：

We usually get missing at EOF when the statement is incomplete.

There are a few issues with your sql statement:

1. The alias a used for table_1 and b used for table_b will not exist outside of the subquery used to create the union
2. You are using the aggregate function min and a non-aggregate column member_id without having member_id in the group by clause.
3. Your subquery being used in your outermost from clause is missing an alias

You may try the following query

SELECT
    Member_ID,
    MIN(
        CASE WHEN Product='A' THEN Sales_Date END
    ) as Product_A_earliest_date,
    MIN(
        CASE WHEN Product='B' THEN Sales_Date END
    ) as Product_B_earliest_date
FROM (
    SELECT Member_ID, Product, Sales_Date FROM table_1
    UNION ALL
    SELECT Member_ID, Product, Sales_Date FROM table_2
) t
GROUP BY Member_ID
ORDER BY Member_ID

View working demo on DB Fiddle

Let me know if this works for you.