SELECT substr(a.base_ymd, 1, 4), b.adong_nm,
	(CASE WHEN tmz_div_cd = 01 THEN '00~06시'
		WHEN tmz_div_cd = 02 THEN '00~06시'
     	WHEN tmz_div_cd = 03 THEN '00~06시'
    	WHEN tmz_div_cd = 04 THEN '00~06시'
     	WHEN tmz_div_cd = 05 THEN '00~06시'
     	WHEN tmz_div_cd = 06 THEN '00~06시'
     	WHEN tmz_div_cd = 06 THEN '00~06시'
     ELSE '_'	
     END) AS time, d.cd_nm, e.cd_nm, SUM(a.ojuri_tou_num)
FROM dw.trd_st_ojuri_tou_adong_by_gen_ageg_sum a ---TRD_ST_관외관광객행정동유입집계
LEFT OUTER JOIN
	(SELECT DISTINCT adong_cd, adong_nm, sido_cd, sido_nm, sgg_cd, sgg_nm FROM dw.trd_ms_adong_bas) b ---행정동코드 
	ON a.adong_cd = b.adong_cd
LEFT OUTER JOIN
	(SELECT DISTINCT cd_grp_cd, cd_grp_nm, cd, cd_nm FROM com.trc_ms_cd_bas WHERE cd_grp_cd = "G1008") d
	ON a.gen_cd = d.cd
LEFT OUTER JOIN
	(SELECT DISTINCT cd_grp_cd, cd_grp_nm, cd, cd_nm FROM com.trc_ms_cd_bas WHERE cd_grp_cd = "G1004") e
	ON a.ageg_div_cd = e.cd
WHERE substr(a.base_ymd,1,4) in (2019, 2021) AND b.adong_nm = '대명6동'
GROUP BY substr(a.base_ymd, 1, 4), b.adong_nm, a.tmz_div_cd, d.cd_nm, e.cd_nm ;