A number of standardized database views are provided to "join" the various tables and dictionaries, and to filter out certain results in order to simplify reporting.
The process of developing standardized views and functions is still in progress. At this point the views provided are specific to Viral Load data reporting. However, the SQL in these views is very useful to review in order to understand the data within the tables. It is also useful to read the Data Structure Flexibility and Data Extraction and Sending data to the LDR pages for the detailed explanations on those pages.
You will notice that the names of the functions specify a generic name of Vendor1. There is a possibility that data providers send data with slightly different structures or codes (LIMSPanelCode, LIMSObservationcode) which would require utilizing queries that accommodate those and then all the viral load data would need unioned or other otherwise appended together for overall reporting.
Views
viewVL_Info - This query flattens out the extra Viral Load registration information from one row per data element into one column per extra data element for each RequestID.
viewVL_Result - This query extracts only the Viral Load related results and flattens the related results from rows to columns.
viewVL_PCR - This view is currently not used but it abstracts the PCR related data from the database from rows to columns. It may need some refining when it starts to be used more and more validation is done.
Functions
These functions are similar to views in that they return a table. Their purpose is to speed up queries by passing in a date range to limit the data to be returned. They all use the base views viewVL_Info and viewVL_Result to find the actual viral load related data after the inner select limits the requests to be returned according to the parameters passed into the function.
These queries first find all requests based on the column indicated for the data range specified. Then that list is joined into the viewVL_Info and viewVL_Results views to get the Viral Load registration and results data for those requests.
getVL_Vendor1_AnalysisDatetime (StartDate, EndDate)
getVL_Vendor1_DateTimeStamp (StartDate, EndDate) - It should be noted that this is the record's time stamp in the database and is used to find records changed (usually after a specific date). This means the query looks for any RequestIDs that have a DateTimeStamp within the range for EITHER the Requests or the LabResults table.
getVL_Vendor1_RegisteredDatetime (StartDate, EndDate)