SELECT
DISTINCT
disp.disp_seq ,
disp.db_id ,
disp.db_nm ,
disp.disp_kind ,
disp.disp_anc ,
disp.disp_tb ,
disp.nat_cd ,
disp.nat_nm ,
disp.city_cd ,
disp.city_nm ,
disp.pk_seq ,
disp.goods_cd ,
disp.banner_nm ,
disp.gubun_nm ,
disp.cls_cd ,
disp.cls_det_cd ,
disp.del_yn ,
disp.disp_yn ,
disp.disp_level ,
disp.disp_order ,
sgl.sgl_seq ,
sgl.db_nm ,
sgl.goods_cd ,
sgl.nat_cd ,
sgl.nat_nm ,
sgl.city_cd ,
sgl.city_nm ,
sgl.sgl_nm ,
sgl.sgl_desc ,
sgl.cha_id ,
sgl.cha_cd ,
sgl.cha_nm ,
sgl.cha_tel ,
sgl.bill_unit ,
amt.* ,
day.*
FROM th_disp_goods disp
LEFT JOIN pf_sgl sgl
ON disp.pk_seq = sgl.sgl_seq
INNER JOIN
(
SELECT
sgl_seq,
sgl_amt_seq,
MIN(start_day) start_day,
end_day,
MIN(b2c_adt_amt) b2c_adt_amt,
b2c_chd_amt,
b2c_inf_amt
FROM pf_sgl_amt
WHERE end_day >= '2025-07-24'
AND disp_yn = 'Y' AND del_yn = 'N'
GROUP BY sgl_seq
) amt
ON sgl.sgl_seq = amt.sgl_seq
INNER JOIN
(
SELECT
sgl_day_seq,
sgl_amt_seq,
MIN(d_start_day) d_start_day
FROM pf_sgl_amt_day
WHERE 1=1
AND d_start_day >= '2025-07-24'
AND disp_yn = 'Y' AND del_yn = 'N'
GROUP BY sgl_amt_seq
) day
ON amt.sgl_amt_seq = day.sgl_amt_seq
where disp.del_yn = 'N' and disp.disp_yn = 'Y' and disp.disp_kind = 'J' and disp.my_db_id = '560' and disp.start_day <= '2025-07-24' and disp.end_day >= '2025-07-24' and disp.nat_cd = '' and disp.city_cd = '' and sgl.nat_cd = 'KOR' and sgl.disp_yn = 'Y' and sgl.del_yn = 'N' and disp.cls_det_cd = '2323'
ORDER BY disp.disp_order
