January 5, 2012 at 3:46 pm
so I have a field of 100 patients, and their daily dosages..20mg, 40mg, 25mg ect.....
I'd like to have a column that calculates the 3rd Quartile for that dialy dosage column for all those patients, and thus my chart output can be fairly easily managed...
thanks
==================================================
SELECT convert(varchar(10),c.VistaCreateDate, 101) as Origdate,
c.RxNumber,
A.PatientName,
Left(a.PatientName,1) + RIGHT(a.PatientSSN,4) as [1stLst],
F.LocalDrugNameWithDose,
G.QtyNumeric,
G.DaysSupply,
CAST(CASE WHEN F.LocalDrugNameWithDose LIKE '%10mg%' THEN (G.QtyNumeric/G.DaysSupply)*10
WHEN F.LocalDrugNameWithDose LIKE '%20mg%' THEN (G.QtyNumeric/G.DaysSupply)*20
WHEN F.LocalDrugNameWithDose LIKE '%40mg%' THEN (G.QtyNumeric/G.DaysSupply)*40
WHEN F.LocalDrugNameWithDose LIKE 'citalopram%20mg%' THEN (G.QtyNumeric/G.DaysSupply)*20
WHEN F.LocalDrugNameWithDose like 'citalopram%40mg%' THEN (G.QtyNumeric/G.DaysSupply)*40 END as INT) as [DAILY DOSE],
C.IssueDate,
D.StaffName,
J.Schedule,
k.Sig
FROM LSV.SPatient.SPatient as A INNER JOIN LSV.RxOut.RxOutpat as C on A.PatientSID = C.PatientSID
INNER JOIN LSV.RxOut.RxOutpatFill as G on C.RxOutpatSID = G.RxOutpatSID
INNER JOIN LSV.SStaff.SStaff as D on C.ProviderSID = D.StaffSID
INNER JOIN LSV.Dim.LocalDrug as F on C.LocalDrugSID = F.LocalDrugSID
INNER JOIN LSV.Dim.NationalDrug as H on C.NationalDrugSID = H.NationalDrugSID
INNER JOIN LSV.Patient.PatientTestResults as I on a.PatientSID = I.PatientSID
INNER JOIN LSV.RxOut.RxOutpatMedInstructions as J on c.rxoutpatSID = J.rxoutpatSID
INNER JOIN LSV.RxOut.RxOutpatSIG as k on c.rxoutpatSID = K.rxoutpatSID
where
C.Sta3n = 596 and
C.IssueDate between
Convert(varchar(10), DateAdd(d,-380,GETDATE()), 101) and Convert(varchar(10), DateAdd(d,1,GETDATE()), 101) and
c.rxstatus IN('active','suspended','hold')
and H.NationalFormularyName = 'citalopram TAB'
GROUP BY A.PatientSID, A.PatientName, Left(a.PatientName,1) + RIGHT(a.PatientSSN,4),F.LocalDrugNameWithDose,G.QtyNumeric
,G.DaysSupply,C.RxNumber,C.IssueDate,D.StaffName, c.VistaCreateDate, J.schedule,k.Sig
ORDER BY [DAILY DOSE] DESC
January 5, 2012 at 6:57 pm
You can always use NTILE() to come up with the records making up the 3rd quartile (and then compile them to whatever values you might need).
; with DosageCTE as (
SELECT convert(varchar(10),c.VistaCreateDate, 101) as Origdate,
c.RxNumber,
A.PatientName,
Left(a.PatientName,1) + RIGHT(a.PatientSSN,4) as [1stLst],
F.LocalDrugNameWithDose,
G.QtyNumeric,
G.DaysSupply,
CAST(CASE WHEN F.LocalDrugNameWithDose LIKE '%10mg%' THEN (G.QtyNumeric/G.DaysSupply)*10
WHEN F.LocalDrugNameWithDose LIKE '%20mg%' THEN (G.QtyNumeric/G.DaysSupply)*20
WHEN F.LocalDrugNameWithDose LIKE '%40mg%' THEN (G.QtyNumeric/G.DaysSupply)*40
WHEN F.LocalDrugNameWithDose LIKE 'citalopram%20mg%' THEN (G.QtyNumeric/G.DaysSupply)*20
WHEN F.LocalDrugNameWithDose like 'citalopram%40mg%' THEN (G.QtyNumeric/G.DaysSupply)*40 END as INT) as [DAILY DOSE],
C.IssueDate,
D.StaffName,
J.Schedule,
k.Sig
FROM LSV.SPatient.SPatient as A INNER JOIN LSV.RxOut.RxOutpat as C on A.PatientSID = C.PatientSID
INNER JOIN LSV.RxOut.RxOutpatFill as G on C.RxOutpatSID = G.RxOutpatSID
INNER JOIN LSV.SStaff.SStaff as D on C.ProviderSID = D.StaffSID
INNER JOIN LSV.Dim.LocalDrug as F on C.LocalDrugSID = F.LocalDrugSID
INNER JOIN LSV.Dim.NationalDrug as H on C.NationalDrugSID = H.NationalDrugSID
INNER JOIN LSV.Patient.PatientTestResults as I on a.PatientSID = I.PatientSID
INNER JOIN LSV.RxOut.RxOutpatMedInstructions as J on c.rxoutpatSID = J.rxoutpatSID
INNER JOIN LSV.RxOut.RxOutpatSIG as k on c.rxoutpatSID = K.rxoutpatSID
where
C.Sta3n = 596 and
C.IssueDate between
Convert(varchar(10), DateAdd(d,-380,GETDATE()), 101) and Convert(varchar(10), DateAdd(d,1,GETDATE()), 101) and
c.rxstatus IN('active','suspended','hold')
and H.NationalFormularyName = 'citalopram TAB'
GROUP BY A.PatientSID, A.PatientName, Left(a.PatientName,1) + RIGHT(a.PatientSSN,4),F.LocalDrugNameWithDose,G.QtyNumeric
,G.DaysSupply,C.RxNumber,C.IssueDate,D.StaffName, c.VistaCreateDate, J.schedule,k.Sig),
QuartileCTE as ( SElect NTILE(4) over (order by [daily dose] desc) Qtile, * from dosageCTE)
Select * from quartileCTE where qtile=3
ORDER BY [DAILY DOSE] DESC
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply