Edit Report:GK Productline Discounts - With Schools
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- GK Productline Discounts - With Schools
-- Select date range.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "Qty": "Total: {{sum}}",
-- "Total Sales": "Total: {{sum}}"
-- }
-- }
SELECT
trandate,
loccode,
transno,
s.debtorno,
branchcode,
brname,
s.lineid,
salesmanname,
s.stockid,
sm.description,
sum(qty *-1) AS 'qty',
s.price,
CAST(
SUM(
s.price * (s.qty * -1)
) AS DECIMAL (10, 2)
) AS prediscounttotal,
(discountpercent * 100) AS discount,
SUM(discountedlinetotal * -1) AS total,
CAST(
SUM(
purchcost * (s.qty * -1)
) AS DECIMAL (10, 2)
) AS totalcost,
(
CAST(
SUM(
s.price * (s.qty * -1)
) AS DECIMAL (10, 2)
) - CAST(
SUM(discountedlinetotal * -1) AS DECIMAL (10, 2)
)
) AS difference
FROM
stocktrans s
INNER JOIN stockmaster sm ON s.stockid = sm.stockid
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and s.supplierno IN ('GK', 'KM', 'KS', 'VA')
and salesmanname =! '25-Non Profit'
and d.typeid = '3'
and discountpercent > '0.0'
GROUP BY
s.stockid,
debtorno
ORDER BY
trandate ASC,
brname ASC,
s.stockid ASC,
trandate DESC,
loccode