Edit Report:C5A Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- C5A Report
-- Select date range.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "salesperson",
-- display: "SalesPerson No",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "Total": "Total: {{sum}}"
-- }
-- }
SELECT
b.debtorno,
b.branchcode,
b.brname,
b.salesman,
club_joined,
accountid,
(
SELECT
sum( discountedlinetotal *- 1 )
FROM
stocktrans
WHERE
supplierno = 'RK'
AND categoryid IN ( 'CL', 'DV', 'BL','INC', 'IN', '99', '99C' )
AND trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND debtorno = c.cust_id
AND branchcode = b.branchcode
) AS 'professional total',
(
SELECT
sum( discountedlinetotal *- 1 )
FROM
stocktrans
WHERE
supplierno = 'RK'
AND trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND debtorno = c.cust_id
AND branchcode = b.branchcode
) AS 'redken total'
FROM
clubs_joined c
JOIN custbranch b ON b.debtorno = c.cust_id
AND b.branchcode = c.branchcode
WHERE
clubid = 9
AND b.debtorno = c.cust_id
AND b. branchcode = c.branchcode