August 9, 2017 at 1:32 pm
Maybe this has been answered before, but I can't seem to find what I need.
I have a SQL statement that has a Having clause, as well as Where.
I want to be able to say IF a parameter is x, then apply the Having line. IF a parameter is Y, then apply the Where statement.
Can this be done, and if so, how?
Here is an example of what I have:
SELECT Salesperson, SUM(SalesLines) FROM Table
WHERE Salesperson IN (SELECT DISTINCT Name from SalesPersonTable)
GROUP BY Salesperson, SalesLines
HAVING SUM(SalesLines) > 100
I want to say if Parameter1 evaluates to true, then apply the Where clause; if Parameter2 evaluates to true, apply the Having clause, or else don't.
Thank you!
August 9, 2017 at 4:14 pm
For starters, you probably don't need that DISTINCT in the subselect, nor should SalesLines be included in the GROUP BY. This could easily be done with dynamic SQL. A cruder version only using one parameter could be done like this, though I'm sure others will be able to provide far more elegant solutions:
DECLARE @x BIT
SET @x = 0
IF @x = 1
SELECT Salesperson, SUM(SalesLines)
FROM Table
WHERE Salesperson IN (SELECT Name from SalesPersonTable)
GROUP BY Salesperson
IF @x = 0
SELECT Salesperson, SUM(SalesLines)
FROM Table
GROUP BY Salesperson
HAVING SUM(SalesLines) > 100
August 9, 2017 at 4:24 pm
can you use a case statement to evaluate two different values based on your parameter? assuming the sum > 0 like this?
declare @param varchar(3) = 'Y'
SELECT Salesperson, SUM(SalesLines)
FROM [MyTable]
WHERE Salesperson IN (SELECT Name from SalesPersonTable)
GROUP BY Salesperson
HAVING SUM(SalesLines) > CASE WHEN @param = 'X' THEN 100 ELSE 0 END
Lowell
August 10, 2017 at 6:56 am
Lowell - Wednesday, August 9, 2017 4:24 PMcan you use a case statement to evaluate two different values based on your parameter? assuming the sum > 0 like this?
declare @param varchar(3) = 'Y'
SELECT Salesperson, SUM(SalesLines)
FROM [MyTable]
WHERE Salesperson IN (SELECT Name from SalesPersonTable)
GROUP BY Salesperson
HAVING SUM(SalesLines) > CASE WHEN @param = 'X' THEN 100 ELSE 0 END
This is perfect! Exactly what I needed, but I couldn't quite get my brain there.
Thank you so much. I really appreciate it, as well as the other answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply