Edit Report:SALON SALES 2024 - 2025 NORTHERN ALBERTA
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- SALON SALES 2024 - 2025 NORTHERN ALBERTA
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- ROLLUP: {
-- columns: {
-- "TOTAL SALES 2024": "Total: {{sum}}",
-- "TOTAL SALES 2025": "Total: {{sum}}",
-- "AMIKA 2024": "Total: {{sum}}",
-- "AMIKA 2025": "Total: {{sum}}",
-- "K18 2024": "Total: {{sum}}",
-- "K18 2025": "Total: {{sum}}",
-- "REDKEN 2024": "Total: {{sum}}",
-- "REDKEN 2025": "Total: {{sum}}",
-- "GOLDWELL 2024": "Total: {{sum}}",
-- "GOLDWELL 2025": "Total: {{sum}}",
-- "KMS 2024": "Total: {{sum}}",
-- "KMS 2025": "Total: {{sum}}",
-- "KERASILK 2024": "Total: {{sum}}",
-- "KERASILK 2025": "Total: {{sum}}",
-- "EVO 2024": "Total: {{sum}}",
-- "EVO 2025": "Total: {{sum}}",
-- "ILES 2024": "Total: {{sum}}",
-- "ILES 2025": "Total: {{sum}}",
-- "VARIS 2024": "Total: {{sum}}",
-- "VARIS 2025": "Total: {{sum}}",
-- "LIVING PROOF 2024": "Total: {{sum}}",
-- "LIVING PROOF 2025": "Total: {{sum}}",
-- "DANNYCO 2024": "Total: {{sum}}",
-- "DANNYCO 2025": "Total: {{sum}}",
-- "PONY 2024": "Total: {{sum}}",
-- "PONY 2025": "Total: {{sum}}",
-- "REVIVE 2024": "Total: {{sum}}",
-- "REVIVE 2025": "Total: {{sum}}",
-- "FRAMAR 2024": "Total: {{sum}}",
-- "FRAMAR 2025": "Total: {{sum}}",
-- "YOUNGBLOOD 2024": "Total: {{sum}}",
-- "YOUNGBLOOD 2025": "Total: {{sum}}",
-- "ARIA 2024": "Total: {{sum}}",
-- "ARIA 2025": "Total: {{sum}}"
-- }
-- }
--
SELECT
c.debtorno,
c.branchcode,
`name`,
c.braddress3 as 'city',
salesmanname,
-- total sales
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and YEAR(trandate)= '2024'
) end as " TOTAL SALES 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and YEAR(trandate)= '2025'
) end as " TOTAL SALES 2025",
-- REDKEN
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RK'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RK'
and YEAR(trandate)= '2024'
) end as " REDKEN 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RK'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RK'
and YEAR(trandate)= '2025'
) end as " REDKEN 2025",
-- GOLDWELL
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'GK'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'GK'
and YEAR(trandate)= '2024'
) end as " GOLDWELL 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'GK'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'GK'
and YEAR(trandate)= '2025'
) end as " GOLDWELL 2025",
-- KMS
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KM'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KM'
and YEAR(trandate)= '2024'
) end as " KMS 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KM'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KM'
and YEAR(trandate)= '2025'
) end as " KMS 2025",
-- KERASILK
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KS'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KS'
and YEAR(trandate)= '2024'
) end as " KERASILK 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KS'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'KS'
and YEAR(trandate)= '2025'
) end as " KERASILK 2025",
-- VARIS
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'VA'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'VA'
and YEAR(trandate)= '2024'
) end as " VARIS 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'VA'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'VA'
and YEAR(trandate)= '2025'
) end as " VARIS 2025",
-- AMIKA
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = ('AM')
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'AM'
and YEAR(trandate)= '2024'
) end as " AMIKA 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = ('AM')
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'AM'
and YEAR(trandate)= '2025'
) end as " AMIKA 2025",
-- LIVING PROOF
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'LP'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'LP'
and YEAR(trandate)= '2024'
) end as " LIVING PROOF 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'LP'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'LP'
and YEAR(trandate)= '2025'
) end as " LIVING PROOF 2025",
-- K18
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'AH'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'AH'
and YEAR(trandate)= '2024'
) end as " K18 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'AH'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'AH'
and YEAR(trandate)= '2025'
) end as " K18 2025",
-- EVO
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'EV'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'EV'
and YEAR(trandate)= '2024'
) end as " EVO 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'EV'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'EV'
and YEAR(trandate)= '2025'
) end as " EVO 2025",
-- Maria Nila
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'MN'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'MN'
and YEAR(trandate)= '2024'
) end as " MARIA NILA 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'MN'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'MN'
and YEAR(trandate)= '2025'
) end as " MARIA NILA 2025",
-- CALECIM
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'CAL'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'CAL'
and YEAR(trandate)= '2024'
) end as " CALECIM 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'CAL'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'CAL'
and YEAR(trandate)= '2025'
) end as " CALECIM 2025",
-- ILES
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'IF'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'IF'
and YEAR(trandate)= '2024'
) end as " ILES 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'IF'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'IF'
and YEAR(trandate)= '2025'
) end as " ILES 2025",
-- DANNYCO
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'DC'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'DC'
and YEAR(trandate)= '2024'
) end as " DANNYCO 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'DC'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'DC'
and YEAR(trandate)= '2025'
) end as " DANNYCO 2025",
-- PONY
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'PH'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'PH'
and YEAR(trandate)= '2024'
) end as " PONY 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'PH'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'PH'
and YEAR(trandate)= '2025'
) end as " PONY 2025",
-- REVIVE
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'R3'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'R3'
and YEAR(trandate)= '2024'
) end as " REVIVE 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'R3'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'R3'
and YEAR(trandate)= '2025'
) end as " REVIVE 2025",
-- FRAMAR
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'FR'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'FR'
and YEAR(trandate)= '2024'
) end as "FRAMAR 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'FR'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'FR'
and YEAR(trandate)= '2025'
) end as " FRAMAR 2025",
-- YOUNGBLOOD
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'YB'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'YB'
and YEAR(trandate)= '2024'
) end as "YOUNGBLOOD 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'YB'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'YB'
and YEAR(trandate)= '2025'
) end as " YOUNGBLOOD 2025",
-- ARIA
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RX'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RX'
and YEAR(trandate)= '2024'
) end as " ARIA 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RX'
and YEAR(trandate)= '2025'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and stocktrans.salesperson = c.salesman
and supplierno = 'RX'
and YEAR(trandate)= '2025'
) end as " ARIA 2025",
'' as '_'
FROM
custbranch c
join debtorsmaster d on d.debtorno = c.debtorno
join salesman sm on c.salesman = sm.salesmancode
WHERE
c.salesman IN ('05','06','07','20','32','91')
and c.debtorno = c.branchcode
and `name` NOT LIKE '%CLOSED%'
group by c.debtorno, c.branchcode
order by
sm.salesmanname