SHARE
TWEET

sql create temptable uutuk ambil data redeem komisi

a guest Sep 19th, 2019 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.           CREATE TEMPORARY TABLE tmp_0e33c2108ed451dcb62bddb72a77e9fd
  3.           SELECT tbl.MemberID, tbl.Member, SUM(tbl.jumkomisi) AS 'TotKomisi', tbl.Bank AS 'Bank', tbl.SandiNi, aktif,
  4.  
  5.           (CASE
  6.             WHEN tbl.SandiNi='0011111' AND SUM(tbl.jumkomisi)<10000 THEN 'Tidak Transfer'
  7.             WHEN tbl.SandiNi!='0011111' AND SUM(tbl.jumkomisi)<15000 THEN 'Tidak Transfer'
  8.             ELSE 'Transfer'
  9.           END) AS transfer
  10.  
  11.           FROM
  12.  
  13.           (
  14.           SELECT  rg.ID AS MemberID, CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',  
  15.           SUM(kd.Nilai) AS jumkomisi,
  16.           bi.NAMA_BANK AS Bank,
  17.           bi.SANDI_NI AS 'SandiNi',
  18.           kd.Jenis AS Jenis,
  19.           CASE WHEN EXISTS(SELECT * FROM PenjualanData WHERE PenjualID=MemberID AND Tgl  BETWEEN '2019-8-20' AND '2019-09-19' AND StatusPembayaran='PAID') AND (SELECT sum(Total) FROM PenjualanData WHERE PenjualID=MemberID AND Tgl   BETWEEN '2019-8-20' AND '2019-09-19' AND StatusPembayaran='PAID') >= 64000 THEN 'Aktif' ELSE 'Hangus' END AS aktif
  20.  
  21.           FROM KomisiData kd
  22.           LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
  23.           LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
  24.           LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
  25.           LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
  26.           LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
  27.  
  28.           WHERE kd.isRedeem = 0
  29.           AND kd.TglKomisi  BETWEEN '2019-8-20' AND '2019-09-19'
  30.           AND kd.Jenis='JUAL'
  31.           AND (SELECT COUNT(*) FROM PenjualanData WHERE PenjualID=MemberID AND Tgl  BETWEEN '2019-8-20' AND '2019-09-19' AND StatusPembayaran='PAID')>0
  32.           GROUP BY Member,memberid,bank,sandini,jenis
  33.  
  34.           UNION ALL
  35.  
  36.           SELECT  rg.ID AS MemberID, CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',  
  37.           SUM(kd.Nilai) AS jumkomisi,
  38.           bi.NAMA_BANK AS Bank,
  39.           bi.SANDI_NI AS 'SandiNi',
  40.           kd.Jenis AS Jenis,
  41.           'Aktif' AS aktif
  42.  
  43.           FROM KomisiData kd
  44.           LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
  45.           LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
  46.           LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
  47.           LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
  48.           LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
  49.  
  50.           WHERE kd.isRedeem = 0
  51.           AND kd.TglKomisi  BETWEEN '2019-8-20' AND '2019-09-19'
  52.           AND (kd.Jenis='DAFTAR' OR kd.Jenis='KODEUNIK')
  53.  
  54.           GROUP BY Member,memberid,bank,sandini,jenis
  55.  
  56.           UNION ALL
  57.  
  58.           SELECT  rg.ID AS MemberID, CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',  
  59.           SUM(kd.Nilai) AS jumkomisi,
  60.           bi.NAMA_BANK AS Bank,
  61.           bi.SANDI_NI AS 'SandiNi',
  62.           kd.Jenis AS Jenis,
  63.           'Aktif' AS aktif
  64.  
  65.           FROM KomisiData kd
  66.           LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
  67.           LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
  68.           LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
  69.           LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
  70.           LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
  71.  
  72.           WHERE kd.isRedeem = 0 and kd.Jenis != 'KODEUNIK'
  73.           AND kd.TglKomisi < '2019-08-20'
  74.  
  75.           GROUP BY Member,memberid,bank,sandini,jenis
  76.  
  77.           ) tbl
  78.  
  79.           GROUP BY Member,memberid,bank,sandini,aktif
  80.  
  81.           HAVING NOT  (
  82.             (SandiNi='0011111' AND SUM(jumkomisi)<10000 ) OR  
  83.             ( SandiNi!='0011111' AND SUM(jumkomisi)<15000 ))"
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top