#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    SQL Left Outer Join Modification


    Hello,

    I have the following code:

    Code:
    report.setSentence(
    	"SELECT  " +
    		"CLOSEDCASH.HOST, " +
    		"CLOSEDCASH.MONEY, " +
    		"CLOSEDCASH.DATEEND, " +
    		"TAXES.NAME AS TAXNAME, CATEGORIES.NAME, " +
    		"SUM(TICKETLINES.UNITS) AS QTY, " +
    		"SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS CATPRICE, " +
    		"SUM((TICKETLINES.PRICE * TAXES.RATE ) * TICKETLINES.UNITS) AS CATTAX, "+
    		"SUM((TICKETLINES.PRICE + TICKETLINES.PRICE * TAXES.RATE ) * TICKETLINES.UNITS) " +
    		"AS CATTOTAL " +
    		"FROM CLOSEDCASH, TAXES, TICKETLINES " +
    		"LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID " +
    		"LEFT OUTER JOIN PRODUCTS_CAT ON PRODUCTS_CAT.PRODUCT = PRODUCTS.ID " +
    		"LEFT OUTER JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID " +
    		"LEFT OUTER JOIN TICKETS ON TICKETS.ID = TICKETLINES.TICKET " +
    		"LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID = TICKETS.ID " +
    		"WHERE CLOSEDCASH.MONEY = RECEIPTS.MONEY " +
    		"AND TICKETLINES.TAXID = TAXES.ID " +
    		"AND ?(QBF_FILTER) " +
    		"GROUP BY TAXES.NAME, CATEGORIES.NAME, CLOSEDCASH.HOST, CLOSEDCASH.MONEY, CLOSEDCASH.DATEEND " +
    		"ORDER BY TAXES.NAME, CATEGORIES.NAME");
    This query is used to make a report that displays sales for the day. Unfortunately, it does not take into account the location of the sales. So, if more than one store were to make sales, this report shows all sales lumped together.

    I want to add something like this:

    Code:
    AND STOCKCURRENT.LOCATION = 'b90a790c-311f-4b82-84a1-1e9ff680cefb'
    to the "WHERE" clause so that only transactions at a particular store are counted, but I know it is not that simple.

    I am attempting to put a POS system in for a retail business. I used Openbravo POS (the Java version).

    The above snippet of code comes from file taxcatsales.bs which I found at http://wiki.openbravo.com/wiki/POS_-_Report_By_Tax_And_Category

    Any help or advice would be helpful- even if it is just to tell me what SQL related material I need to look at to understand this in greater detail.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    Figured this out:

    Code:
    	"SELECT  " +
    		"CLOSEDCASH.HOST, " +
    		"CLOSEDCASH.MONEY, " +
    		"CLOSEDCASH.DATEEND, " +
    		"TAXES.NAME AS TAXNAME, CATEGORIES.NAME, " +
    		"SUM(TICKETLINES.UNITS) AS QTY, " +
    		"SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS CATPRICE, " +
    		"SUM((TICKETLINES.PRICE * TAXES.RATE ) * TICKETLINES.UNITS) AS CATTAX, "+
    		"SUM((TICKETLINES.PRICE + TICKETLINES.PRICE * TAXES.RATE ) * TICKETLINES.UNITS) " +
    		"AS CATTOTAL " +
    		"FROM CLOSEDCASH, TAXES, TICKETLINES " +
    		"LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID " +
    		"LEFT OUTER JOIN STOCKCURRENT ON PRODUCTS.ID = STOCKCURRENT.PRODUCT " +
    		//"LEFT OUTER JOIN PRODUCTS_CAT ON PRODUCTS_CAT.PRODUCT = PRODUCTS.ID " +
    		"LEFT OUTER JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID " +
    		"LEFT OUTER JOIN TICKETS ON TICKETS.ID = TICKETLINES.TICKET " +
    		"LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID = TICKETS.ID " +
    		"WHERE CLOSEDCASH.MONEY = RECEIPTS.MONEY " +
    		"AND STOCKCURRENT.LOCATION = 'cdc99024-91a5-49d7-9159-24f9076a148d' " +
    		"AND CLOSEDCASH.DATEEND IS NOT NULL " +
    		"AND TICKETLINES.TAXID = TAXES.ID " +
    		"AND ?(QBF_FILTER) " +
    		"GROUP BY TAXES.NAME, CATEGORIES.NAME, CLOSEDCASH.HOST, CLOSEDCASH.MONEY, CLOSEDCASH.DATEEND " +
    		"ORDER BY TAXES.NAME, CATEGORIES.NAME");
    Note the all important lines

    Code:
    "LEFT OUTER JOIN STOCKCURRENT ON PRODUCTS.ID = STOCKCURRENT.PRODUCT " +
    and

    Code:
    "AND STOCKCURRENT.LOCATION = 'cdc99024-91a5-49d7-9159-24f9076a148d' " +
    The line

    Code:
    "AND CLOSEDCASH.DATEEND IS NOT NULL " +
    was an attempt to clean up the report a little.

    Unfortunately, the above code does not take discounts into account because discounts are not in inventory; so all discounts which previously showed up as being in the "Unknown" category now are simply missing from the report. Still trying to figure that one out.

IMN logo majestic logo threadwatch logo seochat tools logo