Edit Report:Summit Projection Totals - 00 only
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Summit Projection Totals - 00 only
-- Projection Sales for reps. Select Date Range. Sort by Heading.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "Southern Alberta Total Sales": "Total: {{sum}}",
-- "Northern Alberta Total Sales": "Total: {{sum}}",
-- "Saskatchewan Total Sales": "Total: {{sum}}",
-- "Manitoba Total Sales": "Total: {{sum}}",
-- "Luxury Brand Total Sales": "Total: {{sum}}",
-- "Other Summit Total Sales": "Total: {{sum}}",
-- }
-- }
--
SELECT
salesperson,
salesmanname as 'Sales Rep',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND salesperson IN (
'01', '02', '08', '12', '13', '14', '24', '31', '17'
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Southern Alberta Total Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND salesperson IN ('05', '06', '07', '20', '32', '91') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Northern Alberta Total Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND salesperson IN ('15', '16', '19', '21', '35') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Saskatchewan Total Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND salesperson IN (
'09', '10', '11', '33', '34', '36', '88'
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Manitoba Total Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND salesperson IN ('18', '23', '27', '28', '29', '38') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Luxury Brand Total Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND salesperson IN (
'00', '03', '04', '22', '25', '26', '99'
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS ' Other Summit Total Sales'
FROM
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and d.typeid NOT IN ('6', '8','10','21')
AND salesperson IN (
'00', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12',
'13', '14', '15', '16', '18', '17', '19',
'20', '21', '22', '23', '24', '27',
'28', '29', '31', '32', '33', '34',
'35', '36', '37', '38', '91', '99'
)
group by
salesperson
order by
salesperson ASC