411 REFERENCE · Wikibooks
Using POSper/Developer guide/Stable/SQL Queries
Reference material by Wikibooks contributors. This retains its source identity and is not 411 news reporting.
Postgres
Total Sales Per Date:
SELECT date(tickets.datenew),
SUM(productsout.price*productsout.units) AS total
FROM tickets LEFT OUTER JOIN productsout ON tickets.ticketid=productsout.ticketid
GROUP BY date
ORDER BY date
Totals per product, per date, within a date range:
SELECT date(datenew) AS theDate, name, SUM(units * price) AS price
FROM productsout
LEFT OUTER JOIN products ON productsout.product=products.reference
LEFT OUTER JOIN tickets on productsout.ticketid=tickets.ticketid
WHERE datenew BETWEEN
'2006/2/16' AND
'2006/2/19:23:59'
GROUP BY date(datenew),name
ORDER BY date(datenew),name
Reset Database without removing products
?
MySQL:
Reset database without removing products
TRUNCATE `posper_ticketline`;
TRUNCATE `posper_payment`;
TRUNCATE `posper_ticket`;
TRUNCATE `posper_ticketcreationgenerator`;
ALTER TABLE `posper_ticketcreationgenerator` AUTO_INCREMENT=1;
TRUNCATE `posper_ticketidgenerator`;
ALTER TABLE `posper_ticketidgenerator` AUTO_INCREMENT=1;
TRUNCATE `posper_cashregister`;