Á¶È¸µÈ °ª¿¡ ÀÏÀÚº° tot_amt ÃÑÇհ踦 Ãß°¡ÇÏ°í½ÍÀºµ¥¿ä
¾îµð´Ù ³Ö¾î¾ßÇÒÁö ¸ð¸£°Ú½À´Ï´Ù.. ¤Ð¤Ð
2018.01.01 tot_amt ÃÑÇÕ 212,390,123
2018.01.02 tot_amt ÃÑÇÕ 112,390,123 ÀÌ·±½ÄÀ¸·Î¿ä..
SELECT a.visit_date,
a.part_cd,
nvl(A.start_time,A.BOOKG_TIME) start_time,
get_minor_name(a.company_cd,'517',nvl(a.start_course,a.bookg_course)) start_course,
a.bookg_name,
get_minor_name('J23', '702', a.bookg_member_cd) as bookg_member_cd,
a.circle_name,
a.visit_name,
a.execut_no member_no,
nvl(c.d_cnt,0) as d_cnt,
nvl(c.w_cnt,0) as w_cnt,
nvl(c.d_cnt,0) + nvl(c.w_cnt,0) as t_Cnt,
a.locker_key,
nvl(get_minor_name(a.company_cd, '702', a.member_cd),'ÀϹÝ') mem_nm,
b.minor_name hole_nm,
get_minor_name(a.company_cd, '557', a.paymt_cd) paymt_nm,
D.original_amt as std_amt,
d.tot_amt,
D.original_amt - d.tot_amt
FROM fgc102t_v1 a,
fgg104t b,
fgc104t d,
fgg104t e,
(
select a.resno,
a.execut_no,
SUM(DECODE(B.open_yn,'1',1,0)) AS d_cnt,
SUM(DECODE(B.open_yn,'2',1,0)) AS w_cnt
from fgc102t A,
fgg205t B
where A.company_Cd = B.company_Cd
and a.visit_Date = B.work_Date
and A.company_CD = 'J23'
and a.visit_Date between '20180101' AND '20180131'
and a.resno is not null
and a.execut_no is not null
AND NOT EXISTS (
SELECT '*'
FROM FGG104T
WHERE company_cd = A.company_cd
AND minor_cd = A.member_cd
AND major_cd = '702'
AND chg_code1 = 'NOT'
)
AND NOT EXISTS (
SELECT '*'
FROM FGG104T
WHERE company_cd = A.company_cd
AND minor_cd = A.paymt_cd
AND major_cd = '557'
AND chg_code1 = 'NOT'
)
group by a.resno,
a.execut_no
) C
WHERE a.company_cd = 'J23'
AND a.visit_date BETWEEN '20180101' AND '20180131'
AND a.member_cd LIKE '41'
and a.company_Cd = b.company_Cd
and a.hole_cd = b.minor_cd
and b.major_Cd = '504'
and a.resno = C.resno (+)
and a.execut_no = C.execut_no (+)
and a.company_cd = d.company_cd
and a.visit_date = d.visit_date
and a.id_no = d.id_no
and a.company_cd = e.company_cd
and a.paymt_cd = e.minor_cd
and e.major_cd = '557'
and e.minor_cd LIKE '%'
and nvl(a.part_cd,'%') LIKE '%'
|
Ãß°¡·Î °¢ ÀÏÀÚº° amt µéÀÇ 'ÃÑÇÕ' À» Ãß°¡ÇÏ°í ½ÍÀ¸½Å°Ç°¡¿ä?..
Áú¹®ÀÌ ÀßÀÌÇØ°¡¾ÈµË´Ï´Ù^^;
¸¸¾à¸Â´Ù¸é..ÀÏÀÚº° ÃÑÇÕ°ªÀÌ ÀÖ´Â Ä÷³ÀÌÀÖ´ÂÁö È®ÀÎÇغ¸½Ã°í..
ÃÑÇÕ°è°¡ ¾ø´Ù¸é Á÷Á¢ °¢ Ä÷³º°·Î SUM ÇÔ¼ö·Î ±¸ÇØ¿À½Ã¸éµË´Ï´Ù......