Year Filter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Year Filter

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-16 : 14:54:18

Ok I am experimenting with the data in this database and trying to determine the best way to write this query. I need to find patients that visited in 2005 or 2006 but have not come back since. At first I was doing it looking at patients that have only came in one time ever and that being in 2005 and 2006 but I think I may be missing some patients. I want to narrow it to patients that came in during 2005 and 2006 but have not come back since but I think I am just getting an unduplicated list of patients that visited during those years. Am I on track to do this with the query I have written and if so any ideas?

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth

from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
where year(b.create_timestamp) IN (2005,2006)
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth)tmp
/*where tmp.countenc <=1*/
)
select description,mstr_list_item_desc, count(*)
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,year(b.create_timestamp)as YOS,f.mstr_list_item_desc
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name,payer_id
from person_payer
where person_id = tmp.person_id
order by payer_id) d
join payer_mstr e on d.payer_id = e.payer_id
join mstr_lists f on e.financial_class = f.mstr_list_item_id
where c.description = 'Leon MD, Enrique' /*and
year(b.create_timestamp) IN (2005,2006)*/
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp,
f.mstr_list_item_dessc)tmp2
group by description,mstr_list_item_desc

Thanks in Advance!
Sherri

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-16 : 15:19:57

You haven't posted table structures or sample data, but I
think this logic will suit you, you'll have to adapt it to your own needs, of course.

DECLARE @table TABLE

(anID int
,aValue int
,Date datetime)

INSERT INTO @table
SELECT 1,1 ,'06/01/2005' union all
select 1,2 ,'06/01/2008'union all
select 2,3 ,'06/01/2006'

SELECT a.*
FROM
@table a

WHERE a.anId not in (select distinct anId from @table where DATE >= '01/01/2007')

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 15:23:34

If you'd like an efficient solution, you need to isolate the functions to one side or just use a date range.

WHERE SomeDateTimeColumn >= '01/01/2005' AND SomeDateTimeColumn < '01/01/2007'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-16 : 15:40:12

I am sorry I don't really understand. For each entry in the patient_encounter table it is given a time stamp. I want to be able to know who has not come back since 2005 or 2006 but if I put a date range how is that comparing who came in then to who came in now? I thought I needed to separate it out into first a query that looks at the entire patient_encouner table and for each person_id picks out the max create_timestamp and if that is equal to 2005 or 2006 then have it use those people to perform the rest of the functions. I just can't seem to get Max(create_timetamp) to ever work for me it always tells me it will not work with date time but in Access it works so I would think that there would be something similar in SQL?

quote:
Originally posted by tkizer

If you'd like an efficient solution, you need to isolate the functions to one side or just use a date range.

WHERE SomeDateTimeColumn >= '01/01/2005' AND SomeDateTimeColumn < '01/01/2007'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks in Advance!
Sherri

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-16 : 15:45:01

actually my sample query does just that but I missed a piece of tthe where clause when I copied and pasted
add
WHERE a.anId not in (select distinct anId from @table where DATE >= '01/01/2007')
and a.date >= '01/01/2005' and a.Date < '01/01/2007'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 15:45:53

If you'd like better help, you need to post sample data. We aren't familiar with your system, so posting a query like yours is not easy for us to figure out what is going on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-16 : 15:48:24

Hmmmm well I am looking at your query but I am just not sure how to interegrate it into my query. I am really new to this.

quote:
Originally posted by jimf

actually my sample query does just that but I missed a piece of tthe where clause when I copied and pasted
add
WHERE a.anId not in (select distinct anId from @table where DATE >= '01/01/2007')
and a.date >= '01/01/2005' and a.Date < '01/01/2007'

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-16 : 15:54:25

What sample data would you like to see? I tried to explain what the data looks like I am sorry if I was not clear.

person_ID create_timestamp
1, 4/1/2005
1, 5/1/2005
1, 5/1/2007

2, 1/1/2006
3, 6/1/2006

Since person ID 2 and 3 had their last timestamp in 2006 and none since they would be the two persons that I would want to further query to determine their finanical class, etc that the query does below. I got that part working already. The only part I am trying to do is first narrow down the people that came in during 2005 or 2006 but never had an encounter after that.

I am trying to get the list of people who have not come in since 2005 or 2006 in my CTE and then use that list of people for the rest of the queries. I just can't figure out a way to do it and I am not sure how to intergrate those union queries into mine.

Does this help at all?

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth)tmp
/*where tmp.countenc <=1*/
)select description,mstr_list_item_desc, count(*)
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,year(b.create_timestamp)as YOS,f.mstr_list_item_desc
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name,payer_id
from person_payer
where person_id = tmp.person_id
order by payer_id) d
join payer_mstr e on d.payer_id = e.payer_id
join mstr_lists f on e.financial_class = f.mstr_list_item_id
where c.description = 'Leon MD, Enrique' /*and
year(b.create_timestamp) IN (2005,2006)*/
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp,f.mstr_list_item_desc
)tmp2

group by description,mstr_list_item_desc

Here is how I tried to do the CTE to look at max date but no matter how I put in in its not working...

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id

group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
b.create_timestamp
having max(b.create_timestamp) IN (2005,2006))tmp
/*where tmp.countenc <=1*/
)

quote:
Originally posted by tkizer

If you'd like better help, you need to post sample data. We aren't familiar with your system, so posting a query like yours is not easy for us to figure out who is going.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks in Advance!
Sherri

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-16 : 16:01:57

Does this return the expected results? If so, you can then start joining up the other tables to it.

DECLARE @table TABLE

(anID int

,Date datetime)

INSERT INTO @table
SELECT 1, '4/1/2005' union all
SELECT 1, '5/1/2005' union all
SELECT 1, '5/1/2007' union all

SELECT 2, '1/1/2006' union all
SELECT 3, '6/1/2006'

SELECT a.*
FROM
@table a

WHERE a.anId not in (select distinct anId from @table where DATE >= '01/01/2007')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 16:03:18

Instead of using NOT IN, use NOT EXISTS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-16 : 16:06:34

I am sorry I haven't been able to test it because I just don't know how to put it into my query I have wrote. Thank you for your help I am just new to this and get confused easily. I will just run it for all and export to access and query it that way I guess cause I know I can at least get the project done. I still have alot to learn! :)

quote:
Originally posted by jimf

Does this return the expected results? If so, you can then start joining up the other tables to it.

DECLARE @table TABLE

(anID int

,Date datetime)

INSERT INTO @table
SELECT 1, '4/1/2005' union all
SELECT 1, '5/1/2005' union all
SELECT 1, '5/1/2007' union all

SELECT 2, '1/1/2006' union all
SELECT 3, '6/1/2006'

SELECT a.*
FROM
@table a

WHERE a.anId not in (select distinct anId from @table where DATE >= '01/01/2007')

Thanks in Advance!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources