quote:

---

Originally posted by Page47

Without knowing what you are trying to do, it is hard to say what you are doing wrong other than your syntax is way off.

Additionally, you should rethink your algorithm for calculating age (if you mean that in the traditional sense), unless we all celebrate our birthday on Jan 1 ...

Jay
to here knows when

---

SELECT countperson, person_nbr, date_of_birth, age
FROM
(
	select count(*) as countperson,a.person_nbr,a.date_of_birth,(2008-year(a.date_of_birth)) as age
	from person a
	join diabetes_flwsheet_ b on a.person_id = b.person_id
	where year(b.create_timestamp) = '2008'
	group by a.person_nbr,a.date_of_birth
) tmp
where age >=18 and countperson >=2

quote:

---

Originally posted by tkizer

You are using a derived table incorrectly:

SELECT countperson, person_nbr, date_of_birth, age
FROM
(
	select count(*) as countperson,a.person_nbr,a.date_of_birth,(2008-year(a.date_of_birth)) as age
	from person a
	join diabetes_flwsheet_ b on a.person_id = b.person_id
	where year(b.create_timestamp) = '2008'
	group by a.person_nbr,a.date_of_birth
) tmp
where age >=18 and countperson >=2

You should be able to do it without a derived table though, using HAVING for the countperson check and adding your age >= 18 into the main where clause.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---

quote:

---

Originally posted by sross81

I am sorry I should have been more specific that I actually need the derived table for the next step. Here is my full code. I am trying to get a distinct list of persons with at least 2 visits so far in 2008 from the diabetes_ table and then I want to join that list to the diabetes_flwsheet_ table to get their most recent percent readings. I still have my syntax off....??

With Diabetes(person_id) as
(
select distinct person_id from
(select count(*) as countperson,a.person_id,a.date_of_birth,
(2008-year(a.date_of_birth)) as age
from person a
join diabetes_ b on a.person_id = b.person_id
where year(b.create_timestamp) = '2008'
group by a.person_nbr,a.date_of_birth))tmp
where tmp.age >=18 and tmp.countperson >=2

select tmp.person_id, a.hga1cpercent
from Diabetes tmp
join diabetes_flwsheet_ a on tmp.person_id = a.person_id

quote:

---

Originally posted by tkizer

You are using a derived table incorrectly:

SELECT countperson, person_nbr, date_of_birth, age
FROM
(
	select count(*) as countperson,a.person_nbr,a.date_of_birth,(2008-year(a.date_of_birth)) as age
	from person a
	join diabetes_flwsheet_ b on a.person_id = b.person_id
	where year(b.create_timestamp) = '2008'
	group by a.person_nbr,a.date_of_birth
) tmp
where age >=18 and countperson >=2

You should be able to do it without a derived table though, using HAVING for the countperson check and adding your age >= 18 into the main where clause.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---

---

quote:

---

Originally posted by Page47

I don't know how you inferred your solution from the malformed CTE above, but I commend your powers of clairvoyance with regard to the OP's required "criteria" ...

Jay
to here knows when

---