LVS-95XX Useful Queries and examples

[Omron Forums Support]

Query on the Reference field and Date (last 30 days) sorted by Date (Newest to Oldest)
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where Reference="9580-KLS" AND LclTime >= #2019-10-02 16:26:38# Order By LclTime DESC

Query on the DecodedText field and Date (last 30 days) sorted by Date newest to oldest
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="RGT23244037" AND LclTime >= #2019-10-02 16:31:55# Order By LclTime DESC

Query (DecodedText field and Date range) sorted by Date (Newest to Oldest)
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01 16:31:55# AND #2019-01-01 16:31:55# Order By LclTime DESC
Or sorted by ReportID (Descending)

Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01 16:31:55# AND #2019-01-01 16:31:55# Order By ReportID DESC

This works also if you don’t want to specify the time of day.
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01# AND #2019-01-01# Order By ReportID DESC

Query covering data from November 1, 2019 back to November 1, 2018 is:
Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText= "12345678" and LclTime>=#2018-11-01# Order By ReportID

JOINING TWO DATA TABLES
Report joining two data tables for filtering by Reference (Report table) and Additional reference (Report.Data table)
Select Reports.ReportID, SectorID, LclTime, Reference, ReportData.ParameterValue as AddtionalReference, OverallGrade,DecodedText From Reports INNER JOIN ReportData ON Reports.ReportID = ReportData.ReportID Where ReportData.ParameterName = "Additional reference" AND ReportData.ParameterValue="xxx" AND Reports.Reference ="xxx" Order By Reports.ReportID