Problem with WHERE clause

[SQLBI]

Hi,

I'm trying to put together a recordset but i need to retrieve data based on 2 arguments.

My code at present is

WHERE ((R.[Sub Group Code 1] = 1 AND
	R.[Result Code] = 13) OR
	(R.[Sub Group Code 1] = 99 AND R.[Result Code] = 1))

However this appears to not be working.

I want to avoid if possible using

WHERE R.[Sub Group Code 1] IN (1, 99) AND
	R.[Result Code] IN (1, 13)

As this will bring back too much data and have an impact on query time.

Can anyone suggest where i'm going wrong?

Thanks in advance.


Cheers,
Leigh

"Give a man a fish and he can feed himself for a day..."
Give him the means to catch his own fish and he can really annoy his partner by spending all his spare time fishing!

 

[SQLBill]

First things first...

Have you tried the WHERE statements separately to see if they return what you expect? If not, run them this way...

WHERE (R.[Sub Group Code 1] = 1 AND
    R.[Result Code] = 13)

and then

WHERE (R.[Sub Group Code 1] = 99 AND R.[Result Code] = 1)

Do those return what you expect to see from each separately?

-SQLBill

Posting advice: FAQ481-4875

 

[Onyxpurr]

Are you getting any errors or just no records?

 

[SQLBI]

Hi,

The problem appears to have been in my SELECT statement, in particular my BETWEEN argument.

Upon changing my code to

	SUM(CASE WHEN R.[Sub Group Code 1] = 99 AND R.[Result Code] = 1 AND [b][blue]R.[Date] <= Q2.[This Mth] AND R.[Date] >= Q2.[Mth -11][/blue][/b] THEN R.[Data] ELSE NULL END) AS 'Invoiced Sales'

The problem seems to have been rectified.

Thanks for your help guys.


Cheers,
Leigh

"Give a man a fish and he can feed himself for a day..."
Give him the means to catch his own fish and he can really annoy his partner by spending all his spare time fishing!