Edit Report:Loyalty Customer Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Loyalty Customer Report
-- Loyalty Customer Report
--
SELECT
d.name as BILLTO,
d.debtorno as CUSTOMERNO,
l.branchcode as BRANCH,
t.typename as CUSTTYPE,
l.loyaltyid as LOYALTYACCOUNT,
l.datefirstpurchase as DATEFIRSTPURCH,
l.datelastpurchase as DATELASTPURCH,
l.purchasecount as VISITS,
l.maximumpoints as TOTPOINTSEARNED,
CAST(l.maximumpoints - (sum(loyaltytrans.spentpoints)) as DECIMAL(10,2) ) as POINTBALANCE
FROM
debtorsmaster d
INNER JOIN debtortype t on t.typeid = d.typeid
INNER JOIN loyaltyaccount l ON d.debtorno = l.debtorid
INNER JOIN loyaltytrans ON l.loyaltyid = loyaltytrans.loyaltyid
WHERE l.loyaltyid <> ''
group by d.debtorno
order by t.typename asc, d.debtorno asc