Example MySQL report query. Top 10,000 sales over 365 days example. Performed in MySQL Workbench or other tool. Report may take awhile to pull. On older AMD dual-core it took between 1 to 2 minutes to look over ~2,900 products on ~35,000 ticketline rows, when reporting on every product (%%).
When reporting the '%card%' example as below. It only took 10.343 seconds to report the 23 products found with 'card' in its name.
#---- START PERIOD options ----
SET @NumberOfDays = 365; #Edit with number of days you wish to report.
#SET @StartDate = '2010-12-31 00:00:00'; #Edit with your Start date and time (yyyy-mm-dd hr:min:sec).
#SET @EndDate = '2015-12-31 00:00:00'; #Edit with your End date and time (yyyy-mm-dd hr:min:sec).
#---- END PERIOD options ----
#
#---- START CUSTOM options ----
SET @ProductName = '%card%'; #Edit with part (or whole) of product name. Case insensitive.
#SET @Supplier = '%<entrykey="Supplier">%Cola%</entry>%'; #Edit with supplier. Place Supplier in Properties field >>
#SET @Type = '%<entrykey="Type">%Shirt%</entry>%'; #Edit with type. Place Type in Properties field >>
/* Start Properties (Attribute) field example >>
<properties><entrykey="Supplier">Coca-Cola</entry><entrykey="Type">Adult T-Shirt</entry></properties>
*/ #<< EndProperties(Attribute)fieldexample#----ENDCUSTOMoptions----SELECTPRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL,STOCKCURRENT.UNITSAS'HAVE',SUM(TICKETLINES.UNITS)AS'SOLD',SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)AS'SUBTOTAL',SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE)AS'TAXES',SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)+SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE)AS'GROSSTOTAL',SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)-SUM(PRODUCTS.PRICEBUY*TICKETLINES.UNITS)AS'PROFIT'FROMTICKETLINESLEFTOUTERJOINPRODUCTSONTICKETLINES.PRODUCT =PRODUCTS.IDLEFTOUTERJOINTICKETSONTICKETS.ID =TICKETLINES.TICKETLEFTOUTERJOINRECEIPTSONRECEIPTS.ID =TICKETS.IDLEFTOUTERJOINSTOCKCURRENTONPRODUCTS.ID =STOCKCURRENT.PRODUCT,TAXESWHERERECEIPTS.ID =TICKETS.IDANDTICKETS.ID =TICKETLINES.TICKETANDTICKETLINES.PRODUCT =PRODUCTS.IDANDTICKETLINES.TAXID =TAXES.ID#---STARTPERIODoptionimplementation.Useeither"@NumberOfDays"or"@StartDateAND@EndDate"----ANDRECEIPTS.DATENEWBETWEENDATE_SUB(NOW(),INTERVAL@NumberOfDaysDAY)ANDNOW()#ANDRECEIPTS.DATENEWBETWEEN@StartDateAND@EndDate#---ENDPERIODoptionimplementation----#---STARTCUSTOMoptionimplementation.Useanysingleormultipleoptions----ANDPRODUCTS.NAMELIKE@ProductName#ANDCONVERT(PRODUCTS.ATTRIBUTESUSINGlatin1)LIKE@Supplier#ANDCONVERT(PRODUCTS.ATTRIBUTESUSINGlatin1)LIKE@Type#---ENDCUSTOMoptionimplementation.GROUPBYPRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELLORDERBYGROSSTOTALDESCLIMIT10000#Editthislimittoreturnasmanyproductsasyouwish.Top10,000Sales?
Example MySQL report query. Top 10,000 sales over 365 days example. Performed in MySQL Workbench or other tool. Report may take awhile to pull. On older AMD dual-core it took between 1 to 2 minutes to look over ~2,900 products on ~35,000 ticketline rows, when reporting on every product (%%).
When reporting the '%card%' example as below. It only took 10.343 seconds to report the 23 products found with 'card' in its name.
result example:
Last edit: Andrew 2016-07-15