Multple Select statements then compare

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

SQL Server 2005 Forums

Transact-SQL (2005)

Multple Select statements then compare

Author

Topic

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 19:25:42

Hi,

I'm pretty new to SQL and I'm trying to accomplish a multi-layer (nested) search.

The data input I have looks like this:

Idx receipt_no register_time item value_num reg_num qty cashier
---- ----------- ------------- ---------- --------- ------- --- -------
001 0001 11/12/2009 8:00am MARBLES 2.00 5 1 12345
002 0001 11/12/2009 8:00am CANDY 1.00 5 1 12345
003 0001 11/12/2009 8:00am MILK 2% 2.50 5 1 12345
004 0001 11/12/2009 8:00am BALANCE 5.50 5 1 12345
005 0001 11/12/2009 8:00am CASH 5.50 5 1 12345
006 0001 11/12/2009 8:00am CHANGE 0.00 5 1 12345

I would like to able to find out if someone bought Marbles AND Milk with a balance of less than $10.

The only thing I could think of was to query each variable seperately into two different temporary tables, then compare them at the end. I didn't figure out how to get the Balance < $10 part since they are different columns in the same row.

I came up with the following SQL query but it obviously doesn't run ( used the @var for variables such as the candy, milk, and balance)

SELECT *
INTO #temp_pos_search1
FROM posdata
WHERE (register_num = @regnumber) AND (register_time BETWEEN @start_time AND @end_time) AND (receipt_no = @receipt_num_in) AND
(item LIKE '%' + @item_desc + '%')

SELECT *
INTO #temp_pos_search2
FROM posdata
WHERE (register_num = @regnumber) AND (register_time BETWEEN @start_time AND @end_time) AND (receipt_no = @receipt_num_in) AND
(item LIKE '%' + @item_desc2 + '%')

SELECT idx, receipt_no, register_time, server_time, item, value_char, value_num, sequence_num, register_num, dvr_id, terminal_num, void, nosale, quantity, cashier
FROM #temp_pos_search1
INNER JOIN #temp_pos_search2
ON (receipt_no.#temp_pos_search1=receipt_no.#temp_pos_search2) AND (register_time.#temp_pos_search1=register_time.#temp_pos_search2) AND (register_num.#temp_pos_search1=register_num.#temp_pos_search2)

Any help would be appreciated. I made a single variable search work like a charm, just no luck with multiple variables.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 19:36:14

this will give you the receipt_no that bought both of the items.


select receipt_no
from   posdata
where  register_num = @regnumber
AND    register_time BETWEEN @start_time AND @end_time
AND    (
           item LIKE '%' + @item_desc  + '%'
       or  item LIKE '%' + @item_desc2 + '%'
       )
group by receipt_no
having count(distinct item) = 2

KH
[spoiler]Time is always against us[/spoiler]

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 19:40:46

Thanks!!

I noticed that you used the OR for item1 and item2

Will it return the receipt # if only one of the two items is present?

Thanks!

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 19:42:33

I guess what I mean to say is

Receipt # 0001 could show up 15 times a day at 15 different registers. Any way to display the timestamp/cashier # also?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 19:43:01

note the HAVING condition there

it is restricting there must be 2 distinct items in that receipt

KH
[spoiler]Time is always against us[/spoiler]

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 19:53:23

Yea I opened my mouth before I read through all the code. I also got it to show the cashier #, timestamp and register. Any idea how i'd get the BALANCE < 10 ? since they are in different columns?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 19:56:38

where is the balance column ?

KH
[spoiler]Time is always against us[/spoiler]

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 20:01:09

directly to the right of the item column. The column is labeled num_value

technically BALANCE is the "item" and $5.50 is the num_value

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 20:02:21

Balance is the sum of num_value for a receipt ?

KH
[spoiler]Time is always against us[/spoiler]

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 20:08:59

technically yes, but it gets reported as the SUM already so no math has to be done.

same idea if I wanted to write a search for anyone who bought MARBLED for under $3. How would I do it?

"Balance" is just the text label for the item, its num_value is reported as the sum of the receipt #

THhnks!!

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 20:46:40

would this work?

SELECT register_time, receipt_no, register_num, cashier, value_num
FROM posdata
WHERE (register_num = @regnumber) AND (item LIKE '%' + @item_desc + '%' OR
item LIKE '%' + @item_desc2 + '%' OR (item LIKE '%' + @item_desc3 + '%' AND value_num < $10) ) AND (register_time BETWEEN @start_time AND @end_time)
GROUP BY register_time, receipt_no, register_num, cashier, value_num
HAVING (COUNT(DISTINCT item) >= 3)

item_desc3 being BALANCE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 21:14:26

[code]
declare @posdata table
(
Idx varchar(3),
receipt_no varchar(4),
register_time datetime,
item varchar(10),
value_num decimal(10,2),
reg_num int,
qty int,
cashier int
)
-- Sample Data
insert into @posdata
select '001', '0001', '11/12/2009 8:00am', 'MARBLES', 2.00, 5, 1, 12345 union all
select '002', '0001', '11/12/2009 8:00am', 'CANDY', 1.00, 5, 1, 12345 union all
select '003', '0001', '11/12/2009 8:00am', 'MILK 2%', 2.50, 5, 1, 12345 union all
select '004', '0001', '11/12/2009 8:00am', 'BALANCE', 5.50, 5, 1, 12345 union all
select '005', '0001', '11/12/2009 8:00am', 'CASH', 5.50, 5, 1, 12345 union all
select '006', '0001', '11/12/2009 8:00am', 'CHANGE', 0.00, 5, 1, 12345 union all
select '007', '0002', '11/12/2009 8:10am', 'MARBLES', 2.00, 5, 1, 12345 union all
select '008', '0002', '11/12/2009 8:10am', 'CANDY', 1.00, 5, 1, 12345 union all
select '009', '0002', '11/12/2009 8:10am', 'MARBLES', 2.50, 5, 1, 12345 union all
select '010', '0002', '11/12/2009 8:10am', 'BALANCE', 5.50, 5, 1, 12345 union all
select '011', '0002', '11/12/2009 8:10am', 'CASH', 5.50, 5, 1, 12345 union all
select '012', '0002', '11/12/2009 8:10am', 'CHANGE', 0.00, 5, 1, 12345

-- Query
select receipt_no, register_time, cashier
from @posdata
where (
item like '%' + 'MARBLES' + '%'
or item like '%' + 'MILK' + '%'
or item = 'BALANCE'
)
group by receipt_no, register_time, cashier -- assuming that register_time & cashier is same across a receipt
having count(distinct item) = 3 -- equal to 3 because there must be 3 distinct item count : BALANCE, MARBLES & MILK
and sum(case when item = 'BALANCE' then value_num end) <= 10 -- less or equal to 10

/*
receipt_no register_time cashier
---------- ------------------------------------------------------ -----------
0001 2009-11-12 08:00:00.000 12345

(1 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bandguy001
Starting Member

12 Posts

Posted - 2009-12-07 : 22:56:14

sounds great! Thank you for all your help :) I'd like to learn more about SQL. Any pointers for a starting spot?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources