MVECoder
2/23/2016 - 2:21 PM
Form Printer Output
Form Printer Output
USE [myvisionexpress]
GO
/****** Object: StoredProcedure [dbo].[SelECPFormTest] Script Date: 6/23/2017 7:59:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelECPFormTest]
@ContactID int
AS
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) as 'Today',
CONVERT(VARCHAR(10), GETDATE(), 101) as 'Today1',
CONVERT(VARCHAR(10), GETDATE(), 101) as 'Today2',
contactid as 'ContactID',
contactid as 'ContactID1',
contactid as 'ContactID2',
ISNULL([title],'') + ' ' + ISNULL(patient_nickname,firstname) + ' ' + ISNULL(middlename,'') + ' ' + ISNULL(lastname,'') + ' ' + ISNULL(suffix,'') as 'FullName',
ISNULL([title],'') + ' ' + ISNULL(firstname,'') + ' ' + ISNULL(middlename,'') + ' ' + ISNULL(lastname,'') + ' ' + ISNULL(suffix,'') + ' ' + ISNULL(LEFT(CONVERT(VARCHAR, birthdate, 101), 10),'') + ' ' + CONVERT(VARCHAR, FLOOR(DATEDIFF(day,[birthdate],GETDATE())/365.242199), 10) + 'yo' as 'FullNameDOB',
--ISNULL([title],'') + ' ' + ISNULL(patient_nickname,firstname) + ' ' + ISNULL(middlename,'') + ' ' + ISNULL(lastname,'') + ' ' + ISNULL(suffix,'') as 'FullNameDOB', --+ ' ' + ISNULL(birthdate,'')
--[firstname],
--[middlename] as 'MI',
--[lastname],
--[patient_nickname] as 'nickname',
ISNULL([addressline1],'') + ' ' + ISNULL(addressline2,'') + ' ' + ISNULL(city,'') + ' ' + ISNULL(stateorprovince,'') + ' ' + ISNULL(zipcode,'') as 'Address',
--[addressline2] as 'address2',
--[city],
--[zipcode] as 'zip',
[workphone] as 'WorkPhone',
--[workphoneextension] as 'ext',
[homephone] as 'HomePhone',
[cellularphone] as 'CellPhone',
[emailname] as 'EmailAddress',
--[suffix],
[guardian_name] as 'Guardian',
--[occupation],
[primary_physician] as 'PCP',
--[secondary_physician] as 'Specialist',
--[custom_field_11] as 'Grade',
CONVERT(VARCHAR(10), [birthdate], 101) as 'DOB',
CASE [preferred_contact_method]
WHEN 'Cell' THEN 'Cellphone'
WHEN 'Home' THEN 'Home phone'
WHEN 'Work' THEN 'Work'
WHEN 'E-mail' THEN 'E-Mail'
ELSE CAST([preferred_contact_method] as varchar(10))
END as 'PreferredContact',
(select CASE
WHEN contacts.ssn IS NULL
THEN ''
ELSE '* HIDDEN *'
END
from contacts where contactid = @ContactID) as 'SSN',
--[dbo].[fn_GetPatientSSN](contactid) 'ssn',
--as 'Letter',
[dbo].[fn_GetPatientLastAmountText](contactid) as 'AMT',
FLOOR(DATEDIFF(day,[birthdate],GETDATE())/365.242199) as 'Age',
[dbo].[fn_GetPatientDoctor](contactid) as 'APT_DOC',
[dbo].[fn_GetPatientNextAppointment](contactid) as 'NextApt',
[dbo].[fn_GetPatientApptIsAfter5PMFlag](contactid) as 'ISAPTAFTER5',
-- FIX Is Apt Triage, will this work with different subjects?
[dbo].[fn_GetPatientFlagBySubject](contactid, '300|308|652|653|10177') as 'ISAPTTRIAGE',
[dbo].[fn_GetPatientFlagBySubject](contactid, '249|250|654|299') as 'ISAPTREFRACT',
[dbo].[fn_GetPatientFlagBySubject](contactid, '249|654') as 'ISAPTNEWCOMP',
[dbo].[fn_GetPatientFlagBySubject](contactid, '250|299') as 'ISAPTESTCOMP',
[dbo].[fn_GetPatientInsurance](contactid, 'Medical', 'Primary') as 'PrimaryMedicalInsurance',
[dbo].[fn_GetPatientInsurance](contactid, 'Medical', 'Secondary') as 'SecondaryMedicalInsurance',
[dbo].[fn_GetPatientInsurance](contactid, 'Vision', 'Primary') as 'PrimaryRoutineInsurance',
[dbo].[fn_GetPatientInsurance](contactid, 'Vision', 'Secondary') as 'SecondaryRoutineInsurance',
(SELECT
MAX(
CASE
WHEN medication like '%actoplus%'
OR medication like '%actos%'
OR medication like '%Afrezza%'
OR medication like '%amaryl%'
OR medication like '%avandamet%'
OR medication like '%avandia%'
OR medication like '%Basaglar%'
OR medication like '%bromocript%'
OR medication like '%byett%'
OR medication like '%duetact%'
OR medication like '%farxi%'
OR medication like '%flexpen%'
OR medication like '%fortamet%'
OR medication like '%glimepi%'
OR medication like '%glipiz%'
OR (medication like '%gluco%' AND medication NOT like '%gluconat%' AND medication NOT like '%glucosomine%')
OR medication like '%glumet%'
OR medication like '%glybur%'
OR medication like '%glyset%'
OR medication like '%humulin%'
OR medication like '%insulin%'
OR medication like '%invoka%'
OR medication like '%janu%'
OR medication like '%jardiance%'
OR medication like '%jentadueto%'
OR medication like '%kombiglyze%'
OR medication like '%lantus%'
OR medication like '%levemir%'
OR medication like '%malog%'
OR medication like '%metformin%'
OR medication like '%novo%'
OR medication like '%olog%'
OR medication like '%onglyza%'
OR medication like '%pramlintide%'
OR medication like '%Precose%'
OR medication like '%ryzodeg%'
OR medication like '%sitaglip%'
OR medication like '%symlin%'
OR medication like '%toujeo%'
OR medication like '%tradj%'
OR medication like '%tresiba%'
OR medication like '%trulicity%'
OR medication like '%victoz%'
OR medication like '%welchol%'
THEN 'Diabetes'
ELSE '' END )
+ CHAR(13)+CHAR(10) +
MAX(
CASE
WHEN medication like '%altocor%'
OR medication like '%altoprev%'
OR medication like '%caduet%'
OR medication like '%colesevelam%'
OR medication like '%crestor%'
OR medication like '%ezetimibe%'
OR medication like '%fenofib%'
OR medication like '%gemfibrozil%'
OR medication like '%lipitor%'
OR medication like '%livalo%'
OR medication like '%lomitapide%'
OR medication like '%lopid%'
OR medication like '%mevachor%'
OR medication like '%niacin%'
OR medication like '%niacor%'
OR medication like '%niaspan%'
OR medication like '%praluent%'
OR medication like '%pravachol%'
OR medication like '%repatha%'
OR medication like '%tricor%'
OR medication like '%trilipix%'
OR medication like '%vastatin%'
OR medication like '%vytorin%'
OR medication like '%welchol%'
OR medication like '%zetia%'
OR medication like '%zocor%'
THEN 'Cholesterol, E78.0'
ELSE '' END )
+ CHAR(13)+CHAR(10) +
MAX(
CASE
WHEN medication like '%-nitro%'
OR medication like '%accupril%'
OR medication like '%accuretic%'
OR medication like '%aceon%'
OR medication like '%adalat%'
OR medication like '%afeditab%'
OR medication like '%aldactone%'
OR medication like '%aldoril%'
OR medication like '%aliskiren%'
OR medication like '%altace%'
OR medication like '%amlobenz%'
OR medication like '%amlodipine%'
OR medication like '%anolol%'
OR medication like '%april%'
OR medication like '%aquazide%'
OR medication like '%atacand%'
OR medication like '%avalide%'
OR medication like '%avapro%'
OR medication like '%azor%'
OR medication like '%benazepril%'
OR medication like '%benicar%'
OR medication like '%blocadren%'
OR medication like '%butolol%'
OR medication like '%bystolic%'
OR medication like '%caduet%'
OR medication like '%calan%'
OR medication like '%capoten%'
OR medication like '%capozide%'
OR medication like '%captopril%'
OR medication like '%cardizem%'
OR medication like '%cardura%'
OR medication like '%cartia%'
OR medication like '%carvedilol%'
OR medication like '%catapres%'
OR medication like '%chlorthalidone%'
OR medication like '%clonidine%'
OR medication like '%coreg%'
OR medication like '%corgard%'
OR medication like '%corzide%'
OR medication like '%covera%'
OR medication like '%cozaar%'
OR medication like '%dilt%'
OR medication like '%diltiazem%'
OR medication like '%diovan%'
OR medication like '%diuril%'
OR medication like '%doxazosin%'
OR medication like '%dyazide%'
OR medication like '%epril%'
OR medication like '%esidrix%'
OR medication like '%exforge%'
OR medication like '%felodipine%'
OR medication like '%furosemide%'
OR medication like '%hctz%'
OR medication like '%hydralazine%'
OR medication like '%hydrochlorothiazide%'
OR medication like '%hytrin%'
OR medication like '%hyzaar%'
OR medication like '%inderal%'
OR medication like '%innopran%'
OR medication like '%inspra%'
OR medication like '%ipril%'
OR medication like '%isoptin%'
OR medication like '%labetalol%'
OR medication like '%lasix%'
OR medication like '%losartan%'
OR medication like '%lotensin%'
OR medication like '%lotrel%'
OR medication like '%lozol%'
OR medication like '%matzim%'
OR medication like '%maxzide%'
OR medication like '%methyldopa%'
OR medication like '%metolazone%'
OR medication like '%micardis%'
OR medication like '%microzide%'
OR medication like '%minipress%'
OR medication like '%minitran%'
OR medication like '%minoxidil%'
OR medication like '%nebivolol%'
OR medication like '%nexiclon%'
OR medication like '%nifedical%'
OR medication like '%nifedipine%'
OR medication like '%nitrek%'
OR medication like '%nitro-%'
OR medication like '%nitrocot%'
OR medication like '%nitrogard%'
OR medication like '%nitrol%'
OR medication like '%nolol%'
OR medication like '%normodyne%'
OR medication like '%norvasc%'
OR medication like '%opril%'
OR medication like '%opril%'
OR medication like '%pressor%'
OR medication like '%prinivil%'
OR medication like '%prinzide%'
OR medication like '%prolol%'
OR medication like '%prolol%'
OR medication like '%sartan%'
OR medication like '%sectral%'
OR medication like '%spironolactone%'
OR medication like '%taztia%'
OR medication like '%tekt%'
OR medication like '%tenormin%'
OR medication like '%terazosin%'
OR medication like '%thiazide%'
OR medication like '%tiazac%'
OR medication like '%toprol%'
OR medication like '%tribenzor%'
OR medication like '%vasotec%'
OR medication like '%verapamil%'
OR medication like '%verelan%'
OR medication like '%zebeta%'
OR medication like '%zestoretic%'
OR medication like '%zestril%'
OR medication like '%ziac%'
OR medication like '%zosin%'
THEN 'Hypertension, I10'
ELSE '' END )
+ CHAR(13)+CHAR(10) +
MAX(
CASE
WHEN medication like '%plaquenil%'
OR medication like '%hydroxychloroquine%'
THEN 'Plaquenil'
ELSE '' END )
+ CHAR(13)+CHAR(10) +
MAX(
CASE
WHEN medication like 'caduet'
THEN 'HTN+Chol E78.0,I10'
ELSE '' END )
from prescriptions
where patientid = @contactid
and status = 'Active') as 'Conditions',
(SELECT
CASE WHEN EXISTS
(select orderid from orders
where prescriptionid in
--Find SCL Rx in last 12mos from today
(select prescriptionid from prescriptions
where patientid = @ContactID
and prescriptiontypeid = '953'
and rxdate between DATEADD(MONTH, -18, GETDATE()) and GETDATE()))
THEN '' --Blank. Had Rx and bought
WHEN EXISTS
(select orderid from orders
where
EXISTS --Find an actual Rx
(select prescriptionid from prescriptions
where patientid = @ContactID
and prescriptiontypeid = '953'
and rxdate between DATEADD(MONTH, -18, GETDATE()) and GETDATE())
AND prescriptionid not in
--Find SCL Rx in last 12mos from today
(select prescriptionid from prescriptions
where patientid = @ContactID
and prescriptiontypeid = '953'
and rxdate between DATEADD(MONTH, -18, GETDATE()) and GETDATE()))
THEN 'YES' --Has Rx, No CL Order, YES UPP REFIT
WHEN NOT EXISTS
(select prescriptionid from prescriptions
where patientid = @ContactID
and prescriptiontypeid = '953'
and rxdate between DATEADD(MONTH, -18, GETDATE()) and GETDATE())
THEN '' --Blank. No RX in last 12 mos
ELSE 'ERROR'
END) AS 'UPP',
CONCAT([title],' ',[firstname],' ',[middlename],' ',[lastname],' ',[suffix]) as 'fullname',
(select top 1 CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + convert(varchar(5), start_time, 108)
from calendar
where CONVERT(varchar(10), GETDATE(), 101) = CONVERT(varchar(10), calendar_date, 101)
and subjectid in (249,250,265,291,297,300,301,302,308,555,650,652,653,654,659,744,10177)
and patientid = @ContactID
order by start_time desc) as 'DayAptTime',
(select top 1 convert(varchar(5), start_time, 108)
from calendar
where CONVERT(varchar(10), GETDATE(), 101) = CONVERT(varchar(10), calendar_date, 101)
and subjectid in (249,250,297,300,301,308,652,653,10177)
and patientid = @ContactID
order by start_time desc) as 'TTIME',
--[dbo].[fn_GetPatientApptTime](contactid) as 'TTIME',
case
when custom_field_11 between '-6' and '-999' THEN 'D'
when custom_field_11 between '-5' and '-3' THEN 'C'
when custom_field_11 between '-2' and '5' THEN 'B'
when custom_field_11 > '5' THEN 'A'
ELSE '' END as 'Grade'
FROM [dbo].[contacts]
WHERE contactid = @ContactID
--Deliverables:
--Today,Today1,Today2 return today's date
--DayAptTime returns today + appointment time today
--Grade - Historical value of patient
--TTIME -
--UPP - Yes if they buy elsewhere, NO if they buy from us
--Conditions - Checks medications and returns Diabetes, Hypertension, and/or Cholesterol along with ICD10 Code
--ContactID
--ContactID1
--ContactID2
--fullname returns Title, First, Middle, Last, Suffix as one string
--title
--firstname
--MI
--lastname
--nickname
--address1
--address2
--city
--zip
--workphone
--ext - work phone extension
--EmailAddress
--homephone
--cellphone
--suffix
--guardian
--occupation
--PCP
--Specialist
--DOB
--PreferredContact - returns Cellphone, Home phone, Work, E-Mail based off profile preferred contact method
--ssn
--AMT - returns date of last Advanced Testing
--Age
--APT_DOC - returns doctor they are seeing
--NextApt
--ISAPTAFTER5 - X for after5pm to place on route slip
--ISAPTTRIAGE - X for urgent care to place on route slip
--ISAPTREFRACT - X for Refraction to place on route slip
--ISAPTNEWCOMP - X for New yearly to place on route slip
--ISAPTESTCOMP - X for Est yearly to place on route slip
--PrimaryMedicalInsurance - returns ins name
--SecondaryMedicalInsurance - returns ins name
--PrimaryRoutineInsurance - returns ins name
--SecondaryRoutineInsurance - returns ins name