Untitled

SELECT distinct BD.testname
--     WorkOrder_View.orderid,
--     TR.firstname    AS Name,                -- name
--     TR.dateofbirth  AS DateOfBirth,         -- birth_date
--     TR.genderid     AS GenderId,            -- gender
--     TR.nationalid   AS NationalId,          -- national ID number
--     ET.description  AS Ethnicity,           -- ethnicity
--     V.doctorname    AS DoctorName,          -- doctor's name
--     WorkOrder_View.orderdetailid,           -- order detail ID
--     WorkOrder_View.sampleno,                -- barcode
--     WorkOrder_View.testname,                -- test name
--     BD.addedby,                             -- added by ID
--     BD.addeddatetime,                       -- added date and time
--     BD.updatedby,                           -- updated by ID
--     BD.updateddatetime,                     -- updated date and time
--     BD.samplecollectedby,                   -- collected by ID
--     WorkOrder_View.samplecollecteddatetime, -- collected date and time
--     dispatchbyid,                           -- dispatched by ID
--     dispatchdatetime,                       -- dispatched date and time
--     receivedbyid,                           -- received 1 by ID (not in use)
--     receiveddatetime,                       -- received 1 date and time (not in use)
--     finalizedby,                            -- validated / completed / finalized by ID
--     WorkOrder_View.finalizeddate,           -- validated / completed / finalized date and time
--     BD.samplerejectedby,                    -- rejected by
--     BD.samplerejectiondatetime,             -- rejected date and time
--     WorkOrder_View.patientsourceid,         -- patient source ID
--     WorkOrder_View.institution,             -- institution
--     WorkOrder_View.ward,                    -- ward
--     teststatus,                             -- level status
--     samplestatusno,                         -- sample status number
--     emailsentdatetime,                      -- email sent date and time
--     BD.samplerejectionremark,               -- rejected remark/reason for rejected
--     BD.rejectInformTo,                       -- inform to
--     WorkOrder_View.patientid
FROM [HKL].[dbo].[WorkOrder_View]
    LEFT JOIN HKL.dbo.trn_registration TR ON TR.id=WorkOrder_View.patientid
    INNER JOIN HKL.dbo.trn_registrationdetails TRD ON TRD.id=TR.id
    LEFT JOIN HKL.dbo.mst_Ethnicity ET ON ET.ethnicityid=TRD.ethnicityid
    FULL OUTER JOIN HKL.dbo.trn_pathradotherorderbookingdetails BD ON BD.orderdetailid=WorkOrder_View.orderdetailid
    LEFT JOIN trn_visit V ON V.patientid=WorkOrder_View.patientid AND V.date=BD.addeddatetime
WHERE gridsubgroupname = 'Immunoserology'
ORDER BY testname ASC
--     AND ((samplestatusno = '4' AND teststatus = '4' AND BD.finalizeddate IS NOT NULL)  -- completed
--              OR (samplestatusno = '3' AND teststatus IS NULL))  -- rejected
--     AND WorkOrder_View.testname NOT IN ('PNEUMOCYCTIS JIROVECI IF')
--     AND WorkOrder_View.sampleno='3103898'
-- ORDER BY addeddatetime