SQL Left Outer Join Modification

Dev Shed Forums Sponsor:

October 8th, 2012, 02:13 PM

dabgdk

Registered User

Join Date: Oct 2012

Posts: 2

Time spent in forums: 8 m 3 sec
Reputation 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.

October 9th, 2012, 04:29 PM

dabgdk

Registered User

Join Date: Oct 2012

Posts: 2

Time spent in forums: 8 m 3 sec
Reputation 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.

SQL Left Outer Join Modification

Developer Shed Advertisers and Affiliates