Notifications
Clear all
Topic starter
What tables are needt to get a listing of pharmacies with number of ordes placed at each pharmcy.
Is it pharmacy, medcate or another set of tables?
Thanks .
Posted : February 25, 2015 12:33 pm
Funny I was just in my system doing something similar. I think it's the PRESCRIB table that you are looking for. It contains each prescription and relationship to the pharmacy table. This query might get you close to what you are looking for:
SELECT Pharmacy.name,Count(PHARMBUSID)as TotalSripts from PRESCRIB INNERJOIN Pharmacy ON PRESCRIB.PHARMBUSID=Pharmacy.PharmacyId WHERE
PRESCRIB.CLINICALDATE BETWEEN'1/1/2014 12:00AM'AND'12/31/2014 11:59PM' GROUP
BY Pharmacy.name
Posted : February 25, 2015 12:45 pm
Try this:
Posted : February 25, 2015 1:49 pm
you can try this query - this is what I use.
SELECT
person.lastname,
person.firstname,
person.middlename,
PRESCRIB.RXTYPE,
person.PatientId,
(SELECT
RTRIM(RTRIM(USRINFO.LASTNAME + ' ' + ISNULL(USRINFO.Suffix,'')) + ', '+ ISNULL(USRINFO.FIRSTNAME,'')
+ ' ' + ISNULL(USRINFO.MIDDLENAME,''))
FROM
usrinfo
WHERE
usrinfo.pvid = document.usrid) document_provider,
(SELECT locreg.NAME
FROM
locreg
WHERE
locreg.locid = document.locofcare) LOCATION,
(SELECT
RTRIM(RTRIM(USRINFO.LASTNAME + ' ' + ISNULL(USRINFO.Suffix,'')) + ', '+ ISNULL(USRINFO.FIRSTNAME,'')
+ ' ' + ISNULL(USRINFO.MIDDLENAME,''))
FROM
usrinfo
WHERE
usrinfo.pvid = prescrib.pvid) prescribing_provider,
/*DECODE
(prescrib.rxtype,
'W', 'Handwritten',
'H', 'Historical',
'F', 'Fax to pharmacy',
'A', 'Re-fax',
'D', 'Pharmacy''s prescribing method',
'Q', 'Print then fax to pharmacy',
'P', 'Print then give to patient',
'R', 'Reprint',
'M', 'Print then mail to patient',
'L', 'Print then mail to pharmacy',
'S', 'Samples given to patient',
'T', 'Telephone',
'E', 'Electronic','') rxtype,*/
prescrib.clinicaldate prescrib_date,
PRESCRIB.PHARMACY,
MEDICATE.DESCRIPTION
FROM
document,
person,
medicate,
prescrib
WHERE
document.clinicaldate >= 1738368000000000 and
DOCUMENT.CLINICALDATE <= 1740009600000000
AND
document.pid = person.pid AND
document.pid = medicate.pid AND
document.sdid = medicate.sdid AND
medicate.pid = prescrib.pid AND
medicate.mid = prescrib.mid AND
document.sdid = prescrib.sdid AND
document.pid = prescrib.pid
group by
person.lastname,
person.firstname,
person.middlename,
person.PatientId,
DOCUMENT.LOCOFCARE,
PRESCRIB.PVID,
PRESCRIB.CLINICALDATE,
PRESCRIB.RXTYPE,
document.USRID,
PRESCRIB.PHARMACY,
MEDICATE.DESCRIPTION
Posted : February 25, 2015 3:46 pm
select pharmacy, count(*) from PRESCRIB
where PHARMACY <> 'null'
group by pharmacy
order by 2 desc
Posted : February 25, 2015 4:39 pm
