Edit Report:New Customers
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- New Customers
-- New customers by date range with MTD and YTD sales.
-- ^customers
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of this month", end: "last day of this month" }
-- }
select
concat(dm.debtorno, '.', cb.branchcode) as `Customer #`,
name as `Name`,
address1 as `Address`,
date_format(clientsince, '%m/%y') `Client Since`,
case
when ISNULL((select sum(discountedlinetotal) *-1 from stocktrans where debtorno = dm.debtorno and date(trandate) > MAKEDATE(year(now()),1))) then 0.00
else ((select cast(sum(discountedlinetotal) as decimal(10,2))*-1 from stocktrans where debtorno = dm.debtorno and date(trandate) > MAKEDATE(year(now()),1)))
end as `YTD`,
case
when ISNULL((select sum(discountedlinetotal) *-1 from stocktrans where debtorno = dm.debtorno and date(trandate) BETWEEN (MAKEDATE(year('{{range.start}}'),1)) and LAST_DAY(date('{{range.end}}')))) then 0.00
else (select cast(sum(discountedlinetotal) as decimal(10,2))*-1 from stocktrans where debtorno = dm.debtorno and date(trandate) BETWEEN (MAKEDATE(year('{{range.start}}'),1)) and LAST_DAY(date('{{range.end}}')))
end as `MTD`,
address3 as `CITY`,
address4 as `STATE`,
address5 as `ZIP` ,
cb.phoneno as `PHONE`,
cb.salesman as `SLM`
from debtorsmaster dm
left join custbranch cb on cb.debtorno = dm.debtorno
where date(dm.clientsince) between '{{range.start}}' and '{{range.end}}'
order by cb.salesman, dm.debtorno;