Help Creating a Parameter

  • I've have two versions of a query I want to use in Report Builder but I'm not sure how to create a parameter from a temp table or embedded query.

    Here's the 1st query which uses a temp table to filter the dataset used in the result set:

    --VERSION 1--

    declare @encounters table (person_id varchar(36))

    insert into @encounters (person_id)

    select person_id from patient_encounter where billable_timestamp between '20120101' and '20141231' and person_id in

    (select person_id from patient_encounter [highlight="#ffff11"]where billable_timestamp between '20150101' and '20151231'[/highlight])

    select last_name [Last Name], first_name [First Name], date_of_birth DOB, person_nbr Person# from person

    where date_of_birth between '20120101' and '20121231'

    and uds_homeless_status_id is not null

    and uds_homeless_status_id not in ('A14323BA-AD13-465B-8EC1-4C5A13E1B958', '96778FE1-8FC6-423C-A95B-2288424E2868')

    and person_id in (select person_id from @encounters)

    I need to create a parameter called 'Measure Year" for the highlighted dates in the temp table. Users need to be able to select the measure year; 2015, 2016, 2017 etc. Only the year changes, the range remains the same.

    Here is an alternate query I could use if it's easier to accomplish the goal of having a parameter for the measure year:

    --VERSION 2--

    --ALL PATIENTS IN NGTEST - 99,990

    select first_name + ' ' + last_name Patient,

    substring (date_of_birth, 5,2) + '-' +

    substring (date_of_birth, 7,2) + '-' +

    substring (date_of_birth, 1,4) DOB,

    substring(person_nbr, patindex('%[^ ]%', person_nbr+''), len(person_nbr)) Person#

    --person_id PersonID

    from person

    where date_of_birth between '20120101' and '20121231' --1684 pts born in 2012

    and uds_homeless_status_id is not null --1482 pts born in 2012 where homeless status is empty

    and uds_homeless_status_id != 'A14323BA-AD13-465B-8EC1-4C5A13E1B958' --445 pts born in 2012 where 'Not Homeless' is unchecked

    and uds_homeless_status_id != '96778FE1-8FC6-423C-A95B-2288424E2868' --374 pts born in 2012 where 'Unknown/Unreported' also is unchecked

    --and uds_homeless_status_id not in ('A14323BA-AD13-465B-8EC1-4C5A13E1B958', '96778FE1-8FC6-423C-A95B-2288424E2868') - previous two lines combined

    --and expired_ind = 'N' --all deceased pts born in 2012 (0 in ngtest)

    and person_id in

    --ALL ENCOUNETERS IN 2012 - 106,577

    (select person_id

    from patient_encounter

    where billable_timestamp between '20120101' and '20141231' --330 pts born in 2012 with enconter(s) before 2015

    and person_id in

    --ALL ENCOUNTERS IN 2015 - 134,345

    (select person_id

    from patient_encounter

    [highlight="#ffff11"]where billable_timestamp between '20150101' and '20151231'[/highlight])) --125 pts born in 2012 with enconter(s) before 2015 and encouters in 2015

    order by Patient, DOB, Person#

    Same goal... I need to create a parameter called 'Measure Year" for the highlighted dates in the last embedded query (not sure if that is the right terminology).

    I hope I explained this right. Please let me know if clarification is needed.

    Thanks in advance for the assistance. it's been a few years since I've worked in Report Builder.

  • David,

    you could modify your stored procedure so that you can pass in a date and use something like this in your filter

    If you could use DATEFROMPARTS, you'd be in the clear. But since you're using 2008, it's not available. Here's a link to someone who did this... Steve Stedman's DateFromParts function[/url].

    (from Lynn Pettis' blog here[/url])

    DECLARE @BeginningOfThisYear DATE,

    @BeginningOfNextYear DATE

    select @BeginningOfThisYear = dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year

    select @BeginningOfNextYear = dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year

    Then you could just do

    SELECT....

    FROM ...

    WHERE

    SomeDate >= @BeginningOfThisYear

    AND SomeDate<@BeginningOfNextYear

    You would just calculate the two dates (@BeginningOfThisYear and @BeginningOfNextYear) at the top of your stored procedure and then use the two dates in your WHERE clause.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply