SSRS parms for SQL Query

I ned advise on how to best set up parms in the SSRS and the SQL for the following WHERE clause in my Query.  The year date is the problem here.  I get a value of 2013 from my dropdown and need to pass into the SQL.  And so I can set up the next parm to  be the current year minus 90 days.
I'm stuck on how to declare and concatenate the values in the SQL


here is the where:

 
(m1.MemberEffectiveDate between '01-01-2013' and '12-31-2013'
OR
m1.MemberEffectiveDate between '10-03-2012' and '12-31-2012'
and (j2.dateofcompletion not between '10-03-2012' and '12-31-2012'  
or j2.dateofcompletion is null)
)


thinking out loud i need a @jan = '01-01-' and concatenate to the @year but how??
rschmehlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
@jan = CONVERT(DATETIME, CAST(@year AS VARCHAR) + '0101', 112)
0
Brian CroweDatabase AdministratorCommented:
also you can replace

m1.MemberEffectiveDate between '01-01-2013' and '12-31-2013'

with

YEAR(m1.MemberEffectiveDate) = @year
0
PortletPaulfreelancerCommented:
for retention of indexes in the query execution it is safer to avoid applying functions to the data (to suit the parameters) - instead it is generally a better approach to do the reverse and adjust the parameters to suit the data.

+Currently you are using "between" - and I suspect this will be a hard sell, but "between" is not the best of options for a date range. see: "Beware of Between"

BriCrowe has already provided how to use the year parameter, so I would suggest the equivalent of this:
@start = convert(datetime, cast(@year AS varchar) + '0101', 112)


...

 (    m1.MemberEffectiveDate>= @start 
  AND m1.MemberEffectiveDate < dateadd(year,1,@start )  -- nb: less than only
 )
OR
 (
    (     m1.MemberEffectiveDate >= dateadd(day,90,@start)
      AND m1.MemberEffectiveDate  < dateadd(year,1,@start )   -- nb: less than only
    )
    AND
     (
       (      j2.dateofcompletion < dateadd(day,90,@start)  -- nb: less than only
          AND j2.dateofcompletion >= dateadd(year,1,@start ) 
       )
     OR j2.dateofcompletion is null
    )
 )

Open in new window

I realize this may appear more complex, but it is "sargable" and often to achieve that the sql looks harder, but the execution is simpler.

http://en.wikipedia.org/wiki/Sargable

Rules of thumb
Avoid functions using table values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rschmehlAuthor Commented:
Thanks, I got it
0
PortletPaulfreelancerCommented:
Thanks for the grading. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.