Precision Queries

TABLE: HV_LABGEN_ACC_ICD10_CODES_PL
SELECT DISTINCT
       'Precision'  AS SOURCE_CLINIC,
       CONCAT(b.accession) AS ACCESSION,
       CONCAT(c.diag)      AS ICD10_CODE
FROM   orddiag      b,
       orddiag_diag c
WHERE  b.test <> '@@@@'
AND    c.orddiag_recnum = b.recnum


TABLE: HV_LABGEN_CLIENTS_PL
select 'Precision'  AS SOURCE_CLINIC,
-- c.NUM + 0 AS NUM,
-- c.COPIES + 0 AS COPIES,
-- c.DRCOPY + 0 AS DRCOPY,
-- c.BILLDR + 0 AS BILLDR,
-- c.BILLPAT + 0 AS BILLPAT,
-- c.DISCOUNT + 0 AS DISCOUNT,
-- c.SALESGROUP + 0 AS SALESGROUP,
-- c.SALESGRP2 + 0 AS SALESGRP2,
-- c.SALESGRP3 + 0 AS SALESGRP3,
-- c.SALESGRP4 + 0 AS SALESGRP4,
-- c.ROUTE + 0 AS ROUTE,
-- c.BILLTOCLT + 0 AS BILLTOCLT,
c.RECNUM + 0 AS RECNUM,
concat(c.NAME) AS NAME,
concat(c.TITLE) AS TITLE,
-- concat(c.ADDRS) AS ADDRS,
-- concat(c.CITY) AS CITY,
-- concat(c.STATE) AS STATE,
-- concat(c.ZIP) AS ZIP,
-- concat(c.COMPANY) AS COMPANY,
-- concat(c.REMREP) AS REMREP,
-- concat(c.PRIVILEGE) AS PRIVILEGE,
-- concat(c.PROVIDER) AS PROVIDER,
-- concat(c.DRTYPE) AS DRTYPE,
-- concat(c.TEL) AS TEL,
-- concat(c.CONTACT) AS CONTACT,
-- concat(c.BILLPROF) AS BILLPROF,
-- concat(c.ADDR2) AS ADDR2,
-- concat(c.CITYSTATE) AS CITYSTATE,
-- concat(c.BILLZIP) AS BILLZIP,
-- concat(c.PROVIDER2) AS PROVIDER2,
-- concat(c.ASSIGN) AS ASSIGN,
-- concat(c.SPECPRICE) AS SPECPRICE,
-- concat(c.COMPTEST) AS COMPTEST,
-- concat(c.INACTIVE_) AS INACTIVE_,
-- concat(c.OKTOBILL) AS OKTOBILL,
-- concat(c.DIALYSIS) AS DIALYSIS,
-- concat(c.ALLBILLTYPES) AS ALLBILLTYPES,
-- date(c.LSTPAYDT) AS LSTPAYDT,
concat(c2.npi)  AS NPI
FROM            client          c
LEFT JOIN       cltext2         c2      ON      c2.recnum = c.recnum


TABLE: HV_LABGEN_INSURANCE_PL
select 'Precision'  AS SOURCE_CLINIC,
a.RECNUM + 0 AS RECNUM,
-- a.BILLSCH + 0 AS BILLSCH,
-- a.FORMUSED + 0 AS FORMUSED,
a.TESTCODE + 0 AS TESTCODE,
concat(a.CODE) AS CODE,
concat(a.NAME) AS NAME,
concat(a.ADDR1) AS ADDR1,
concat(a.ADDR2) AS ADDR2,
-- concat(a.TEL) AS TEL,
-- concat(a.PROVIDER) AS PROVIDER,
-- concat(a.SUPPCODE) AS SUPPCODE,
-- concat(a.MAGNETIC) AS MAGNETIC,
-- concat(a.DISKNUM) AS DISKNUM,
-- concat(a.LASTCLAIM) AS LASTCLAIM,
-- concat(a.CHKRESULT) AS CHKRESULT,
-- concat(a.LOGINID2) AS LOGINID2,
-- concat(a.LOGINPASS2) AS LOGINPASS2,
-- concat(a.LOGINID) AS LOGINID,
-- concat(a.LOGINPASS) AS LOGINPASS,
-- concat(a.REMTEL) AS REMTEL,
-- concat(a.PAYTOLAB) AS PAYTOLAB,
concat(a.BILLSTATE) AS BILLSTATE,
-- concat(a.PRIORAUTH) AS PRIORAUTH,
-- concat(a.BOX23) AS BOX23,
-- concat(a.LINEITEM) AS LINEITEM,
-- concat(a.BOX33B) AS BOX33B,
-- concat(a.BOX17Q) AS BOX17Q,
-- concat(a.BOX24H) AS BOX24H,
-- concat(a.OVERALLOWED) AS OVERALLOWED,
-- concat(a.BOX24B) AS BOX24B,
-- concat(a.BOX24J) AS BOX24J,
-- concat(a.BOX32B) AS BOX32B,
-- concat(a.BOX31) AS BOX31,
-- concat(a.BOX10D) AS BOX10D,
concat(a.LONGNAME) AS LONGNAME,
-- concat(a.BOX17A) AS BOX17A,
-- concat(a.PRIMARYPAYER) AS PRIMARYPAYER,
-- concat(a.BILLSOLO) AS BILLSOLO,
-- concat(a.BOX24JNPI) AS BOX24JNPI,
-- date(a.LASTTRANS) AS LASTTRANS,
-- date(a.ICD10STARTDATE) AS ICD10STARTDATE,
-- date(a.BSSTARTDATE) AS BSSTARTDATE,
CONCAT(b.zip) AS ZIP_CODE
from insurance a
LEFT JOIN insext b on b.recnum = a.recnum


TABLE: HV_LABGEN_ORDERED_TESTS_PL
select 'Precision'      AS SOURCE_CLINIC,
       concat(d.indtests)     AS INDTESTS,
--       concat(d.testflags)    AS TESTFLAGS,
       concat(d.results)      AS RESULTS,
       d.order_recnum + 0     AS ORDER_RECNUM,
       d.seq + 0              AS SEQ,
       d.recnum + 0           AS RECNUM,
       DATE_ADD(e.test_date_time, INTERVAL 0 DAY) AS TEST_COMPLETION_DATE,
       CONCAT(e.technician)   AS TEST_COMPLETION_TECH,
       DATE_ADD(f.test_date_time, INTERVAL 0 DAY) AS TEST_STARTED_DATE,
       CONCAT(f.technician)   AS TEST_STARTED_TECH
from (select order_recnum,
             indtests,
--             min(testflags) as testflags,
             min(results)   as results,
             min(seq)       as seq,
             min(recnum)    as recnum
      from (select order_indtests.indtests,
--                   order_indtests.testflags,
                   order_indtests.results,
                   order_indtests.order_recnum,
                   order_indtests.seq,
                   order_indtests.recnum
            from   order_indtests,
                   order_
            where  order_indtests.indtests is not null
            and    order_.recnum = order_indtests.order_recnum
            and    order_.entrydt >= (now() - interval 3 month)
            UNION ALL
            select ordext_indtests.indtests,
--                   ordext_indtests.testflags,
                   ordext_indtests.results,
                   order_.recnum,
                   ordext_indtests.seq,
                   ordext_indtests.recnum
            from   ordext_indtests,
                   ordext,
                   order_
            where  order_.extrec = ordext.recnum
            and    ordext.recnum = ordext_indtests.ordext_recnum
            and    ordext_indtests.indtests is not null
            and    order_.entrydt >= (now() - interval 3 month)) xx
      group by order_recnum, indtests) d
LEFT JOIN
(
select c.recnum             AS ORDER_RECNUM,
       MAX(a.tech)          AS TECHNICIAN,
       b.tests              AS TEST_CODE,
       MAX(STR_TO_DATE(CONCAT(DATE_FORMAT(resdate,'%Y-%m-%d'),' ',
            CASE restime WHEN NULL THEN NULL
            ELSE CONCAT(SUBSTRING(restime,1,2),':',SUBSTRING(restime,3,2))
            END), '%Y-%m-%d %T')) AS TEST_DATE_TIME
from   ordext2       a,
       ordext2_tests b,
       order_        c
where  a.isverify       = 'Y'
and    b.ordext2_recnum = a.recnum
and    c.accession      = a.accession
and    c.entrydt >= (now() - interval 3 month)
group
by     c.recnum,
       b.tests
) e on e.order_recnum = d.order_recnum and e.test_code = d.indtests
LEFT JOIN
(
select c.recnum             AS ORDER_RECNUM,
       MAX(a.tech)          AS TECHNICIAN,
       b.tests              AS TEST_CODE,
       MAX(STR_TO_DATE(CONCAT(DATE_FORMAT(resdate,'%Y-%m-%d'),' ',
            CASE restime WHEN NULL THEN NULL
            ELSE CONCAT(SUBSTRING(restime,1,2),':',SUBSTRING(restime,3,2))
            END), '%Y-%m-%d %T')) AS TEST_DATE_TIME
from   ordext2       a,
       ordext2_tests b,
       order_        c
where  ifnull(a.isverify,'X') <> 'Y'
and    b.ordext2_recnum = a.recnum
and    c.accession      = a.accession
and    c.entrydt >= (now() - interval 3 month)
group
by     c.recnum,
       b.tests
) f on f.order_recnum = d.order_recnum and f.test_code = d.indtests


TABLE: HV_LABGEN_ORDERS_PL
select 'Precision'  AS SOURCE_CLINIC,
a.CLIENT1 + 0 AS CLIENT_RECNUM,
a.CLIENT2 + 0 AS PHYSICIAN_RECNUM,
a.BILLTYPE + 0 AS BILLTYPE,
a.PATPTR + 0 AS PATIENT_RECNUM,
a.RECNUM + 0 AS RECNUM,
CONCAT(a.ACCESSION) AS ACCESSION,
CONCAT(a.BODYSITE) AS BODYSITE,
CONCAT(a.COMMENT1) AS COMMENT1,
CONCAT(a.COMMENT2) AS COMMENT2,
CONCAT(a.PENDING) AS PENDING,
date (a.ENTRYDT) AS ENTRYDT,
date (a.COLLECTDT) AS COLLECTDT,
date (a.REPDATE) AS REPDATE,
IFNULL(MAX(e.userid),MAX(b.userid))  AS ORDER_ENTERED_BY,
concat(c.name) AS TEST_TYPE_NAME,
STR_TO_DATE(CONCAT(DATE_FORMAT(entrydt,'%Y-%m-%d'),' ', IFNULL(entrytim,'00:00')), '%Y-%m-%d %T') AS ORDER_DATE_TIME,
STR_TO_DATE(CONCAT(DATE_FORMAT(collectdt,'%Y-%m-%d'),' ', IFNULL(CONCAT(left(collecttime,2),':',right(collecttime,2)),'00:00')), '%Y-%m-%d %T') AS COLLECTED_DATE_TIME,
MAX(STR_TO_DATE(CONCAT(DATE_FORMAT(d.samplrePLdt,'%Y-%m-%d'),' ', IFNULL(d.samplrePLtm,'00:00')), '%Y-%m-%d %T')) AS RECEIVED_DATE_TIME,
MIN(date(e.date_)) AS ENTERED_DATE,
d.travelchg + 0 AS TRAVEL_CHARGES,
CONCAT(ps.billtype) AS SKILLED_NONSKILLED_DESIG,
COUNT(DISTINCT att.filepath) AS NUMBER_OF_ATTACHMENTS
FROM order_                                     a
LEFT JOIN       auditlog                b       ON  b.accession = a.accession
                                AND b.comment_ like 'Order Entered by%'
LEFT JOIN       tube            c       ON c.code               = a.bodysite
LEFT JOIN       ordext3         d       ON  d.acc               = a.accession
LEFT JOIN   auditlog    e   ON  e.accession = a.accession and (e.comment_ like 'Order Entered by%' or e.comment_ like 'Remote Order%Entered by%')
LEFT JOIN patient p on p.recnum = a.patptr
LEFT JOIN patstat ps on ps.patid = p.chart and a.collectdt between ps.stdate and ps.endate
LEFT JOIN attachment att on att.accession = a.accession
group by
'Precision',
a.CLIENT1 + 0,
a.CLIENT2 + 0,
a.BILLTYPE + 0,
a.PATPTR + 0,
a.RECNUM + 0,
CONCAT(a.ACCESSION),
CONCAT(a.BODYSITE),
CONCAT(a.COMMENT1),
CONCAT(a.COMMENT2),
CONCAT(a.PENDING),
date (a.ENTRYDT),
date (a.COLLECTDT),
date (a.REPDATE),
concat(c.name),
STR_TO_DATE(CONCAT(DATE_FORMAT(entrydt,'%Y-%m-%d'),' ', IFNULL(entrytim,'00:00')), '%Y-%m-%d %T'),
STR_TO_DATE(CONCAT(DATE_FORMAT(collectdt,'%Y-%m-%d'),' ', IFNULL(CONCAT(left(collecttime,2),':',right(collecttime,2)),'00:00')), '%Y-%m-%d %T'),
d.travelchg,
ps.billtype


TABLE: HV_LABGEN_PATIENTS_PL
select 'Precision'  AS SOURCE_CLINIC,
a.RECNUM + 0 AS RECNUM,
concat(a.CHART) AS CHART,
concat(a.LASTN) AS LASTN,
concat(a.FIRSTN) AS FIRSTN,
concat(a.MIDDLE) AS MIDDLE,
concat(a.SEX) AS SEX,
concat(a.PHONE) AS PHONE,
concat(a.SSNUM) AS SSNUM,
date(a.DOB) AS DOB,
concat(a.ADDRESS1) AS ADDRESS1,
concat(a.ADDRESS2) AS ADDRESS2,
concat(a.CITY) AS CITY,
concat(a.STATE) AS STATE,
concat(a.ZIP) AS ZIP,
concat(a.RELATION) AS RELATION,
concat(a.INSID1) AS INSID1,
concat(a.GROUP1) AS GROUP1,
CASE a.relation WHEN 'SE' THEN a.LASTN     ELSE a.ILNAME  END AS ILNAME,
CASE a.relation WHEN 'SE' THEN a.FIRSTN    ELSE a.IFNAME  END AS IFNAME,
CASE a.relation WHEN 'SE' THEN a.middle    ELSE a.IMIDDLE END AS IMIDDLE,
CASE a.relation WHEN 'SE' THEN a.sex       ELSE b.isex    END AS ISEX,
CASE a.relation WHEN 'SE' THEN a.phone     ELSE b.iphone  END AS IPHONE,
CASE a.relation WHEN 'SE' THEN a.ssnum     ELSE b.issnum  END AS ISSNUM,
CASE a.relation WHEN 'SE' THEN a.dob       ELSE b.idob    END AS IDOB_NEW,
CASE a.relation WHEN 'SE' THEN a.ADDRESS1  ELSE a.IADDR1  END AS IADDR1,
CASE a.relation WHEN 'SE' THEN a.CITY  ELSE a.IADDR2  END AS IADDR2,
CASE a.relation WHEN 'SE' THEN a.state     ELSE a.ISTATE  END AS ISTATE,
CASE a.relation WHEN 'SE' THEN a.zip       ELSE a.IZIP    END AS IZIP,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE a.IRELATION END AS IRELATION,
concat(a.INSID2)    AS INSID2,
concat(a.GROUP2)    AS GROUP2,
concat(a.INS2)      AS INS2,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.lastn  ELSE b.oilname  END END  AS SECONDARY_LAST_NAME,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.firstn ELSE b.oifname  END END  AS SECONDARY_FIRST_NAME,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.middle ELSE b.oimiddle END END  AS SECONDARY_MIDDLE,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.sex    ELSE b.oisex    END END  AS SECONDARY_SEX,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.phone  ELSE b.oiphone  END END  AS SECONDARY_PHONE,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.ssnum  ELSE b.oissnum  END END  AS SECONDARY_SSNUM,
CASE concat(IFNULL(insid2,'!'), ifnull(group2,'!'),ifnull(ins2,'!')) WHEN '!!!' THEN NULL ELSE CASE a.irelation WHEN 'SE' THEN a.dob    ELSE b.oidob    END END  AS SECONDARY_DOB_NEW
FROM patient a
left join insured b on b.recnum = a.recnum


TABLE: HV_LABGEN_REPORT_DATES_PL
select 'Precision'  AS SOURCE_CLINIC,
RECNUM + 0 AS RECNUM,
concat(ACCESSION) AS ACCESSION,
-- concat(FIRSTTIME) AS FIRSTTIME,
-- concat(FINALTIME) AS FINALTIME,
-- concat(TESTTYPE) AS TESTTYPE,
-- date(FIRSTDATE) AS FIRSTDATE,
date(FINALDATE) AS FINALDATE,
STR_TO_DATE(CONCAT(DATE_FORMAT(finaldate,'%Y-%m-%d'),' ', ifnull(finaltime,'00:00')), '%Y-%m-%d %T') AS FINAL_DATE_TIME
from reportdt
where (accession, recnum) IN
       (SELECT r.accession,
               MAX(r.recnum)
        FROM   reportdt r
        where  r.finaldate is not null
        GROUP
        BY     r.accession)


TABLE: HV_LABGEN_TESTS_PL
select 'Precision'  AS SOURCE_CLINIC,
t.DECPOS + 0 AS DECPOS,
t.WORKSHEET + 0 AS WORKSHEET,
t.WSHSEQ + 0 AS WSHSEQ,
t.VOLUME + 0 AS VOLUME,
t.RECNUM + 0 AS RECNUM,
concat(t.CODE) AS CODE,
ltrim(t.NAME) AS NAME,
concat(t.ABBR) AS ABBR,
concat(t.RESULTTYPE) AS RESULTTYPE,
concat(t.TYPE_) AS TYPE_,
concat(t.MALELOW) AS MALELOW,
concat(t.MALEHIGH) AS MALEHIGH,
concat(t.FEMLOW) AS FEMLOW,
concat(t.FEMHIGH) AS FEMHIGH,
concat(t.GENLOW) AS GENLOW,
concat(t.GENHIGH) AS GENHIGH,
concat(t.EXTRANGE) AS EXTRANGE,
concat(t.PANIPLALUE) AS PANIPLALUE,
concat(t.CALCTEST) AS CALCTEST,
concat(t.CALCFORMULA) AS CALCFORMULA,
concat(t.SIGLTSTWSH) AS SIGLTSTWSH,
concat(t.TECHID) AS TECHID,
concat(t.TIMEDONE) AS TIMEDONE,
concat(t.MSG) AS MSG,
concat(t.SEPARATEORD) AS SEPARATEORD,
concat(t.ONLINEORD) AS ONLINEORD,
concat(t.UNITS) AS UNITS,
concat(t.REFLAB) AS REFLAB,
concat(t.DEFAULTS) AS DEFAULTS,
concat(t.ALPHANOR) AS ALPHANOR,
concat(t.ALPHABNOR) AS ALPHABNOR,
concat(t.PANICLOW) AS PANICLOW,
concat(t.PANICHIGH) AS PANICHIGH,
concat(t.DEPT) AS DEPT,
concat(t.TUBETYPE) AS TUBETYPE,
concat(t.LABEL) AS LABEL,
concat(t.RFLXLOW) AS RFLXLOW,
concat(t.RFLXHI) AS RFLXHI,
concat(t.RFLXRES) AS RFLXRES,
concat(t.RFLXOTHER) AS RFLXOTHER,
concat(t.RFLXTEST) AS RFLXTEST,
concat(t.REFTSTCODE) AS REFTSTCODE,
concat(t.TIMELIMIT) AS TIMELIMIT,
concat(t.RFLXTEST2) AS RFLXTEST2,
concat(t.INACTIVE_) AS INACTIVE_,
concat(t.ELECORDER) AS ELECORDER,
concat(t.SPECIESSPEC) AS SPECIESSPEC,
concat(t.RFLXINSIDE) AS RFLXINSIDE,
concat(t.PREFIX) AS PREFIX,
concat(t.BACTISRC) AS BACTISRC,
concat(t.DELTEST) AS DELTEST,
concat(t.METSCONSISTENT) AS METSCONSISTENT,
concat(t.METHODOLOGY) AS METHODOLOGY,
concat(t.EXCLUDEFIXEDCOST) AS EXCLUDEFIXEDCOST,
concat(t.TEMPERATURE) AS TEMPERATURE,
concat(t.MISSINGINFO) AS MISSINGINFO,
concat(t.LONGNAME) AS LONGNAME,
concat(t.FILLER2) AS FILLER2,
date(t.LASTORDERDATE) AS LASTORDERDATE,
date(t.FIRSTORDERDATE) AS FIRSTORDERDATE,
concat(d.name) AS DEPARTMENT_NAME,
concat(b.labname) AS LABNAME,
concat(p.cptcode) AS CPT_CODE
FROM test				t
LEFT JOIN	department	d	ON d.recnum	= t.dept
LEFT JOIN	reflab	b	ON  b.labnum	= t.reflab
LEFT JOIN   (select testcode, max(cptcode) cptcode
             from newprices
             where 1=1
             and cptcode is not null
             and type_ = 'T'
             and testcode <> cptcode
             group by testcode) p                          ON p.testcode = t.code


TABLE: HV_LABGEN_ORDERED_TESTS_PL2

select 'Precision'      AS SOURCE_CLINIC,
       concat(d.indtests)     AS INDTESTS,
--       concat(d.testflags)    AS TESTFLAGS,
       concat(d.results)      AS RESULTS,
       d.order_recnum + 0     AS ORDER_RECNUM,
       d.seq + 0              AS SEQ,
       d.recnum + 0           AS RECNUM,
       DATE_ADD(e.test_date_time, INTERVAL 0 DAY) AS TEST_COMPLETION_DATE,
       CONCAT(e.technician)   AS TEST_COMPLETION_TECH,
       DATE_ADD(f.test_date_time, INTERVAL 0 DAY) AS TEST_STARTED_DATE,
       CONCAT(f.technician)   AS TEST_STARTED_TECH
from (select order_recnum,
             indtests,
--             min(testflags) as testflags,
             min(results)   as results,
             min(seq)       as seq,
             min(recnum)    as recnum
      from (select order_indtests.indtests,
--                   order_indtests.testflags,
                   order_indtests.results,
                   order_indtests.order_recnum,
                   order_indtests.seq,
                   order_indtests.recnum
            from   order_indtests,
                   order_
            where  order_indtests.indtests is not null
            and    order_.recnum = order_indtests.order_recnum
            and    order_.entrydt between str_to_date('%%FROM_DATE', '%M %d %Y') and str_to_date('%%TO_DATE', '%M %d %Y')
            UNION ALL
            select ordext_indtests.indtests,
--                 ordext_indtests.testflags,
                   ordext_indtests.results,
                   order_.recnum,
                   ordext_indtests.seq,
                   ordext_indtests.recnum
            from   ordext_indtests,
                   ordext,
                   order_
            where  order_.extrec = ordext.recnum
            and    ordext.recnum = ordext_indtests.ordext_recnum
            and    ordext_indtests.indtests is not null
            and    order_.entrydt between str_to_date('%%FROM_DATE', '%M %d %Y') and str_to_date('%%TO_DATE', '%M %d %Y')) xx
      group by order_recnum, indtests) d
LEFT JOIN
(
select c.recnum             AS ORDER_RECNUM,
       MAX(a.tech)          AS TECHNICIAN,
       b.tests              AS TEST_CODE,
       MAX(STR_TO_DATE(CONCAT(DATE_FORMAT(resdate,'%Y-%m-%d'),' ',
            CASE restime WHEN NULL THEN NULL
            ELSE CONCAT(SUBSTRING(restime,1,2),':',SUBSTRING(restime,3,2))
            END), '%Y-%m-%d %T')) AS TEST_DATE_TIME
from   ordext2       a,
       ordext2_tests b,
       order_        c
where  a.isverify       = 'Y'
and    b.ordext2_recnum = a.recnum
and    c.accession      = a.accession
and    c.entrydt between str_to_date('%%FROM_DATE', '%M %d %Y') and str_to_date('%%TO_DATE', '%M %d %Y')
group
by     c.recnum,
       b.tests
) e on e.order_recnum = d.order_recnum and e.test_code = d.indtests
LEFT JOIN
(
select c.recnum             AS ORDER_RECNUM,
       MAX(a.tech)          AS TECHNICIAN,
       b.tests              AS TEST_CODE,
       MAX(STR_TO_DATE(CONCAT(DATE_FORMAT(resdate,'%Y-%m-%d'),' ',
            CASE restime WHEN NULL THEN NULL
            ELSE CONCAT(SUBSTRING(restime,1,2),':',SUBSTRING(restime,3,2))
            END), '%Y-%m-%d %T')) AS TEST_DATE_TIME
from   ordext2       a,
       ordext2_tests b,
       order_        c
where  ifnull(a.isverify,'X') <> 'Y'
and    b.ordext2_recnum = a.recnum
and    c.accession      = a.accession
and    c.entrydt between str_to_date('%%FROM_DATE', '%M %d %Y') and str_to_date('%%TO_DATE', '%M %d %Y')
group
by     c.recnum,
       b.tests
) f on f.order_recnum = d.order_recnum and f.test_code = d.indtests