Types of joins and expected output

Question

I have a table that has wholesale data and retail data. the data is structured as

Channel	Serial#	Date
WS-Build	12345	1/1/2019
WS-Dealer	34567	1/5/2021
Retail	12345	1/1/2020
Retail	34567	3/5/2021

I would like the output to match on serial#

Each serial # will appear twice in the table. I am trying to get a count of # of units sold via builder or dealer.

Serial#	Channel	WholesaleDate	Retail Date
12345	WS-Build	1/1/2019	1/1/2020
34567	WS-Dealer	1/5/2021	3/5/2021

How can i achieve that by joining on the same table?

Answer 1

Try join by serial and channel

select t1.serial#, t2.WholesaleDate, t2."Retail Date", (*) from table1 t1
join table2 t2 on t1.serial# = t2.serial# and t1.channel = t2.channel
group by t1.serial#, , t2.WholesaleDate, t2."Retail Date";

Answer 2

As long as the retail is after the sale you can do

but i don't get where the counts come in

SELECT
 t1."Serial#",t1."Channel", t1."Date" as WholesaleDate, t2."Date" as "Retail Date"
FROM tab1 t1 JOIN tab1 t2 ON t1."Serial#" = t2."Serial#" AND t1."Date" < t2."Date"

Serial#	Channel	wholesaledate	Retail Date
12345	WS-Build	2019-01-01 00:00:00	2020-01-01 00:00:00
34567	WS-Dealer	2021-05-01 00:00:00	2021-05-03 00:00:00

SELECT 2

fiddle