ISSUE_1: 여러번의 LEFT JOIN과 수식계산 오류

얼핏 보면 같은 역할을 하는 쿼리문 같아 보여도 완전 다른 결과를 내놓는 경우였다.

환자가 그날 수납했던 금액이 원본 쿼리랑 비교했을 때보다 2배로 집계되는 경우가 간혹 있었다.

(나머지 쿼리에 대한 결과는 다 맞는데…!)

다음은 환자 스케줄 정보를 가지고 오는 쿼리(원본 쿼리)이다

const query = `SELECT P.CUSTNO AS CUSTNO, P.CUSTNAME AS CUSTNAME, P.CUSTGENDER AS CUSTGENDER, FLOOR((TO_DAYS(NOW()) - TO_DAYS(CONCAT((CASE 
        WHEN SUBSTR(P.CUSTJN,7,1) IN(1,2,5,6) THEN "19"
        WHEN SUBSTR(P.CUSTJN,7,1) IN(3,4,7,8) THEN "20"
        ELSE "18"
        END
        ), LEFT(P.CUSTJN,6))))/365) AS AGE,
        S.SCHDOCTOR, S.SCHNURSE,
        P.CUSTADDR11 AS CUSTADDR11, IF(TSALE.SCHEDULEID AND TSALE.POSTURE = "OUT", 1, 0) AS TPRESCRIPTION,
        IFNULL(SPAY.CLAIMAMT,0) + IFNULL(SPAY.LIABILITYAMT,0) + IFNULL(SPAY.NONINSAMT,0) -IFNULL(SPAY.TAXDCAMT,0) - IFNULL(SPAY.DISCOUNTAMT,0) AS TOTALAMOUNT,
        IFNULL(SPAY.LIABILITYAMT,0)+ IFNULL(SPAY.NONINSAMT,0) AS LIABILITYAMT,
        IFNULL(SPAY.LIABILITYAMT,0) + IFNULL(SPAY.NONINSAMT,0) + IFNULL(SPAY.LTCHARGEAMT,0) - IFNULL(SPAY.DISCOUNTAMT,0) - IFNULL(SPAY.TAXDCAMT,0) AS PAYAMOUNT, 
        S.CUSTOMERID AS CUSTOMERID, S.SCHEDULEID
        FROM TCUSTOMERSCHEDULE S 
        LEFT JOIN TSALESTATEMENT SPAY ON S.SCHEDULEID = SPAY.SCHEDULEID
        LEFT JOIN TCUSTOMERPERSONAL P ON S.CUSTOMERID = P.CUSTOMERID
        LEFT JOIN TSALEITEM TSALE ON S.SCHEDULEID= TSALE.SCHEDULEID
        WHERE S.SCHEDULEDATE = ${date} AND ((S.SCHEDULESTATUS <> 0) AND (S.SCHEDULESTATUS <> 1)) AND S.ORGID = ${orgID} AND P.DISCD <> 1
        GROUP BY S.SCHEDULEID
        ORDER BY S.SCHEDULETIME DESC
        LIMIT ?, ?
        `; 

      db.query(
        query,
        [
          parseInt(searchCond.STARTROW) - 1,
          parseInt(searchCond.ENDROW) - parseInt(searchCond.STARTROW) + 1,
        ],
        function (error, results) {
          if (error) {
            logger.error('getTodayCustomerList : error=' + error);
            callback(error, null);
            return;
          }

          async.each(results,
            function(schedule, itercb) {
              console.log(schedule.SCHEDULEID, schedule.CUSTNAME)
              var sql =
                "SELECT SCHEDULEID, IFNULL(SUM(IF(REFUNDFLAG = 1 OR LEFT(PAYMENTCODE, 1) = 'D', PAYMENTAMT * -1, PAYMENTAMT)), 0) PAYMENTAMT, \\n" +
                "       MAX(PAYMENTCODE) PAYMENTCODE FROM TPAYMENT \\n" + "  WHERE SCHEDULEID = ?  \\n";
              db.query(sql, [schedule.SCHEDULEID], function(err, res) {
                if (err) {
                  logger.error('getTodayCustomerList : error=' + err);
                  callback(err, null);
                  return;
                }
                schedule.PAYMENTAMT = res[0].PAYMENTAMT;
                schedule.PAYMENTCODE = res[0].PAYMENTCODE;
                schedule.UNPAIDAMT = schedule.ORDERAMT + schedule.LTCHARGEAMT - schedule.PAYMENTAMT - schedule.REFUNDAMT;
                schedule.NUNPAIDAMT = 0;
                itercb(null);
              });
            },

그리고 다음은 직접 작성한 스케줄 정보 쿼리이다

const query = `SELECT P.CUSTNO AS CUSTNO, P.CUSTNAME AS CUSTNAME, P.CUSTGENDER AS CUSTGENDER, FLOOR((TO_DAYS(NOW()) - TO_DAYS(CONCAT((CASE 
    WHEN SUBSTR(P.CUSTJN,7,1) IN(1,2,5,6) THEN "19"
    WHEN SUBSTR(P.CUSTJN,7,1) IN(3,4,7,8) THEN "20"
    ELSE "18"
    END
    ), LEFT(P.CUSTJN,6))))/365) AS AGE,
    S.SCHDOCTOR, S.SCHNURSE,
    P.CUSTADDR11 AS CUSTADDR11, IF(TSALE.SCHEDULEID AND TSALE.POSTURE = "OUT", 1, 0) AS TPRESCRIPTION,
    IFNULL(SPAY.CLAIMAMT,0) + IFNULL(SPAY.LIABILITYAMT,0) + IFNULL(SPAY.NONINSAMT,0) -IFNULL(SPAY.TAXDCAMT,0) - IFNULL(SPAY.DISCOUNTAMT,0) AS TOTALAMOUNT,
    IFNULL(SPAY.LIABILITYAMT,0)+ IFNULL(SPAY.NONINSAMT,0) AS LIABILITYAMT,
    IFNULL(SPAY.LIABILITYAMT,0) + IFNULL(SPAY.NONINSAMT,0) + IFNULL(SPAY.LTCHARGEAMT,0) - IFNULL(SPAY.DISCOUNTAMT,0) - IFNULL(SPAY.TAXDCAMT,0) AS PAYAMOUNT, 
    IF(PAY.SCHEDULEID, IFNULL(SUM(IF(REFUNDFLAG = 1 OR LEFT(PAYMENTCODE, 1) = 'D', PAYMENTAMT * -1, PAYMENTAMT)), 0),0) AS PAYMENTAMT,
    S.CUSTOMERID AS CUSTOMERID
    FROM TCUSTOMERSCHEDULE S 
    JOIN TSALESTATEMENT SPAY ON S.SCHEDULEID = SPAY.SCHEDULEID
    LEFT JOIN TCUSTOMERPERSONAL P ON S.CUSTOMERID = P.CUSTOMERID
    LEFT JOIN TPAYMENT PAY ON S.SCHEDULEID = PAY.SCHEDULEID
    LEFT JOIN TSALEITEM TSALE ON S.SCHEDULEID= TSALE.SCHEDULEID
    WHERE S.SCHEDULEDATE = ${date} AND ((S.SCHEDULESTATUS <> 0) AND (S.SCHEDULESTATUS <> 1)) AND S.ORGID = ${orgid} AND P.DISCD <> 1
    GROUP BY S.SCHEDULEID
    ORDER BY S.SCHEDULETIME DESC
    LIMIT ?, ?
    `;

차이점 분석

원본 쿼리는 스케줄과 스케줄에 대한 매출을 가지고 오고, 그 결과를 하나씩 순회하면서 일일이 환자가 실제 수납한 금액을 계산해서 돌려주고 있다.

작성한 쿼리는 쿼리 한번에 실제 수납한 금액까지 같이 계산하고 있다.

SELECT 부분은 잠시 제외하고 FROM ~ JOIN 부분만 비교해보자