|
아래와 같이 함수를 많이 사용
해서 그런지
속도가 장난 아니게 늦어요
...
뭐 좋은 방법이 없을
까요
/* ※
// 판매구분 (SALEGBN) : 01=소매, 02=도매, 03=인터넷, 04=재고이동
// 의뢰STATUS : 1 =의뢰, 2 =진행, 3 = 반려 , 4=취소
,5=완료
// 입고 확인 : 1=입고대기, 2=부분입고, 3=추가입고,
5=입고확인
// 판매상태(STATUS ) : 1=
선결제 , 2 = 일반
// 품목구분(proggbn2) :01=도서 ,02=음반 ,03=용품 ,04=S/W
, 05=A/B
// 제품구분(proggbn1) :01=제품 ,02=상품 ,03=수입상품
// 출고구분(outgbn) : 1=정상 , 2=증정 , 3=반품
판매기본[SALEMST] 판매상세 [SALEDET]
판매내역[PAYMST]
사업장 pk 사업장 pk
사업장 pk
판매일자 pk 판매일자 pk
판매일자 pk
판매번호 pk 판매번호 pk
판매번호 pk
판매구분 판매순번 pk 순번
pk
판매상태 품목코드 = ISBN + SEQ (품목정보의)
택배비
판매수량
운송비
판매금액
가입비
출고구분
봉투보증금
품목정보[PRODUCT]
ISBN
SEQ
제품구분(proggbn1)
품목구분(proggbn2)
매장판매(인터넷판매제외)
인터넷판매
-------------------------------------------------------------------------------------------------------------
구분 | 정상판매 | 선결제 판매 |
정상판매 | 선결제 판매
| 총수 수량 금액 | 총수 수량 금액 | 총수 수량
금액 | 총수 수량 금액
----------------------------------------------------------|--------------------------------------------------
1 |판매 | | |
|
국내도서 |증정 | | |
|
|반품 | | |
|
|소계 | | |
|
----------------------------------------------------------|--------------------------|------------------------
2 |판매 | | |
|
수입도서|증정 | | |
|
|반품 | | |
|
|소계 | | |
|
--------------------------------------|-------------------|--------------------------|------------------------
3 |판매 | | |
|
음반 |증정 | | |
|
|반품 | | |
|
|소계 | | |
|
--------------------------------------|-------------------|--------------------------|------------------------
4 |판매 | | |
|
용품 |증정 | | |
|
|반품 | | |
|
|소계 | | |
|
--------------------------------------|-------------------|--------------------------|------------------------
|판매 | | |
|
|증정 | | |
|
6 |반품 | | |
|
S/W |소계 | | |
|
-------------------------------------|-------------------|--------------------------|------------------------
회원가입비 | | |
|
택배비 | | |
|
운송비 | | |
|
---------------------------------------------------------|---------------------------------------------------
합 계 | | |
|
-------------------------------------------------------------------------------------------------------------
인터넷 판매분과 정상 판매분의 구분 하기를 요청 함.
하여
SELECT A.CPNYCD ,
/* 1 매장 판매 국내도서 */
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201021' THEN A.PRODCD END) CNT11, /*일반/국내도서/판매
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201022' THEN A.PRODCD END) CNT12, /*일반/국내도서/증정
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201023' THEN A.PRODCD END) CNT13, /*일반/국내도서/반품
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1
WHEN '20102' THEN A.PRODCD END) CNT14, /*일반/국내도서/소계 */
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101021' THEN A.PRODCD END) CNT15, /*선결제/국내도서/판매
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101022' THEN A.PRODCD END) CNT16, /*선결제/국내도서/증정
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101023' THEN A.PRODCD END) CNT17, /*선결제/국내도서/반품
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1
WHEN '10102' THEN A.PRODCD END) CNT18, /*선결제/국내도서/소계 */
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201021' THEN A.SALEQTY END),0) QTY11, /*일반/국내도서/판매
*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201022' THEN A.SALEQTY END),0) QTY12, /*일반/국내도서/증정
*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201023' THEN A.SALEQTY END),0) QTY13, /*일반/국내도서/반품
*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101021' THEN A.SALEQTY END),0) QTY14, /*일반/국내도서/소계 */
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101022' THEN A.SALEQTY END),0) QTY15, /*선결제/국내도서/판매
*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101023' THEN A.SALEQTY END),0) QTY16, /*선결제/국내도서/증정
*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201021' THEN A.KSALEAMT - A.PREAMT END),0) AMT11,
/*일반/국내도서/판매금액*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201023' THEN A.KSALEAMT - A.PREAMT END),0) AMT13,
/*일반/국내도서/반품금액*/
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101021' THEN A.PREAMT END),0) AMT14,
ISNULL(SUM( CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101023' THEN A.PREAMT END),0) AMT16 ,
0 icnt......
FROM SALEDET A, SALEMST B,
PRODUCT C,
(SELECT ISNULL(SUM(CASE B.STATUS WHEN '2' THEN A.ENTERAMT
END),0) ENTERAMT1, /*일반/회원가입비 */
ISNULL(SUM(CASE B.STATUS WHEN '1' THEN A.ENTERAMT
END),0) ENTERAMT2, /*선결제/회원가입비*/
ISNULL(SUM(CASE B.STATUS WHEN '2' THEN A.TRANSAMT
END),0) TRANSAMT1, /*일반/택배비 */
ISNULL(SUM(CASE B.STATUS WHEN '1' THEN A.TRANSAMT
END),0) TRANSAMT2, /*선결제/택배비 */
ISNULL(SUM(CASE B.STATUS WHEN '2' THEN A.FTRANAMT
END),0) FTRANAMT1, /*일반 /운송비 */
ISNULL(SUM(CASE B.STATUS WHEN '1' THEN A.FTRANAMT
END),0) FTRANAMT2 /*선결제/운송비 */
FROM SALEPAY A, SALEMST B
WHERE A.CPNYCD = B.CPNYCD
AND A.SALEYMD = B.SALEYMD
AND A.SALENO = B.SALENO
AND A.CPNYCD = '01'
AND B.SALEGBN LIKE '%'
AND B.SALEGBN <> '03'
AND A.SALEYMD >= '20050131'
AND A.SALEYMD <= '20050131'
AND B.STATUS IN ('1','2')
AND NOT EXISTS ( SELECT SALENO FROM SALEDET
WHERE CPNYCD = B.CPNYCD
AND SALEYMD = B.SALEYMD
AND SALENO = B.SALENO
AND CPNYCD = '01'
AND SALEYMD >= '20050131'
AND SALEYMD <= '20050131'
AND SALEGBN LIKE '%'
AND SALEGBN <> '03'
AND DELYN = 'Y') ) D
WHERE A.CPNYCD = B.CPNYCD
AND A.SALEYMD =
B.SALEYMD
AND A.SALENO = B.SALENO
AND A.PRODCD = C.ISBNCD + C.SEQ
AND A.CPNYCD = '01'
AND A.SALEYMD >= '20050131'
AND A.SALEYMD <= '20050131'
AND B.SALEGBN LIKE '%'
AND B.SALEGBN <> '03'
AND B.STATUS IN ('1','2')
AND A.DELYN = 'N'
GROUP BY A.CPNYCD
UNION ALL
SELECT A.CPNYCD ,
0 CNT11 ......
/* 인터넷 국내도서 */
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201021' THEN A.PRODCD END) iCNT11, /*일반/국내도서/판매
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201022' THEN A.PRODCD END) iCNT12, /*일반/국내도서/증정
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '201023' THEN A.PRODCD END) iCNT13, /*일반/국내도서/반품
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1
WHEN '20102' THEN A.PRODCD END) iCNT14, /*일반/국내도서/소계 */
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101021' THEN A.PRODCD END) iCNT15, /*선결제/국내도서/판매
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101022' THEN A.PRODCD END) iCNT16, /*선결제/국내도서/증정
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN
WHEN '101023' THEN A.PRODCD END) iCNT17, /*선결제/국내도서/반품
*/
COUNT(DISTINCT CASE B.STATUS+C.PROGGBN2+C.PROGGBN1
WHEN '10102' THEN A.PRODCD END) iCNT18, /*선결제/국내도서/소계 */
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'201021' THEN A.SALEQTY END),0) iQTY11, /*일반/국내도서/판매
*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'201022' THEN A.SALEQTY END),0) iQTY12, /*일반/국내도서/증정
*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'201023' THEN A.SALEQTY END),0) iQTY13, /*일반/국내도서/반품
*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'101021' THEN A.SALEQTY END),0) iQTY14, /*일반/국내도서/소계 */
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'101022' THEN A.SALEQTY END),0) iQTY15, /*선결제/국내도서/판매
*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'101023' THEN A.SALEQTY END),0) iQTY16, /*선결제/국내도서/증정
*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'201021' THEN A.KSALEAMT - A.PREAMT END),0) iAMT11,
/*일반/국내도서/판매금액*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'201023' THEN A.KSALEAMT - A.PREAMT END),0) iAMT13,
/*일반/국내도서/반품금액*/
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'101021' THEN A.PREAMT END),0) iAMT14,
ISNULL(SUM(CASE B.STATUS+C.PROGGBN2+C.PROGGBN1+A.OUTGBN WHEN
'101023' THEN A.PREAMT END),0) iAMT16
FROM SALEDET A, SALEMST B,
PRODUCT C,
(SELECT ISNULL(SUM(CASE B.STATUS WHEN '2' THEN A.ENTERAMT
END),0) ENTERAMT1, /*일반/회원가입비 */
ISNULL(SUM(CASE B.STATUS WHEN '1' THEN A.ENTERAMT
END),0) ENTERAMT2, /*선결제/회원가입비*/
ISNULL(SUM(CASE B.STATUS WHEN '2' THEN A.TRANSAMT
END),0) TRANSAMT1,
ISNULL(SUM(CASE B.STATUS WHEN '1' THEN A.TRANSAMT
END),0) TRANSAMT2,
ISNULL(SUM(CASE B.STATUS WHEN '2' THEN A.FTRANAMT
END),0) FTRANAMT1,
ISNULL(SUM(CASE B.STATUS WHEN '1' THEN A.FTRANAMT
END),0) FTRANAMT2
FROM SALEPAY A, SALEMST B
WHERE A.CPNYCD = B.CPNYCD
AND A.SALEYMD = B.SALEYMD
AND A.SALENO = B.SALENO
AND A.CPNYCD = '01'
AND B.SALEGBN = '03'
AND A.SALEYMD >= '20050131'
AND A.SALEYMD <= '20050131'
AND B.STATUS IN ('1','2')
AND NOT EXISTS ( SELECT SALENO FROM SALEDET
WHERE CPNYCD = B.CPNYCD
AND SALEYMD = B.SALEYMD
AND SALENO = B.SALENO
AND CPNYCD = '01'
AND SALEYMD >= '20050131'
AND SALEYMD <= '20050131'
AND SALEGBN = '03'
AND DELYN = 'Y') ) D
WHERE A.CPNYCD = B.CPNYCD
AND A.SALEYMD = B.SALEYMD
AND A.SALENO = B.SALENO
AND A.PRODCD = C.ISBNCD + C.SEQ
AND A.CPNYCD = '01'
AND A.SALEYMD >= '20050131'
AND A.SALEYMD <= '20050131'
AND B.SALEGBN = '03'
AND B.STATUS IN ('1','2')
AND A.DELYN = 'N'
GROUP BY A.CPNYCD
|
