Edit Report:Pony Weft Sales and Total Sales
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Pony Weft Sales and Total Sales
-- Select Date Range
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "Pony Weft Qty": "Total: {{sum}}",
-- "Store Pony Weft Sales": "Total: {{sum}}",
-- "Total Pony Weft Sales": "Total: {{sum}}",
-- "Total Pony Sales": "Total: {{sum}}",
-- }
-- }
--
SELECT
debtorno,
branchcode,
brname,
salesmanname,
CAST(
SUM( CASE WHEN loccode >= '00' AND supplierno = 'PH' AND lineid = 'WFT' THEN ( qty * - 1 ) ELSE 0 END ) AS INTEGER ) AS 'Pony Weft Qty',
CAST(
SUM( CASE WHEN loccode > '00' AND supplierno = 'PH' AND lineid = 'WFT' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Store Pony Weft Sales',
CAST(
SUM( CASE WHEN loccode >= '00' AND supplierno = 'PH' AND lineid = 'WFT' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Total Pony Weft Sales',
CAST(
SUM( CASE WHEN loccode >= '00' AND supplierno = 'PH' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Total Pony Sales'
FROM
stocktrans
WHERE
supplierno = 'PH'
and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and loccode IN (select loccode from locations)
group by brname
order by
CAST(
SUM( CASE WHEN loccode >= '00' AND supplierno = 'PH' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) DESC