User query help

For users or potential users.
Post Reply
babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

User query help

Post by babysilvertooth » Mon Apr 06, 2020 11:59 am

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

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: User query help

Post by Tom Zaccaria » Tue Apr 07, 2020 2:59 am

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

drtmz

babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Re: User query help

Post by babysilvertooth » Thu Apr 09, 2020 12:56 pm

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;

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: User query help

Post by Tom Zaccaria » Thu Apr 09, 2020 2:28 pm

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

babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Re: User query help

Post by babysilvertooth » Fri Apr 10, 2020 2:10 pm

Thank you! I'l try it

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: User query help

Post by Tom Zaccaria » Sat Apr 11, 2020 4:49 am

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

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: User query help

Post by Tom Zaccaria » Sat Apr 11, 2020 4:59 am

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

babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Re: User query help

Post by babysilvertooth » Mon Apr 13, 2020 1:06 pm

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!

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: User query help

Post by Tom Zaccaria » Tue Apr 14, 2020 3:05 am

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

Post Reply