월별 상품최저가

use study;

select * from tbl_basegoods;
select * from tbl_subgoods;

-- 기본+하위 상품 조인하여 모두 나타내기
select ts.id,(tb.gname+'-'+ts.gname) as goodsname ,ts.price,ts.startdate,ts.enddate from tbl_subgoods as ts
inner join tbl_basegoods tb on tb.id = ts.pid 
where ts.startdate   BETWEEN '2018-01-01' AND '2018-12-30';

-- 3월 4월 기본상품별 출발 최저가 : 편의를 위해 년도 생략 2018년이라고 가정....
with tmpView as(
	select DISTINCT MIN(ts.price)  OVER(PARTITION BY tb.id) as minprice,
	ts.id as sid,(tb.gname+'-'+ts.gname) as goodsname ,ts.price,ts.startdate,ts.enddate from tbl_subgoods as ts
	inner join tbl_basegoods tb on tb.id = ts.pid 
	where MONTH(ts.startdate) = 03
UNION ALL
	select DISTINCT MIN(ts.price)  OVER(PARTITION BY tb.id) as minprice,
	ts.id as sid,(tb.gname+'-'+ts.gname) as goodsname ,ts.price,ts.startdate,ts.enddate from tbl_subgoods as ts
	inner join tbl_basegoods tb on tb.id = ts.pid 
	where MONTH(ts.startdate) = 04
)
select tv.sid,tv.price,tv.goodsname, case when tv.minprice=tv.price then
CAST(MONTH(tv.startdate) AS VARCHAR(5)) +'월' else null end as bestmonth  from tmpView as tv where tv.minprice=tv.price