January 8, 2015 at 3:18 am
Hi to all. I would like to modify code below to filter on a different date field based on a user selection.
The current WHERE below:
WHERE
ecnt.EngineeringChangeNoteTypeId IN (@ECNType)
AND ecn.SystemType IN (@Status)
AND ecn.CreatedDate >=@StartDate AND ecn.CreatedDate <=@EndDate
If the user selects a specific @status value they want to filter on a different date value. So along the lines of:
IF @status = 'C'
ecn.FinishingEffectivityDate >=@StartDate AND ecn.FinishingEffectivityDate<=@EndDate
ELSE ecn.CreatedDate >=@StartDate AND ecn.CreatedDate <=@EndDate
I hope that makes sense.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2015 at 3:32 am
CASE WHEN @status = 'C' AND ecn.FinishingEffectivityDate >=@StartDate AND ecn.FinishingEffectivityDate<=@EndDate THEN 1
WHEN @status != 'C' AND ecn.CreatedDate >=@StartDate AND ecn.CreatedDate <=@EndDate THEN 1
ELSE 0
END = 1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2015 at 3:36 am
Thanks for your quick response.
WHERE
ecnt.EngineeringChangeNoteTypeId IN (@ECNType)
AND ecn.SystemType IN (@Status)
CASE WHEN @status = 'C' AND ecn.FinishingEffectivityDate >=@StartDate AND ecn.FinishingEffectivityDate<=@EndDate THEN 1
WHEN @status != 'C' AND ecn.CreatedDate >=@StartDate AND ecn.CreatedDate <=@EndDate THEN 1
ELSE 0
END = 1
This returns syntax error near keyword case which indicates I have dome something wrong! 🙂
If I remove code below your solution runs perfectly.
ecnt.EngineeringChangeNoteTypeId IN (@ECNType)
AND ecn.SystemType IN (@Status)
UPDATE:
I think I have it. I have changed query to CTE then added your suggestion to filter on that.
Thanks for your help.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2015 at 4:12 am
2Tall (1/8/2015)
Thanks for your quick response.WHERE
ecnt.EngineeringChangeNoteTypeId = @ECNType
AND ecn.SystemType = @status AND
CASE WHEN @status = 'C' AND ecn.FinishingEffectivityDate >=@StartDate AND ecn.FinishingEffectivityDate<=@EndDate THEN 1
WHEN @status != 'C' AND ecn.CreatedDate >=@StartDate AND ecn.CreatedDate <=@EndDate THEN 1
ELSE 0
END = 1
This returns syntax error near keyword case which indicates I have dome something wrong! 🙂
You left out an AND
I also changed your INs to =, as there's no real point to an IN with a single value in the brackets
Bear in mind while this works, it's not efficient and will have performance issues on larger row counts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2015 at 4:58 am
Hi. The IN was used as the parameter in the report accepts multiple values. Although not ideal regards performance it was a quick and dirty way to deliver the multi select option 🙂
I will re-test as I am not seeing the expected results regards the date filter.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2015 at 5:16 am
It would appear I cannot have multiple selections for @status due to the expected Boolean expression.
CASE WHEN @status = 'C' AND ecn.FinishingEffectivityDate >=@StartDate AND ecn.FinishingEffectivityDate<=@EndDate THEN 1
WHEN @status != 'C' AND ecn.CreatedDate >=@StartDate AND ecn.CreatedDate <=@EndDate THEN 1
ELSE 0
END = 1
Mmm, I will need to have a think!
Many Thanks,
Phil
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2015 at 5:55 am
Perhaps you can (incur yet another performance hit and) use LIKE?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 8, 2015 at 6:13 am
Hi Phil. The report does not pose much of a performance hit although I agree it is not the best way to write code (limited by ability in this instance 🙂 ). The report executes in under a second and is only run by a few users.
I appreciate all the feedback.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2015 at 6:07 pm
You might want to consider using a dynamic search stored procedure for this:
http://www.sqlservercentral.com/articles/T-SQL/103529/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2015 at 12:59 am
Hi Dwain, thanks for the link. I may need a lobotomy to implement 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 9, 2015 at 1:29 am
2Tall (1/8/2015)
Hi. The IN was used as the parameter in the report accepts multiple values.
Except IN doesn't work that way.
DECLARE @SomeTable TABLE (SomeColumn VARCHAR(20))
INSERT INTO @SomeTable
Values ('A'),('B'),('C'),('''A'',''B'',''C''');
DECLARE @Statuses varchar(20);
SET @Statuses = '''A'',''B'',''C''';
SELECT * FROM @SomeTable WHERE SomeColumn IN (@Statuses);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2015 at 2:43 am
Hi Gail. I guess I am on the naughty step regards bad coding and a distinct lack of understanding:)
University put me off of programming and aged me by 10 years, this ruined my modelling career (not that I had one).
I would love for the penny to drop, maybe one day it will (back to the lobotomy).
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 9, 2015 at 6:47 am
If your @status parameter contains a comma-separated list of values you want to include in your WHERE clause, you could split the string and use it as a table in your query. Here's an example:
--create and populate a demo table
DECLARE @temp TABLE (
ID Integer not null,
Status varchar(16) not null);
INSERT INTO @temp(ID, Status)
VALUES(1, 'Active'),
(2, 'Inactive'),
(3, 'Locked'),
(4, 'Restricted'),
(5, 'Banned');
--define the search string
DECLARE @strSearch varchar(100) = 'Active,Inactive';
--search against the items in the delimited string
SELECT *
FROM @temp
WHERE Status IN (SELECT item
FROM DelimitedSplit8K(@strSearch, ','))
ORDER BY id;
Note that this uses Jeff Moden's DelimitedSplit8K function. If you don't know the function yet, the article is at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It's well worth the time to read it.
January 11, 2015 at 9:14 am
Thanks Ed.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply