Edit Report:mike's report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- mike's report
-- Select date range.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
SELECT
s.salesperson,
s.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 s
inner join debtorsmaster d on s.debtorno = d.debtorno
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','17','19','20','21','22','24','31','32','33','34','35','36','88','91','99' )
and d.typeid NOT IN ('6', '8','10','21')
group by
salesperson
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 s
inner join debtorsmaster d on s.debtorno = d.debtorno
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','17','19','20','21','22','24','31','32','33','34','35','36','88','91','99' )
and d.typeid NOT IN ('6', '8','10','21')
union
select
'Salesperson',
'Sales Rep',
'Warehouse Total',
'Store Total',
'Total Sales',
'OR WH Sales',
'OR Store Sales',
'OR Sales Total',
'BOB WH Sales',
'BOB Store Sales',
'BOB Sales Total'
union
SELECT
s.salesperson,
s.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 = 'CBB' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) 'BOB WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'CBB'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'BOB Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'CBB' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'BOB Sales Total'
FROM
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND s.salesperson IN ('18','23','27','28','29','30','38')
and d.typeid NOT IN ('6', '8','10','21')
group by
salesperson
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 = 'CBB' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'BOB WH Sales',
CAST(
SUM(
CASE WHEN loccode <> '00'
AND supplierno = 'CBB'
AND salesperson IN (
SELECT
salesmancode
FROM
salesman
WHERE
commissionrate1 = 1
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'BOB Store Sales',
CAST(
SUM(
CASE WHEN loccode >= '00'
AND supplierno = 'CBB' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'BOB Sales Total'
FROM
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND s.salesperson IN ('18','23','27','28','29','30','38')
and d.typeid NOT IN ('6', '8','10','21')
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 = s.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 s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and loccode IN ('01', '02', '03', '04', '05', '06', '07',
'08', '09','10', '11')
and d.typeid NOT IN ('6', '8','10','21')
group by
loccode
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 s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and loccode IN ('01', '02', '03', '04', '05', '06', '07',
'08', '09','10')
and d.typeid NOT IN ('6', '8','10','21')
union
select
'Redken Total Sales',
(
select
cast(
sum(discountedlinetotal * -1) as decimal(10, 2)
)
from
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
where
s.supplierno = 'RK'
and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and d.typeid NOT IN ('6', '8','10','21')
) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select
'Goldwell Total Sales',
(
select
cast(
sum(discountedlinetotal * -1) as decimal(10, 2)
)
from
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
where
supplierno IN ('GK', 'KM', 'VA', 'KS')
and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and d.typeid NOT IN ('6', '8','10','21')
) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select
'Luxury Brand Total Sales',
(
select
cast(
sum(discountedlinetotal * -1) as decimal(10, 2)
)
from
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
where
salesperson IN ('18','23','27','28','29','30','38')
and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and d.typeid NOT IN ('6', '8','10','21')
) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select
'DSC Sales Totals',
(
select
cast(
sum(discountedlinetotal * -1) as decimal(10, 2)
)
from
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
where
s.salesperson NOT IN ('18','23','27','28','29','30','38')
and trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and d.typeid NOT IN ('6', '8','10','21')
and salesperson in (select salesmancode from salesman where commissionrate1 =1 )
) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''
union
select
'Summit Totals',
(
select
cast(
sum(discountedlinetotal * -1) as decimal(10, 2)
)
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')
) as TOTAL,
'',
'',
'',
'',
'',
'',
'',
'',
''