Update_Daily_Txn_Status

UPDATE
-- SELECT *
-- FROM
  (SELECT l.responseTime, trnxCode, srcAC, SUM(CASE WHEN originalTxnId IS NULL THEN amount ELSE -amount END ) total
     , SUM(CASE WHEN originalTxnId IS NULL THEN 1 ELSE -1 END ) count
FROM profino.sc_user_temp_profile p
INNER JOIN profino.sc_transaction_log l ON p.userName = l.srcAC
  WHERE l.trnxCode IN (10, 10510, 10511)
  AND date(l.responseTime) BETWEEN '2017-05-01' AND '2017-05-31'
AND p.distributorWallet = '016161040403'
GROUP BY date(responseTime), trnxCode, srcAC) log
LEFT JOIN profino.sc_daily_txn_status st
    ON log.srcAC = st.wallet
AND log.trnxCode = st.txnCode
AND date(log.responseTime) = date(st.currentDate)
  AND date(st.currentDate) BETWEEN '2017-05-01' AND '2017-05-31'
SET st.totalDebit = log.total,
  st.totalTxnNumber = log.count
;

UPDATE
-- SELECT *
-- FROM
  (SELECT l.responseTime, trnxCode, srcAC, SUM(CASE WHEN originalTxnId IS NULL THEN amount ELSE -amount END ) total
     , SUM(CASE WHEN originalTxnId IS NULL THEN 1 ELSE -1 END ) count
FROM profino.sc_user_temp_profile p
INNER JOIN profino.sc_transaction_log l ON p.userName = l.srcAC
  WHERE l.trnxCode IN (107,10701,10702)
  AND date(l.responseTime) BETWEEN '2017-05-01' AND '2017-05-31'
AND p.distributorWallet = '016161040403'
GROUP BY date(responseTime), trnxCode, srcAC) log
LEFT JOIN profino.sc_daily_txn_status st
    ON log.srcAC = st.wallet
AND log.trnxCode = st.txnCode
AND date(log.responseTime) = date(st.currentDate)
  AND date(st.currentDate) BETWEEN '2017-05-01' AND '2017-05-31'
SET st.totalDebit = log.total,
  st.totalTxnNumber = log.count
;


INSERT INTO profino.sc_daily_txn_status (id, wallet, currentDate, txnCode, totalDebit, totalTxnNumber)
SELECT concat(date(log.responseTime), '-',log.srcAC, '-', txnCode), log.srcAC, date(log.responseTime), log.trnxCode, log.total, log.count
FROM
  (SELECT l.responseTime, trnxCode, srcAC, SUM(CASE WHEN originalTxnId IS NULL THEN amount ELSE -amount END ) total
     , SUM(CASE WHEN originalTxnId IS NULL THEN 1 ELSE -1 END ) count
FROM profino.sc_user_temp_profile p
INNER JOIN profino.sc_transaction_log l ON p.userName = l.srcAC
  WHERE l.trnxCode IN (10,10510,10511)
  AND date(l.responseTime) BETWEEN '2017-05-01' AND '2017-05-31'
AND p.distributorWallet = '016161040403'
GROUP BY date(responseTime), trnxCode, srcAC) log
LEFT OUTER JOIN profino.sc_daily_txn_status st
    ON log.srcAC = st.wallet
AND log.trnxCode = st.txnCode
AND date(log.responseTime) = date(st.currentDate)
  AND date(st.currentDate) BETWEEN '2017-05-01' AND '2017-05-31'
WHERE st.id IS NULL
;


INSERT INTO profino.sc_daily_txn_status (id, wallet, currentDate, txnCode, totalDebit, totalTxnNumber)
SELECT concat(date(log.responseTime), '-',log.srcAC, '-', txnCode), log.srcAC, date(log.responseTime), log.trnxCode, log.total, log.count
FROM
  (SELECT l.responseTime, trnxCode, srcAC, SUM(CASE WHEN originalTxnId IS NULL THEN amount ELSE -amount END ) total
     , SUM(CASE WHEN originalTxnId IS NULL THEN 1 ELSE -1 END ) count
FROM profino.sc_user_temp_profile p
INNER JOIN profino.sc_transaction_log l ON p.userName = l.srcAC
  WHERE l.trnxCode IN (107,10701,10702)
  AND date(l.responseTime) BETWEEN '2017-05-01' AND '2017-05-31'
AND p.distributorWallet = '016161040403'
GROUP BY date(responseTime), trnxCode, srcAC) log
LEFT OUTER JOIN profino.sc_daily_txn_status st
    ON log.srcAC = st.wallet
AND log.trnxCode = st.txnCode
AND date(log.responseTime) = date(st.currentDate)
  AND date(st.currentDate) BETWEEN '2017-05-01' AND '2017-05-31'
WHERE st.id IS NULL
;