Open Dental Forum

I found the query to find patients of a certain age range, but I want to include their home and cell phone numbers. Not sure where or how to add that to the code to get the information.

Thanks

Post the code you have so far and I will see what help to offer you.

drtmz

I have this, but only shows the name, I want to add the phone number so I can call them instead of looking up each one.
Thanks

/*175 Patients of specifed age range and gender*/
SET @Young='1', @Old='100'; #Change age range here.
SET @Gender='1'; #Change gender here. 0-Male, 1-Female, 2-Unknown
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', PatNum,
(YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) AS 'Age',
(CASE WHEN Gender=0 THEN 'M' WHEN Gender=1 THEN 'F' ELSE 'U' END) AS 'Gender'
FROM patient
WHERE ((YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5))) BETWEEN @Young AND @Old
AND Gender=@Gender
AND PatStatus=0
ORDER BY LName, FName;

This should do it. If you want work phone, it is easy to add

/*175 Patients of specifed age range and gender*/
SET @Young='1', @Old='100'; #Change age range here.
SET @Gender='1'; #Change gender here. 0-Male, 1-Female, 2-Unknown
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', PatNum,
(YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) AS 'Age',
(CASE WHEN Gender=0 THEN 'M' WHEN Gender=1 THEN 'F' ELSE 'U' END) AS 'Gender', WirelessPhone, HmPhone
FROM patient
WHERE ((YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5))) BETWEEN @Young AND @Old
AND Gender=@Gender
AND PatStatus=0
ORDER BY LName, FName;

drtmz

Thank you! I'l try it

Do do know that will only give one specific gender. You would have to run it once for males and again for females. If you want to remove the gender variable, let me know. And I think we can group it by age.

drtmz

Ok. Here is the same code modified for both sexes grouped by age. I think this is what you want.

/*175 Patients of specified age range and gender*/

SET @Young='1', @Old='100'; #Change age range here.
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', PatNum,
(YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) AS 'Age',
(CASE WHEN Gender=0 THEN 'M' WHEN Gender=1 THEN 'F' ELSE 'U' END) AS 'Gender', WirelessPhone, HmPhone
FROM patient
WHERE ((YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5))) BETWEEN @Young AND @Old
AND PatStatus=0
ORDER BY age;

drtmz

Thanks Tom, that is it, I ran the report and took it with me without reading it and realized it was single gender...started to work on the report and realized it.

One more request, can it only include only active patients?

Cheers!

It does as written, PatStatus=0, is code for active patients.

Now you may have entry errors in that an archived patient is still coded as active. But the report will return only those patients that are listed as active.

drtmz