CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Misiu
Misiu

Reputation: 4919

Multiple between criteria on column

I have a very simple table containing 3 columns:

ID  |  Orders | Code
--------------------
  1 |      11 |  'OK'
  2 |      25 | 'ER1'
  3 |       0 |  'OK'
  4 |      30 | 'ER2'
  5 |      42 |  'OK'

I'm trying to create stored procedure to select rows that meet my criterias:

What I need is to be able to select rows that for example have less than 10 orders or more that 30.

so sample call to my procedure would be:

EXEC REPORT1 @code='OK', @orders0_10=1,@ordersMore30=1

This should return rows 3 and 5. Below is my procedure so far

CREATE PROCEDURE [dbo].[REPORT1]
(
@code NVARCHAR (5) = '',
@orders0_10 INT = 0,
@orders11_20 INT = 0,
@orders21_30 INT = 0,
@ordersMore30 INT = 0,
)
AS

SET NOCOUNT ON

SELECT TOP 1000
    ROW_NUMBER() OVER (ORDER BY RPT.Nr) AS Lp,
    RPT.ID,
    RPT.Orders,
    RPT.Code
FROM MyData RPT (NOLOCK)
WHERE (@code = '' OR RPT.Code= @code)
AND
--(
   (@orders0_10 = 1 AND RPT.Orders<= 10)
--OR (@orders11_20 = 1 AND (RPT.Orders > 10 AND RPT.Orders <= 20))
--OR (@orders21_30 = 1 AND (RPT.Orders > 20 AND RPT.Orders <= 30))
--OR (@ordersMore30 = 1 AND RPT.Orders > 30)
--)
ORDER BY RPT.Nr
GO

EDIT I'll try to clarify what I need.
If I specify criterias like here: http://sqlfiddle.com/#!3/25045/19 I'm getting 2 rows (1,3) and that is correct.

If I change them to this: http://sqlfiddle.com/#!3/25045/21 I also bet 2 rows (2,4) and that is correct.

But if I specify them as so: http://sqlfiddle.com/#!3/25045/22 I should get union of previous results (1,2,3,4):

rows with Sales< 10 OR 20

EDIT I got final working version: http://sqlfiddle.com/#!3/25045/33, hope this will be useful for someone.

Upvotes: 1

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Your criteria are:

where (@orders0_10 = 0 OR RPT.Orders<= 10) AND
      (@orders11_20 = 1 OR (RPT.Orders > 10 AND RPT.Orders <= 20)) AND
      (@orders21_30 = 1 OR (RPT.Orders > 20 AND RPT.Orders <= 30)) AND
      (@ordersMore30 = 1 OR RPT.Orders > 30)

I think the problem is the 0 in the first row; it should be a 1:

where (@orders0_10 = 1 OR RPT.Orders<= 10) AND
      (@orders11_20 = 1 OR (RPT.Orders > 10 AND RPT.Orders <= 20)) AND
      (@orders21_30 = 1 OR (RPT.Orders > 20 AND RPT.Orders <= 30)) AND
      (@ordersMore30 = 1 OR RPT.Orders > 30)

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460058

You can use the COALESCE "trick":

SELECT 
    RPT.ID,
    RPT.Orders,
    RPT.Code
FROM MyData RPT 
WHERE COALESCE(@code, RPT.Code) = RPT.Code
AND   COALESCE(@orders0_10, RPT.Orders)   <= RPT.Orders
AND   COALESCE(@ordersMore30, RPT.Orders) >= RPT.Orders

Demo

Upvotes: 0

Related Questions