TABLE: HV_LABGEN_ORDERED_TESTS_CV2

select 'Clearview'      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