0

basically i have a table which has different branch names recorded in the same column as other data. I cannot change how the data comes in so i have to find a way of extracting and matching data from this column.

eg

+---------+---------------------+------+------------+--------+
| Company | Branch | Tml | Name | |
+---------+---------------------+------+------------+--------+
| 1 | 1 | | Venue Cafe | |
| 1 | 2 | | Tasty Cafe | |
| 1 | 1 | 0001 | POS1 | |
| 1 | 2 | 0001 | POS1 | |
| 1 | 1 | 0002 | POS2 | |
| 1 | 2 | 0002 | POS2 | |
+---------+---------------------+------+------------+--------+


i do have other data i need to extract as well so i will put my current code below

SELECT

	strathtrans.COMPANY As Company, 
	strathtrans.BRANCH As Branch, 
	strathtrans.LOCATION As Location, 
	strathtrans.TERMINAL As Terminal, 
	CASE WHEN config_locations_strath.TERMINAL = 002 OR config_locations_strath.TERMINAL = 001  AND config_locations_strath.BRANCH = 007 then "Venue Cafe"  ELSE "" END,
	config_locations_strath.`NAME` as `NAME`,
	strathtrans.PRODUCT, strathtrans.`DESC`, 
	SUM(strathtrans.stk_unit * strathtrans.quantity) AS qty_sales, 
	SUM( strathtrans.TOTAL ) AS amt_sales 
	From config_locations_strath Inner Join
	strathtrans On strathtrans.BRANCH = config_locations_strath.BRANCH And
	strathtrans.LOCATION = config_locations_strath.LOCATION And
	strathtrans.TERMINAL = config_locations_strath.TERMINAL
	WHERE strathtrans.TYPE = 'PAID' AND strathtrans.batch_item <= 1 AND strathtrans.trans_no > 0 
	GROUP BY COMPANY, BRANCH, LOCATION, TERMINAL, PRODUCT, `DESC` 
	ORDER BY `amt_sales` DESC

is there a way to combine case statements?

1
Contributor
1
Reply
2
Views
7 Years
Discussion Span
Last Post by cacoyle
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.