아래의 1,2,3,4 번을 간단히 표현할수 있을까요?? 중복 되는데 where 조건만 조금씩 틀린데 간단히 할수 있는 방법이 있으면 알려주세용..
감사합니다
1.
SELECT DISTINCT ISNULL(COUNT(MM.Sup_No), 0) as Sup_No,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.acc_count, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.acc_count_spcl, 0) ELSE 0 END) AS GJ,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.PBLC_AMT, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.SPCL_AMT, 0) ELSE 0 END) AS AMT
INTO #RESULT1
FROM GT..tblmember MM
LEFT JOIN (SELECT SUP_NO, ISNULL(SUM(Rcpt_Amt), 0) AS IN_AMT FROM GT..TBLCREDIT GROUP BY Sup_No ) as CRR on mm.Sup_No = CRR.SUP_NO
LEFT JOIN NP..tblnbanktran t2 inner join (SELECT RDNO, max (SEQ) AS seq FROM NP..tblnbanktran WHERE RDNO LIKE 'T%'GROUP BY RDNO) as t1 on t1.RDNO = t2.RDNO And t1.SEQ = t2.SEQ on mm.Sup_No = t2.RDNO
WHERE MM.SUP_NO IS NOT NULL
AND MM.PER_NM IS NOT NULL
AND MM.PblcYN = 'Y'
AND MM.REG_DT BETWEEN '20130301' AND '20140430'
AND CRR.IN_AMT >0
AND (MM.CANCEL_DT = '' OR MM.LIVE_YN = 'Y')
2.
SELECT DISTINCT ISNULL(COUNT(MM.Sup_No), 0) Sup_No,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.acc_count, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.acc_count_spcl, 0) ELSE 0 END) AS GJ,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.PBLC_AMT, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.SPCL_AMT, 0) ELSE 0 END) AS AMT
INTO #RESULT2
FROM GT..tblmember MM
LEFT JOIN (SELECT SUP_NO, ISNULL(SUM(Rcpt_Amt), 0) AS IN_AMT FROM GT..TBLCREDIT GROUP BY Sup_No ) as CRR on mm.Sup_No = CRR.SUP_NO
LEFT JOIN NP..tblnbanktran t2 inner join (SELECT RDNO, max (SEQ) AS seq FROM NP..tblnbanktran WHERE RDNO LIKE 'T%'GROUP BY RDNO) as t1 on t1.RDNO = t2.RDNO And t1.SEQ = t2.SEQ on mm.Sup_No = t2.RDNO
WHERE MM.SUP_NO IS NOT NULL
AND MM.PER_NM IS NOT NULL
AND MM.PblcYN = 'Y'
AND MM.REG_DT BETWEEN '20130301' AND '20140430'
AND (CRR.IN_AMT = 0 OR CRR.IN_AMT IS NULL)
AND (MM.CANCEL_DT = '' OR MM.LIVE_YN = 'Y')
3.
SELECT DISTINCT ISNULL(COUNT(MM.Sup_No), 0) Sup_No,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.acc_count, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.acc_count_spcl, 0) ELSE 0 END) AS GJ,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.PBLC_AMT, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.SPCL_AMT, 0) ELSE 0 END) AS AMT
INTO #RESULT3
FROM GT..tblmember MM
LEFT JOIN (SELECT SUP_NO, ISNULL(SUM(Rcpt_Amt), 0) AS IN_AMT FROM GT..TBLCREDIT GROUP BY Sup_No ) as CRR on mm.Sup_No = CRR.SUP_NO
LEFT JOIN NP..tblnbanktran t2 inner join (SELECT RDNO, max (SEQ) AS seq FROM NP..tblnbanktran WHERE RDNO LIKE 'T%'GROUP BY RDNO) as t1 on t1.RDNO = t2.RDNO And t1.SEQ = t2.SEQ on mm.Sup_No = t2.RDNO
WHERE MM.SUP_NO IS NOT NULL
AND MM.PER_NM IS NOT NULL
AND MM.PblcYN = 'Y'
AND MM.REG_DT BETWEEN '20130301' AND '20140430'
AND CRR.IN_AMT >0
AND (MM.CANCEL_DT <> '' OR MM.LIVE_YN <> 'Y')
4.
SELECT DISTINCT ISNULL(COUNT(MM.Sup_No), 0) Sup_No,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.acc_count, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.acc_count_spcl, 0) ELSE 0 END) AS GJ,
SUM(CASE WHEN MM.PblcYN = 'Y' THEN ISNULL(MM.PBLC_AMT, 0) ELSE 0 END + CASE WHEN MM.SpclYN = 'Y' THEN ISNULL(MM.SPCL_AMT, 0) ELSE 0 END) AS AMT
INTO #RESULT4
FROM GT..tblmember MM
LEFT JOIN (SELECT SUP_NO, ISNULL(SUM(Rcpt_Amt), 0) AS IN_AMT FROM GT..TBLCREDIT GROUP BY Sup_No ) as CRR on mm.Sup_No = CRR.SUP_NO
LEFT JOIN NP..tblnbanktran t2 inner join (SELECT RDNO, max (SEQ) AS seq FROM NP..tblnbanktran WHERE RDNO LIKE 'T%'GROUP BY RDNO) as t1 on t1.RDNO = t2.RDNO And t1.SEQ = t2.SEQ on mm.Sup_No = t2.RDNO
WHERE MM.SUP_NO IS NOT NULL
AND MM.PER_NM IS NOT NULL
AND MM.PblcYN = 'Y'
AND MM.REG_DT BETWEEN '20130301' AND '20140430'
AND (CRR.IN_AMT = 0 OR CRR.IN_AMT IS NULL)
AND (MM.CANCEL_DT <> '' OR MM.LIVE_YN <> 'Y')