I have to query results from three different tables and so far have been able to get the separate results individually. Now I'd like to join these results. The following two pieces of code are my queries. Should I nest the two somehow? I would want to inner join on the s.STORE_NUM and str.num from the bottom query, how would I declare the two separately though?
SELECT
S.STORE_NUM,
M.YEAR_FISCAL,
--M.YEAR_445,
M.MONTH_NAME_445,
M.WEEK_START_DT_MON,
SUM(SALES_NET) - SUM(S.SALES_GCARD_NET) as SALES_WT_GC,
SUM(S.TRAFFIC_COUNT) AS TRAFFIC,
SUM(S.TICKETS_NET) AS TRANS,
SUM(S.QTY_NET) AS UNITS,
-- Calculation level:
SUM(S.QTY_NET)/SUM(S.TRAFFIC_COUNT) AS UOT,
SUM(S.QTY_NET)/SUM(S.TICKETS_NET) AS UPT,
SUM(S.TICKETS_NET)/SUM(S.TRAFFIC_COUNT) AS CONVPCT
FROM DNA_PUBLIC..RETAIL_SALES_BY_STORE_BY_DAY S
JOIN DNA_PUBLIC.ADMIN.DAY_MAP M
ON S.DATE_DATA = M.DATE_DT
WHERE S.DATE_DATA BETWEEN '08/24/2020' AND '09/27/2020'
AND S.SALES_NET > 0
AND S.TRAFFIC_COUNT > 0
AND S.QTY_NET > 0
AND S.TICKETS_NET > 0
AND S.STORE_NUM = 2Ah
GROUP BY 1,2,3,4
ORDER BY S.STORE_NUM, WEEK_START_DT_MON
and
SELECT r.str_num, r.str_nam, r.str_dst, s.region_name, s.div_name, r.STR_ADDR, r.str_cty, r.str_zip5
FROM admin.retail_store_names r JOIN admin.store_dim s ON r.str_num = s.store_num
I would want to inner join on the s.STORE_NUM and str.num from the bottom query, how would I declare the two separately though?
You can join both resultsets by turning the queries to subqueries, and joining in the outer query. The order by clause goes to the outer query:
select t1.*, t2.*
from (
-- first query goes here (without the "order by")
) t1
inner join (
-- second query goes there
) t2 on t2.str_num = t1.store_num
order by t1.str_num, t2.week_start_dt_mon