February 26, 2016 at 8:18 pm
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.
February 27, 2016 at 9:29 am
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