August 29, 2018 at 3:10 pm
Hi,
I have this table CREATE TABLE Test (
FirstName VARCHAR(50)
,LastName VARCHAR(50)
,Active INT
,MaxBC INT
)
INSERT INTO Test (FirstName
,LastName
,Active
,MaxBC)
VALUES ('John','Smith',1,1)
,('John','Smith',0,1)
,('John','Smith',0,2)
,('John','Smith',1,2)
,('John','Smith',1,3)
,('John','Smith',1,3)
HI want to select data from this table and I have this variable which can 0 or any number. if @ExMaxBC is 0, I want to select only Active records (Active = 1). if @ExMaxBC is any number, I want to select records where MaxMC from my table where it is > ExMaxBC. E.G 1
@ExMaxBC = 0
SELECT
FirstName, LastName, Active, MaxBC
WHERE Active = 1
e.G 2
@ExMaxBC = 1
SELECT
FirstName, LastName, Active, MaxBC
WHERE MaxBC > @ExMaxBC
The first Example should only return Active = 1 records, the second Example should return both Active = 0 and Active = 1 records, but with MaxBC > @ExMaxBC
Please help
August 29, 2018 at 3:19 pm
Just write it as a piece of conditional logic, i.e.WHERE (@ExMaxBC = 0 AND Active = 1) OR MaxBC > @ExMaxBC
August 29, 2018 at 3:30 pm
andycadley - Wednesday, August 29, 2018 3:19 PMJust write it as a piece of conditional logic, i.e.WHERE (@ExMaxBC = 0 AND Active = 1) OR MaxBC > @ExMaxBC
I have set my @ExMaxBC = 0 but I'm still getting both Active = 0 and Active = 1
August 29, 2018 at 3:49 pm
Dumb moment on my part, try:WHERE (@ExMaxBC = 0 AND Active = 1) OR (@ExMacBC <> 0 And MaxBC > @ExMaxBC)
August 30, 2018 at 1:39 am
andycadley - Wednesday, August 29, 2018 3:49 PMDumb moment on my part, try:WHERE (@ExMaxBC = 0 AND Active = 1) OR (@ExMacBC <> 0 And MaxBC > @ExMaxBC)
Yes it is now working thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply