Edit Report:Luxury Brand Sales Breakdown
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Luxury Brand Sales Breakdown
-- PLACE HOLDER REPORT DESCRIPTION
-- ^Monthly Reports
-- }
-- }
--
SELECT
c.debtorno,
c.branchcode,
`name`,
sm.salesmanname as salesperson,
-- total sales
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '1'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '1'
and YEAR(trandate)= '2023'
) end as " Jan Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '1'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2024'
) end as " Jan Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) end as " Feb Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) end as " Feb Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) end as " Mar Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) end as " Mar Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) end as " Apr Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) end as " Apr Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) end as " May Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) end as " May Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) end as " Jun Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) end as " Jun Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) end as " Jul Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) end as " Jul Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) end as " Aug Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) end as " Aug Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) end as " Sep Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) end as " Sep Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) end as " Oct Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) end as " Oct Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) end as " Nov Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) end as " Nov Total Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) end as " Dec Total Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
--
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) end as " Dec Total Sales 2024",
-- OR
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) = '1'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '1'
and YEAR(trandate)= '2023'
) end as " Jan OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '1'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2024'
) end as " Jan OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) end as " Feb OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) end as " Feb OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) end as " Mar OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) end as " Mar OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) end as " Apr OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) end as " Apr OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) end as " May OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) end as " May OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) end as " Jun OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) end as " Jun OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) end as " Jul OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) end as " Jul OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) end as " Aug OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) end as " Aug OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) end as " Sep OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) end as " Sep OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) end as " Oct OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) end as " Oct OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) end as " Nov OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) end as " Nov OR Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) end as " Dec OR Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'OR'
-- and supplierno = 'OR'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) end as " Dec OR Sales 2024",
-- RC
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) IN ('1','2','3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2023'
) end as " Jan RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '1'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2024'
) end as " Jan RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) end as " Feb RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) end as " Feb RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) end as " Mar RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) end as " Mar RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) end as " Apr RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) end as " Apr RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) end as " May RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) end as " May RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) end as " Jun RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) end as " Jun RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) end as " Jul RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) end as " Jul RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) end as " Aug RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) end as " Aug RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) end as " Sep RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) end as " Sep RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) end as " Oct RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) end as " Oct RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) end as " Nov RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) end as " Nov RCO Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) end as " Dec RCO Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'RC'
-- and supplierno = 'RC'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) end as " Dec RCO Sales 2024",
-- Stephen J
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) IN ('1','2','3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2023'
) end as " Jan SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '1'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2024'
) end as " Jan SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) end as " Feb SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) end as " Feb SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) end as " Mar SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) end as " Mar SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) end as " Apr SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) end as " Apr SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) end as " May SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) end as " May SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) end as " Jun SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) end as " Jun SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) end as " Jul SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) end as " Jul SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) end as " Aug SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) end as " Aug SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) end as " Sep SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) end as " Sep SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) end as " Oct SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) end as " Oct SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) end as " Nov SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) end as " Nov SJ Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
-- and supplierno = 'SJ'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) end as " Dec SJ Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'SJ'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) end as " Dec SJ Sales 2024",
-- Stephen J
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and MONTH(trandate) IN ('1','2','3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2023'
) end as " Jan CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '1'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) IN ('1')
and YEAR(trandate)= '2024'
) end as " Jan CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2023'
) end as " Feb CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '2'
and YEAR(trandate)= '2024'
) end as " Feb CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2023'
) end as " Mar CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '3'
and YEAR(trandate)= '2024'
) end as " Mar CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2023'
) end as " Apr CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '4'
and YEAR(trandate)= '2024'
) end as " Apr CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2023'
) end as " May CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '5'
and YEAR(trandate)= '2024'
) end as " May CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2023'
) end as " Jun CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '6'
and YEAR(trandate)= '2024'
) end as " Jun CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2023'
) end as " Jul CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '7'
and YEAR(trandate)= '2024'
) end as " Jul CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2023'
) end as " Aug CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '8'
and YEAR(trandate)= '2024'
) end as " Aug CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2023'
) end as " Sep CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '9'
and YEAR(trandate)= '2024'
) end as " Sep CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2023'
) end as " Oct CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '10'
and YEAR(trandate)= '2024'
) end as " Oct CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2023'
) end as " Nov CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '11'
and YEAR(trandate)= '2024'
) end as " Nov CBB Sales 2024",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
-- and supplierno = 'CBB'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2023'
) end as " Dec CBB Sales 2023",
case when (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) is NULL then 0 else (
select
sum(discountedlinetotal * -1)
from
stocktrans
where
stocktrans.debtorno = c.debtorno
and supplierno = 'CBB'
and MONTH(trandate) = '12'
and YEAR(trandate)= '2024'
) end as " Dec CBB Sales 2024",
'' as '_'
FROM
custbranch c
join debtorsmaster d on d.debtorno = c.debtorno
join salesman sm on c.salesman = sm.salesmancode
WHERE
d.typeid IN (0,1,2,3,4,5,7,16,17,19,20)
and c.debtorno = c.branchcode
and sm.salesmanname != '26-COD'
and sm.salesmancode IN ('23', '27', '28', '29','30')
and `name` NOT LIKE '%CLOSED%'
group by c.debtorno, c.branchcode
order by
sm.salesmanname