Geek asks and answers

Mysql request to create a commission report based on specified members

A person receives a commission of 10% for purchases made by his mentioned friends.

There are two tables:

  • Reference table
  • Transaction table

Reference table

Person_id    Referrer_id
3             1
4             1
5             1
6             2

Transaction table

   Person_id   Amount     Action     Date
   3           100        Purchase   10-20-2011
   4           200        Purchase   10-21-2011
   6           400        Purchase   12-15-2011
   3           200        Purchase   12-30-2011
   1            50        Commision  01-01-2012
   1            10        Cm_Bonus   01-01-2012
   2            20        Commision  01-01-2012

How to get the following result set for Referrer_Person_id = 1

Month     Ref_Pur  Earn_Comm   Todate_Earn_Comm   BonusRecvd  Paid   Due
10-2011    300      30          30                  0           0     30
11-2011      0       0          30                  0           0     30
12-2011    200      20          50                  0           0     50
01-2012      0       0          50                 10          50      0


Shortcuts used above:

Ref_Pur          = Total Referred Friend Purchase for that month

Earn_Comm        = 10% Commision earned for that month

Todate_Earn_Comm = Total Running Commision earned upto that month

MYSQL CODE I wrote

SELECT dx1.month,
       dx1.ref_pur,
       dx1.earn_comm,
       ( @cum_earn := @cum_earn + dx1.earn_comm ) as todate_earn_comm

FROM

(
    select date_format(`date`,'%Y-%m') as month,
           sum(amount) as ref_pur , 
           (sum(amount)*0.1) as earn_comm
    from transaction tr, reference rf
    where tr.person_id=rf.person_id and
          tr.action='Purchase' and
          rf.referrer_id=1
    group by date_format(`date`,'%Y-%m')
    order by date_format(`date`,'%Y-%m')

)as dx1

JOIN (select @cum_earn:=0)e;

How to join the request to also include BonusRecvd, Paid, and Due trnsactions, which is independent of the lookup table?

and also create a row for the month '11 -2011 ', although this month there was no trnx

+3
source share
1 answer

, , , (Action IN ('Commision', 'Cm_Bonus')) , . , , , , :

SELECT t.Amount, t.Action, t.Date
FROM Transaction t LEFT JOIN Reference r ON t.Person_id = r.Person_id
WHERE r.Referrer_id = 1 AND t.Action = 'Purchase'
   OR t.Person_id   = 1 AND t.Action IN ('Commision', 'Cm_Bonus')

SUMs CASE , Action. :

…
IFNULL(SUM(CASE Action WHEN 'Purchase'  THEN Amount END)      , 0) AS Ref_Pur,
IFNULL(SUM(CASE Action WHEN 'Purchase'  THEN Amount END) * 0.1, 0) AS Earn_Comm,
IFNULL(SUM(CASE Action WHEN 'Cm_Bonus'  THEN Amount END)      , 0) AS BonusRecvd,
IFNULL(SUM(CASE Action WHEN 'Commision' THEN Amount END)      , 0) AS Paid
…

Due @cum_earn, , Paid, - :

(@cum_due := @cum_due + Earn_Comm - Paid) AS Due

, - . , :

  • , ( ).

  • (.. , ).

  • , , , .

  • , , , , .

, ", ". , () .

, 3, - , . , , . :

CREATE TABLE numbers (n int);
INSERT INTO numbers (n) SELECT 0;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
/* repeat as necessary; every repeated line doubles the number of rows */

. , -, , . , , SQL Fiddle " ".

+1

More articles:


All Articles