Запрос, выводящий топ карт с максимальными суммами покупок за определенный период : УКМ-4

kamres · █ 25.11.2013 18:09

Цитата:

vdm ➤ v49.

SELECT cr.start_card_code card,
sum(if(h.TYPE IN (1, 4, 9, 10), -d.amount, d.amount)) cardsum
FROM
trm_out_receipt_header h
INNER JOIN trm_out_receipt_subtotal d
ON h.cash_id = d.cash_id AND h.id = d.id
INNER JOIN trm_out_receipt_footer f
ON f.cash_id = d.cash_id AND f.id = d.id
INNER JOIN trm_in_cards cr
ON cr.global_id = 0 AND cr.id = h.card
WHERE
cr.deleted = 0 AND cr.active = 1
AND f.RESULT = 0
AND h.TYPE IN (0, 5, 1, 4, 8, 9, 10)
AND f.DATE BETWEEN '2013-10-01 00:00:00' AND '2013-10-01 23:59:59'
GROUP BY card
ORDER BY cardsum DESC
LIMIT 10;

Возвраты вычитаются, карты отбираются только активные (без учета блокировки самого клиента).

А есть ли такой же только для дисконтных карт?

vdm · █ 26.11.2013 13:19

Цитата:

kamres ➤ А есть ли такой же только для дисконтных карт?

Примерно так. Если в чеке несколько карт, его полная сумма пойдет на каждую.

Код:

SELECT ds.card_number card,
       sum(if(h.TYPE IN (1, 4, 9, 10), -d.amount, d.amount)) cardsum
FROM
  trm_out_receipt_header h
INNER JOIN trm_out_receipt_subtotal d
  ON h.cash_id = d.cash_id AND h.id = d.id
INNER JOIN trm_out_receipt_footer f
  ON f.cash_id = d.cash_id AND f.id = d.id
INNER JOIN trm_out_receipt_discounts ds
  ON ds.cash_id = h.cash_id AND ds.receipt_header = h.id
WHERE
  ds.card_number IS NOT NULL
  AND ds.amount<0
  AND f.RESULT = 0
  AND h.TYPE IN (0, 5, 1, 4, 8, 9, 10)
  AND f.DATE BETWEEN '2013-10-01 00:00:00' AND '2013-10-01 23:59:59'
GROUP BY
  ds.card_number
ORDER BY
  cardsum DESC
LIMIT
  10;

kamres · █ 27.11.2013 11:52

Цитата:

vdm ➤ Примерно так. Если в чеке несколько карт, его полная сумма пойдет на каждую.

Код:

SELECT ds.card_number card,
       sum(if(h.TYPE IN (1, 4, 9, 10), -d.amount, d.amount)) cardsum
FROM
  trm_out_receipt_header h
INNER JOIN trm_out_receipt_subtotal d
  ON h.cash_id = d.cash_id AND h.id = d.id
INNER JOIN trm_out_receipt_footer f
  ON f.cash_id = d.cash_id AND f.id = d.id
INNER JOIN trm_out_receipt_discounts ds
  ON ds.cash_id = h.cash_id AND ds.receipt_header = h.id
WHERE
  ds.card_number IS NOT NULL
  AND ds.amount<0
  AND f.RESULT = 0
  AND h.TYPE IN (0, 5, 1, 4, 8, 9, 10)
  AND f.DATE BETWEEN '2013-10-01 00:00:00' AND '2013-10-01 23:59:59'
GROUP BY
  ds.card_number
ORDER BY
  cardsum DESC
LIMIT
  10;

Так не получил данных, а вот так все получилось:

Код:

SELECT ds.card_number card,
       sum(if(h.TYPE IN (1, 4, 9, 10), -d.amount, d.amount)) cardsum
FROM
  trm_out_receipt_header h
INNER JOIN trm_out_receipt_subtotal d
  ON h.cash_id = d.cash_id AND h.id = d.id
INNER JOIN trm_out_receipt_footer f
  ON f.cash_id = d.cash_id AND f.id = d.id
INNER JOIN trm_out_receipt_discounts ds
  ON ds.cash_id = h.cash_id AND ds.receipt_header = h.id
WHERE
  ds.deleted = 0 
  AND ds.card_number IS NOT NULL
  AND f.RESULT = 0
  AND h.TYPE IN (0, 5, 1, 4, 8, 9, 10)
  AND f.DATE BETWEEN '2013-10-01 00:00:00' AND '2013-10-01 23:59:59'
  GROUP BY ds.card_number
  ORDER BY cardsum DESC
  LIMIT 10;