Edit Report:Salesreps + Stores Monthly_1
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Salesreps + Stores Monthly_1
-- Select Date Range.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "DSC Warehouse Total": "Total: {{sum}}",
-- "DSC Store Total": "Total: {{sum}}",
-- "DSC Total Sales": "Total: {{sum}}",
-- "DSC Redken Sales Warehouse Only": "Total: {{sum}}",
-- "DSC Redken Sales Stores Only": "Total: {{sum}}",
-- "DSC Redken Sales Total": "Total: {{sum}}",
-- }
-- }
-- FILTER: {
-- filter: "twig",
-- column: "DSC Warehouse Total",
-- params: {
-- template: '{{ value | number_format(2, '.', ',') }}'
-- }
-- }
--
# ---- general salesreps ----
SELECT
salesperson,
salesmanname as 'Sales Rep',
CAST(
SUM(
CASE WHEN loccode = '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Warehouse Total',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Total',
CAST(
SUM(
CASE WHEN loccode >= '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Total Sales',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno = 'RK' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'RK'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'RK' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Redken Sales Total',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno IN ('GK', 'KM', 'VA','KS') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno IN ('GK', 'KM', 'VA','KS') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Sales Total'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND salesperson IN (
'00', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12',
'13', '14', '15', '16', '18', '19',
'20', '21', '22', '24', '25',
'26', '31', '32',
'33', '34', '35', '36', '37', '91', '99','88'
)
group by
salesperson
# ---- general salesreps TOTALS ----
union
SELECT
'TOTAL',
'',
CAST(
SUM(
CASE WHEN loccode = '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Warehouse Total',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Total',
CAST(
SUM(
CASE WHEN loccode >= '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Total Sales',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno = 'RK' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'RK'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'RK' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Redken Sales Total',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno IN ('GK', 'KM', 'VA','KS') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno IN ('GK', 'KM', 'VA', 'KS')
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno IN ('GK', 'KM', 'VA','KS') THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Sales Total'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND salesperson IN (
'00', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12',
'13', '14', '15', '16', '18', '19',
'20', '21', '22', '24', '25',
'26', '31', '32',
'33', '34', '35', '36', '37', '91', '99','88'
)
# ---- lux brand sales----
union
select
'Salesperson',
'Sales Rep',
'Warehouse Total',
'Store Total',
'Total Sales',
'OR WH Sales',
'OR Store Sales',
'OR Sales Total',
'RC WH Sales',
'RC Store Sales',
'RC Sales Total'
# ---- lux brands sales----
union
SELECT
salesperson,
salesmanname as 'Sales Rep',
CAST(
SUM(
CASE WHEN loccode = '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Warehouse Total',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Total',
CAST(
SUM(
CASE WHEN loccode >= '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Total Sales',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno = 'OR'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'OR WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'OR'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'OR Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'OR'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'OR Sales Total',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno = 'RC'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) 'RC WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'RC'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RC Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'RC'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RC Sales Total'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND salesperson IN (
'23','27','28','29','30','38'
)
group by
salesperson
# ---- lux brand sales TOTALS----
UNION
SELECT
'TOTAL',
'',
CAST(
SUM(
CASE WHEN loccode = '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Warehouse Total',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Total',
CAST(
SUM(
CASE WHEN loccode >= '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Total Sales',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno = 'OR'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'OR WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'OR'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'OR Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'OR'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'OR Sales Total',
CAST(
SUM(
CASE WHEN loccode = '00'
AND supplierno = 'RC'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RC WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'RC'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RC Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'RC'
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RC Sales Total'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND salesperson IN (
'23','27','28','29','30','38'
)
# ---- stores sales----
union
select
'Location No',
'Location Name',
'Store Sales',
'DSC Store Sales',
'Store Total Sales',
'RK Store Sales',
'RK Store DSC Sales',
'RK Store Total Sales',
'GK Store Sales',
'GK Store DSC Sales',
'GK Store Total Sales'
union
SELECT
loccode as 'Location No',
(select TRIM(REPLACE( RIGHT(locationname, LENGTH(locationname) - 3 ), 'SUMMIT', '')) from locations where locations.loccode = stocktrans.loccode) as 'Location Name',
CAST(
SUM(
CASE WHEN loccode > '00'
AND salesperson NOT IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'DSC Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Total Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno = 'RK'
AND salesperson NOT IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno = 'RK'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store DSC Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno = 'RK' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store Total Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
AND salesperson NOT IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store DSC Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store Total Sales'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and loccode IN (
'01', '02', '03', '04', '05', '06', '07',
'08'
)
group by
loccode
# ---- stores sales TOTALS----
union
SELECT
'TOTAL',
'',
CAST(
SUM(
CASE WHEN loccode > '00'
AND salesperson NOT IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'DSC Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'Store Total Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno = 'RK'
AND salesperson NOT IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno = 'RK'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store DSC Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno = 'RK' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'RK Store Total Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
AND salesperson NOT IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store DSC Sales',
CAST(
SUM(
CASE WHEN loccode > '00'
AND supplierno IN ('GK', 'KM', 'VA','KS')
THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'GK Store Total Sales'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and loccode IN (
'01', '02', '03', '04', '05', '06', '07',
'08'
)
# ---- summit totals----
union
select 'Redken Total Sales',
(select cast(sum(discountedlinetotal * -1 ) as decimal(10,2)) from stocktrans where supplierno = 'RK' and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}' ) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select 'Goldwell Total Sales',
(select cast(sum(discountedlinetotal * -1 ) as decimal(10,2)) from stocktrans where supplierno IN ('GK', 'KM', 'VA','KS')
and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}' ) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select 'Luxury Brand Total Sales',
(select cast(sum(discountedlinetotal * -1 ) as decimal(10,2)) from stocktrans where salesperson IN (
'23','27','28','29','30','38'
) and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}') as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select 'DSC Sales Totals',
(select cast(sum(discountedlinetotal * -1 ) as decimal(10,2)) from stocktrans where salesperson NOT IN (
'23','27','28','29','30','38'
) and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}' ) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select 'Summit Totals',
(select cast(sum(discountedlinetotal * -1 ) as decimal(10,2)) from stocktrans WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}' ) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''