Edit Report:TESTING e-boutique customers sales
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- TESTING e-boutique customers sales
-- customers without sales reps
--
--
--
SELECT
s.debtorno AS DEBTORNO,
s.branchcode AS BRANCH,
c.brname AS SALON,
c.braddress3 AS CITY,
c.braddress4 AS PROV,
c.phoneno AS PHONENO,
s.salesmanname AS REP,
(select count(DISTINCT ( transno ))
from stocktrans
where year(trandate) = '2021'
and stocktrans.debtorno = s.debtorno
and stocktrans.branchcode = s.branchcode
and stocktrans.placedfrom = 4
and stocktrans.salesperson IN ('00', '99', '22')
) as '2021 ECOM ORDERS',
(select count(DISTINCT ( transno ))
from stocktrans
where year(trandate) = '2022'
and stocktrans.debtorno = s.debtorno
and stocktrans.branchcode = s.branchcode
and stocktrans.placedfrom = 4
and stocktrans.salesperson IN ('00', '99', '22')
) as '2022 ECOM ORDERS',
CAST(sum(
case when YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2021 ECOM SALES',
CAST(sum(
case when YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2022 ECOM SALES',
CAST(sum(
case when categoryid = 'IN' and YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2021 INTROS',
CAST(sum(
case when categoryid ='IN' and YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2022 INTROS',
CAST(sum(
case
when supplierno ='RK' and YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2)) as '2021 REDKEN',
CAST(sum(
case
when supplierno ='RK' and YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2)) as '2022 REDKEN',
CAST(sum(
case
when supplierno ='GK' and YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2)) as '2021 GOLDWELL',
CAST(sum(
case
when supplierno ='GK' and YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2)) as '2022 GOLDWELL',
CAST(sum(
case when supplierno ='OX' and YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2021 OLAPLEX',
CAST(sum(
case when supplierno ='OX' and YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2022 OLAPLEX',
CAST(sum(
case when supplierno ='EV' and YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2021 EVO',
CAST(sum(
case when supplierno ='EV' and YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2022 EVO',
CAST(sum(
case when supplierno ='AM' and YEAR(trandate) = '2021'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2021 AMIKA',
CAST(sum(
case when supplierno ='AM' and YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2))
as '2022 AMIKA'
FROM
stocktrans s
INNER JOIN custbranch c ON c.debtorno = s.debtorno
AND c.branchcode = s.branchcode
WHERE
s.placedfrom = 4
and s.salesperson IN ('00', '99', '22')
GROUP BY
s.debtorno
ORDER BY
CAST(sum(
case when YEAR(trandate) = '2022'
then (discountedlinetotal * -1)
else 0
end
) AS DECIMAL(10,2)) DESC