June 15, 2021 at 5:18 pm
I have a crystal report I'm trying to create a sp for the data so I can use in SSRS. This is the statement which works:
SELECT p.JCCo,p.Job,substring(p.Phase,2,2) as Division,rtrim(ltrim(p.Phase)) as Phase,p.Description as PhaseDesc,j.Description as JobName,
j.udConstructionStart as ConstructionStart, j.udForecastedComplete as ForecastedComplete
FROM JCJP p inner join JCJM j
ON p.JCCo=j.JCCo and p.Job=j.Job
where p.JCCo=@Company and p.Job=@Job and substring(Phase,1,3) not in (' 00',' 20')
Now I have to add 3 parameters (@Incl17, @Incl18 and @Incl19). If either of these are a Y then those substring(p.Phase,2,2) values of '17', '18' and '19' have to be included as well.
In Crystal Reports this is how it works but cannot seem to get this to work in SQL.
not (Left ({JCJP.Phase},3) in [" 00", " 20"]) and
(if {?Div17}='N' then Left ({JCJP.Phase},3)<>' 17' else 1=1) and
(if {?Div18}='N' then Left ({JCJP.Phase},3)<>' 18' else 1=1) and
(if {?Div19}='N' then Left ({JCJP.Phase},3)<>' 19' else 1=1)
Any ideas? Have tried CASE in many ways but it's not working.
June 15, 2021 at 5:39 pm
I would probably take this approach (although not that efficient, it is easy to read in my opinion):
where (p.JCCo=@Company and p.Job=@Job and substring(Phase,1,3) not in (' 00',' 20')) AND
(@Incl17='Y' OR (@Incl17='N' AND LEFT(Phase,3) NOT IN (' 17'))) AND
(@Incl18='Y' OR (@Incl18='N' AND LEFT(Phase,3) NOT IN (' 18'))) AND
(@Incl19='Y' OR (@Incl19='N' AND LEFT(Phase,3) NOT IN (' 19')))May not be the most efficient approach, but I think it should work. Depending on how much data those INCL17,18 and 19 variables are filtering out, you may get better performance by sending all of the data back to SSRS and letting SSRS filter the rows.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 15, 2021 at 6:07 pm
Fantastic! The number of records that each @Incl filters out is minimal.
June 16, 2021 at 3:00 pm
For the pure SQL part, technically this could theoretically perform better since it avoids using functions on the table columns:
WHERE (p.JCCo=@Company and p.Job=@Job AND Phase NOT LIKE ' 00%' AND Phase NOT LIKE ' 20%') AND
(@Incl17='Y' OR Phase NOT LIKE ' 17%') AND
(@Incl18='Y' OR Phase NOT LIKE ' 18%') AND
(@Incl19='Y' OR Phase NOT LIKE ' 19%')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply