trouble with query, multiple conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trouble with query, multiple conditions

Author

Topic

bandguy001
Starting Member

12 Posts

Posted - 2010-04-15 : 12:16:08

Hi All,

I’m still new to sql and this has been driving me crazy. Below is a sample data table (POSData). Everything is pretty self explanatory except the value_char column. If an item is voided it will have a code of VX and a corresponding negative amount (ex below, BEER at -18.99). Also, if an item is manually entered it will have a value_char of HK (hand keyed). Notice the peanuts with an HK code though. It wasn’t a “hand keyed” item, but the PLU was manually entered so it still receives a code of HK. The peanuts are also voided, but since they were originally hand keyed, they now received a code of VX HK

I’m trying to write a query that will find a transaction with the following conditions

Find a transaction with POSData.item LIKE ‘%’ + ‘KEYED’ +’%’
And the transaction has to have a count (value_char = ‘VX’) >0

So basically I want to find a transaction with at least 1 void, a keyed item, and milk

I came up with this (searches for anything like milk or keyed or VX)
Select POSData.receipt_no, POSData.register_time, POSData.cashier,
POSData.value_char, POSData.item
From POSData
Where (POSData.item Like '%' + 'MILK' + '%') Or
(POSData.item Like '%' + 'KEYED' + '%') Or
(POSData.value_char = 'VX')
Group By POSData.receipt_no, POSData.register_time, POSData.cashier,
POSData.value_char, POSData.item
Having Count(Distinct POSData.value_char = ‘VX’) >= 1 And
Count(Distinct POSData.item) >= 2
Order By POSData.register_time

I know the syntax is wrong but it’s driving me crazy. Any help would be appreciated!!!

IDX receipt_no cashier register_time item value_char value_num
1 001 12345 4/1/2010 12:00pm MILK 1.99
2 001 12345 4/1/2010 12:00pm BEER 18.99
3 001 12345 4/1/2010 12:00pm BEER VX (18.99)
4 001 12345 4/1/2010 12:00pm KEYED ITEM HK 1.00
5 001 12345 4/1/2010 12:00pm PEANUTS HK 1.00
6 001 12345 4/1/2010 12:00pm PEANUTS VX HK (1.00)
5 001 12345 4/1/2010 12:00pm TAX .1
6 001 12345 4/1/2010 12:00pm BALANCE 3.09
7 001 12345 4/1/2010 12:00pm CASH CASH 3.09
8 001 12345 4/1/2010 12:00pm CHANGE CHANGE 0

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-04-15 : 12:47:27

First lets present the data in a cogent manner:


DECLARE @t TABLE
(
	IDX int NOT NULL
	,receipt_no char(3) NOT NULl
	,cashier int NOT NULL
	,register_time datetime NOT NULL
	,item varchar(20) NOT NULL
	,value_char varchar(10) NULL
	,value_num money NOT NULL
)
INSERT INTO @t
SELECT 1, '001', 12345, '20100401 12:00', 'MILK', NULL, 1.99
UNION ALL SELECT 2, '001', 12345, '20100401 12:00', 'BEER', NULL, 18.99
UNION ALL SELECT 3, '001', 12345, '20100401 12:00', 'BEER', 'VX', -18.99
UNION ALL SELECT 4, '001', 12345, '20100401 12:00', 'KEYED ITEM', 'HK', 1.00
UNION ALL SELECT 5, '001', 12345, '20100401 12:00', 'PEANUTS', 'HK', 1.00
UNION ALL SELECT 6, '001', 12345, '20100401 12:00', 'PEANUTS', 'VX HK', (1.00)
UNION ALL SELECT 5, '001', 12345, '20100401 12:00', 'TAX', NULL, .1
UNION ALL SELECT 6, '001', 12345, '20100401 12:00', 'BALANCE', NULL, 3.09
UNION ALL SELECT 7, '001', 12345, '20100401 12:00', 'CASH', 'CASH', 3.09
UNION ALL SELECT 8, '001', 12345, '20100401 12:00', 'CHANGE', 'CHANGE', 0

With the above data, this produces no results:


SELECT receipt_no, register_time, cashier, value_char, item
FROM @t
WHERE item LIKE '%' + 'MILK' + '%'
	OR item LIKE '%' + 'KEYED' + '%'
	OR value_char = 'VX'
GROUP BY receipt_no, register_time, cashier, value_char, item
HAVING COUNT(CASE WHEN value_char LIKE 'VX%' THEN 1 END) >= 1
	AND COUNT(DISTINCT item) >= 2
ORDER BY register_time

If you want any more help I think you are going to have to provide more data, in the above format, AND the expected resutls.
RIRO

bandguy001
Starting Member

12 Posts

Posted - 2010-04-15 : 13:22:44

Thanks for the help but i'm still a bit puzzled. Based on your query it should return any transactions with MILK and KEYED and a VX count >=1

The data provided does contain all of this so how come there isn't a result from the query?

The data provided has 1 milk, 1 keyed, and a vx count of 2

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-04-16 : 06:58:42

The problem is you have neither explained, nor does your data show, what constitutes a transaction.
If I guess that a transaction is a combination of receipt_no, cashier and register_time then the following will produce all the sample data. As you have not given expected results I have no way of knowing if this is what you want.


SELECT T.receipt_no, T.register_time, T.cashier, T.value_char, T.item
FROM @t T
	JOIN
	(
		SELECT T1.receipt_no, T1.cashier, T1.register_time
		FROM @t T1
		WHERE T1.item LIKE '%' + 'MILK' + '%'
			OR T1.item LIKE '%' + 'KEYED' + '%'
			OR T1.value_char = 'VX'
		GROUP BY T1.receipt_no, T1.cashier, T1.register_time
		HAVING COUNT(CASE WHEN T1.value_char LIKE 'VX%' THEN 1 END) >= 1
			AND COUNT(DISTINCT T1.item) >= 2
	) D
		ON T.receipt_no = D.receipt_no
			AND T.cashier = D.cashier
			AND T.register_time = D.register_time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources