Question regarding WHERE statement for yesterday's date

[former_member1110461]

I have received this code from a fellow peer of our EHR vendor. We utilize the same system setup and we are both attempting to pull a medication administration report based on yesterday's administrations. The code I received gives me an error at the WHERE statement referencing the calendar_gregorian_yesterday. I am a novice when it comes to writing SQL commands so I'm unsure how I need to correct this statement to make it work and not give me an error. I have searched the internet to find the answer and have tested numerous command changes but nothing is working. Any help would be appreciated.

SELECT DISTINCT

CASE

WHEN "ns_medication".mlx_arid = 1

THEN

'Facility Name'

END AS "Facility Name",

' ' AS "Message Type - Type Code",

' ' AS "File Generated Date-Time",

"ns_medication".mlx_acctnum AS "Patient Encounter Number",

"visit".visit_mr_num AS "Patient ID",

"visit".visit_name AS "Patient Name",

"visit".visit_gender AS "Gender",

to_char (visit_date_of_birth, 'yyyymmdd') AS "Date of Birth",

to_char ("ip_visit_1".ipv1_ad_date + "ip_visit_1".ipv1_ad_time,

'yyyy-mm-dd HH24:MI:SS') AS "Admission Date-Time",

to_char ("ip_visit_1".ipv1_dis_date + "ip_visit_1".ipv1_dis_time,

'yyyy-mm-dd HH24:MI:SS') AS "Discharge Date-Time",

' ' AS "Patient Census Location",

' ' AS "Patient Census Room",

' ' AS "Patient Census Bed",

"ip_pharmacy".ipa_order AS "Prescription Number (Pyxis Order Number)",

CASE

WHEN ' ' = ' '

THEN

'1'

END AS "Administration Sequence Number",

CASE

WHEN ' ' = ' '

THEN

'1'

END AS "Drug Ingredient Number",

' ' AS "Order Placer Number",

to_char("ns_medication".mlx_actual_dt + "ns_medication".mlx_actual_tm, 'yyyy-mm-dd HH24:MI:SS') AS "Date-Time Start of Administration",

' ' AS "Date-Time End of Administration",

"ar_charge".arch_chgno AS "Administration Code Identifier (Pyxis MedID)",

"ivm1_table".ivm1tb_desc AS "Administered Drug Description",

CASE

WHEN ("ns_medication".mlx_dosageqty = '0')THEN

trim(to_char(ipa_ipbqty[array_position("ip_pharmacy".ipa_ipbchgnum, "ar_charge".arch_chgno)], '999999'))

ELSE "ns_medication".mlx_dosageqty

END

AS "Administered Amount",

CASE

WHEN ("ip_pharmacy".ipa_unit = '')THEN

"ip_pharmacy".ipa_ipbunit[array_position("ip_pharmacy".ipa_ipbchgnum, "ar_charge".arch_chgno)]

ELSE "ip_pharmacy".ipa_unit

END

AS "Administered Units",

' ' AS "Administered Rate",

' ' AS "Administered Rate Unit",

' ' AS "Administered Strength",

' ' AS "Administered Strength Units Text",

CASE

WHEN "ip_pharmacy".ipa_route = 'IVSP' THEN 'IV'

WHEN "ip_pharmacy".ipa_route = 'IVPB' THEN 'IV'

WHEN "ip_pharmacy".ipa_route = 'IV/IM' THEN 'IV'

WHEN "ip_pharmacy".ipa_route = 'IV' THEN 'IV'

WHEN "ip_pharmacy".ipa_route = 'SUB Q' THEN 'SC'

WHEN "ip_pharmacy".ipa_route = 'BOTH EYES' THEN 'OP'

WHEN "ip_pharmacy".ipa_route = 'NASAL' THEN 'IN'

WHEN "ip_pharmacy".ipa_route = 'ORAL' THEN 'PO'

WHEN "ip_pharmacy".ipa_route = 'EPIDURAL' THEN 'EP'

WHEN "ip_pharmacy".ipa_route = 'IM/IV' THEN 'IM'

WHEN "ip_pharmacy".ipa_route = 'LEFT EYE' THEN 'OP'

WHEN "ip_pharmacy".ipa_route = 'TRANSD' THEN 'TD'

WHEN "ip_pharmacy".ipa_route = 'PO/PT' THEN 'PO'

WHEN "ip_pharmacy".ipa_route = 'LT EYE' THEN 'OP'

WHEN "ip_pharmacy".ipa_route = 'RIGHT EYE' THEN 'OP'

WHEN "ip_pharmacy".ipa_route = 'PO' THEN 'PO'

WHEN "ip_pharmacy".ipa_route = 'IM' THEN 'IM'

WHEN "ip_pharmacy".ipa_route = 'ID' THEN 'ID'

WHEN "ip_pharmacy".ipa_route = 'PR' THEN 'PR'

WHEN "ip_pharmacy".ipa_route = 'INH' THEN 'IH'

WHEN "ip_pharmacy".ipa_route = 'IVP' THEN 'IV'

WHEN "ip_pharmacy".ipa_route = 'TOP' THEN 'TP'

END AS "Route of Administration",

CASE

WHEN "ns_medication".mlx_given_omit = 'G' THEN 'CP'

WHEN "ns_medication".mlx_given_omit = 'O' THEN 'NA'

END AS "Completion Status",

' ' AS "Administering Provider ID",

"ns_medication".mlx_init AS "Administering Provider Name"

FROM

((public.ar_charge "ar_charge"

INNER JOIN

public.ip_pharmacy "ip_pharmacy"

ON ("ar_charge".arch_visit_key = "ip_pharmacy".ipa_num) AND ("ar_charge".arch_chgno = "ip_pharmacy".ipa_chg OR "ar_charge".arch_chgno = ANY("ip_pharmacy".ipa_ipbchgnum)) AND ("ar_charge".arch_arid = "ip_pharmacy".ipa_arid))

INNER JOIN

public.ivm3_table "ivm3_table"

ON ("ar_charge".arch_chgno = "ivm3_table".ivm3tb_num) AND ("ar_charge".arch_arid = "ivm3_table".ivm3tb_arid))

INNER JOIN

public.ivm1_table "ivm1_table"

ON ("ivm1_table".ivm1tb_num = "ar_charge".arch_chgno) AND ("ivm1_table".ivm1tb_arid = "ar_charge".arch_arid)

INNER JOIN public.ns_medication "ns_medication"

ON("ip_pharmacy".ipa_num = "ns_medication".mlx_acctnum) AND ("ip_pharmacy".ipa_arid = "ns_medication".mlx_arid) AND ("ip_pharmacy".ipa_order = cast("ns_medication".mlx_order as numeric))

INNER JOIN public.visit "visit"

ON ("ns_medication".mlx_arid = "visit".visit_arid) AND ("ns_medication".mlx_acctnum = "visit".visit_id)

INNER JOIN public.ip_visit_1 "ip_visit_1"

ON ("visit".visit_id = "ip_visit_1".ipv1_num) AND ("visit".visit_arid = "ip_visit_1".ipv1_arid)

WHERE ("ns_medication".mlx_actual_dt = calendar_gregorian_yesterday_)

AND ("ivm3_table".ivm3tb_phndc <> '')

AND ("ns_medication".mlx_arid = 1)

[DellSC]

What type of database are you connecting to? I suspect that calendar_gregorian_yesterday_ is database-specific syntax. If I know what type of database you're using, I can probably give you the correct syntax.

[former_member990854 · Accepted solution]

Hello Karen,

SQL Query is specific to the database you are using, and the best will be to post your question on Postgresql forum, or contact your Postgresql Database Administrator.

Having said that, I quickly look at the Postgresql documentation, and a suggestion will be to use the function: current_date - 1, which will give you yesterday, so your WHERE clause will be like:

WHERE  ("ns_medication".mlx_actual_dt =  (current_date - INTERVAL '1 day') )

See the Postgresql documentation at:

https://www.postgresql.org/docs/8.3/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT