I want to join a table with the output of select statement where I need to use a column last_order_date
from Table1 in the WHERE
clause in the select statement.
For example, see this screenshot:
I tried the following code:
SELECT
*
FROM
Table1 t1
LEFT JOIN
(SELECT prod_id, SUM(sales) sales_sum
FROM Table2
WHERE transaction_date BETWEEN t1.last_order_date AND CAST(GETDATE()-1 AS DATE)
GROUP BY prod_id) t2
ON t1.prod_id = t2.prod_id
Apparently, the problem is that I can't use t1.last_order_date
in the WHERE CLAUSE in the right part of the join statement. Anyone can help?
CodePudding user response:
You can try to use OUTER APPLY
SELECT *
FROM
Table1 t1
OUTER APPLY
(
SELECT t2.prod_id, SUM(t2.sales) sales_sum
FROM Table2 t2
WHERE t2.transaction_date BETWEEN t1.last_order_date AND CAST(GETDATE()-1 AS DATE)
AND t1.prod_id = t2.prod_id
GROUP BY t2.prod_id
) t2
From your expect result I think you can use OUTER JOIN
like this.
SELECT t1.prod_id,
MIN(t1.last_order_date) last_order_date,
SUM(ISNULL(t2.sales,0)) sales
FROM
Table1 t1
LEFT JOIN Table2 t2
ON t2.transaction_date BETWEEN t1.last_order_date
AND CAST(GETDATE()-1 AS DATE)
AND t1.prod_id = t2.prod_id
GROUP BY t1.prod_id