4. Format of dates - use the CONVERT SQL function :

Select TOP 10 SAMPLEDTTIME, CONVERT(CHAR(20),LC_SAMPLE.SAMPLEDTTIME,0),CONVERT(CHAR(10),SAMPLEDTTIME,101),CONVERT(CHAR(10),LC_SAMPLE.SAMPLEDTTIME,110), SAMPLENUM FROM LC_SAMPLE WHERE SAMPLEDTTIME IS NOT NULL

 

Using the format can allow you to truncate the datetime to just a date to allowing you to link LabCal SampleDtTime to the datatbl:

Select LC_SAMPLE.ID,LC_SAMPLE.SAMPLEDBY,LC_SAMPLE.SAMPLEDTTIME, LC_SAMPLETEST.VARID, Datatbl.TextValue FROM LC_SAMPLE,LC_SAMPLETEST,DATATBL WHERE LC_SAMPLE.SAMPLEDTTIME < '2008-02-05' AND LC_SAMPLE.ID=LC_SAMPLETEST.SAMPLEID and LC_SAMPLETEST.VARID = DATATBL.VARID and CONVERT(CHAR(10),LC_SAMPLE.SAMPLEDTTIME,110) = CONVERT(CHAR(10),DATATBL.DATESTAMP,110)

You can use the DATEPART Function to match up hours:

Select TOP 10 SAMPLEDTTIME, DATEPART(hour,SampleDtTime) FROM LC_SAMPLE WHERE SAMPLEDTTIME IS NOT NULL

 NOTE: Using DatePart you can match up LabCal Samples to the hourly results:

Select LC_SAMPLE.ID,LC_SAMPLE.SAMPLEDBY,LC_SAMPLE.SAMPLEDTTIME, LC_SAMPLETEST.VARID, Datatbl.TextValue FROM LC_SAMPLE,LC_SAMPLETEST,DATADDH WHERE LC_SAMPLE.SAMPLEDTTIME < '2008-02-05' AND LC_SAMPLE.ID=LC_SAMPLETEST.SAMPLEID and LC_SAMPLETEST.VARID = DATATBL.VARID and CONVERT(CHAR(10),LC_SAMPLE.SAMPLEDTTIME,110) = CONVERT(CHAR(10),DATADDH.DATESTAMP,110) AND DATEPART(hour,SAMPLEDTTIME) = DATEPART(hour,DATADDH.DATESTAMP)

 

5.  Update the StoretCodes in VARDESC table that do not have zero padding.  I.E. StoretCode is 310 instead of 00310:

update vardesc set StoretCode = '00' + StoretCode where len(StoretCode) = 3

6. Multiple left outer joins on the Sample Table.  I want to see the Sample Name, SampleNum, SampledBy, Assigned to, and Location. 

SELECT LC_SAMPLEDEF.NAME, LC_SAMPLE.SAMPLENUM,LC_PEOPLE.NAME as SAMPLEDBY,A.NAME as ASSIGNEDTO, LOCATION.LOCATION
FROM LC_SAMPLEDEF inner join
(((LC_SAMPLE left outer join Location on LC_SAMPLE.LocationId = Location.LOCID)
left outer join LC_PEOPLE ON LC_SAMPLE.SAMPLEDBY = LC_PEOPLE.ID)
LEFT OUTER JOIN LC_PEOPLE A ON LC_SAMPLE.ASSIGNEDTOID=LC_PEOPLE.ID)
on LC_SAMPLE.SAMPLEDEFID = LC_SAMPLEDEF.ID
WHERE SAMPLEDTTIME >='2008-06-01 00:00:00' AND SAMPLEDTTIME <'2008-07-01 00:00:00'

 

7. Update the SCADA Interface StartTime and/or StopTime to a default time:

Update vardesc Set StartTime = '12/30/1899 12:00:00 AM' where StartTime <> '12/30/1899 12:00:00 AM'

Update vardesc Set StopTime = '12/30/1899 11:59:00 PM' where StopTime <> '12/30/1899 11:59:00 PM'

Method #2 uses the Starttime for variable 1 as the default:

Update vardesc Set StartTime = (Select StartTime from Vardesc where VarNum = 1) where VARTYPE = 'P'

8. Update the LIMS configuration from using UD1 - UD5 to LIMS_LOC, LIMS_TEST, and LIMS_UD's:

This will copy the contents of the UD fields to the LIMS_ fields for Parameter type variables.

UPDATE vardesc
SET
 lims_loc = ud1,
 lims_test = ud2,
 lims_ud1 = ud3,
 lims_ud2 = ud4,
 lims_ud3 = ud5

WHERE vartype = 'P' and ud1 is not null and ud2 is not null and ud1 <> ' ' and ud2 <> ' '

Notice we checked for nulls and blanks. It is best to run the SELECT part of the query to ensure you do not get extraneous variables that do not need to be updated.

9. Associate a LIMS type interface with variables after upgrading from OPS SQL to Hach WIMS

This query will update all the variables using LIMS_ fields in conjunction with a Hach WIMS interface. First you will need to know the ID of the interface from OPSROOT:

SELECT interface_id, lu_id, name from g2_server

The LU_ID will tell you what Q-number and NAME will be the name used for the instance of the interface. You will need one or the other if there are multiple interfaces.

Then, in the facility database, execute the query to update the fields of appropriate variables for that interface:

UPDATE vardesc
SET
g2_interface_id = 3,
g2_enabled = -1

WHERE  vartype = 'P' and lims_loc is not null and lims_loc <> ' '

The G2_INTERFACE_ID is the value you got from the previous query, and G2_ENABLED is set to -1 to enable it.

To look at variables for this scenario, simple SQL:

select * from vardesc
WHERE vartype = 'P' and g2_interface_id = 3

10. Update your limits end date.

Changes the each variables Limit EndDate from 12/31/2011 to 12/31/2015:

UPDATE LIMITS SET LIMITS.ENDDATE = '2015-12-31' WHERE ENDDATE = '2011-12-31'

 

11. Update your G2_Interface_ID for all variables in an Area that have the SCADATAG Set:

update VARDESC set g2_Interface_id=3,vardesc.g2_enabled=-1 FROM vardesc,location where vardesc.locid=location.locid and location.areaid=1 and VARDESC.SCADATAG is not null

This will set all variables with locations that are assigned to AreaID=1 with a SCADATAG set to g2_Interface_ID 3.  You can find the AreaID by running a query (Select * FROM AREA) or by using Area Setup and viewing the AREA.ID in the lower right hand corner for the current record. 

12. Update VarNums for a variable range:

update set vardesc.varnum = (vardesc.varnum + 10) where vardesc.varnum > 330500 and vardesc.varnum < 352000

13. Move logbook entries from one logbook to a different logbook. 

update LOGBOOKENTRIES Set LBID=1 WHERE LBID = 0 AND DateStamp + TIMESTAMP NOT IN (SELECT DATESTAMP+TIMESTAMP FROM LOGBOOKENTRIES WHERE LBID=1)

NOTE: This moves the Logbook entries from Logbook 0 (zero is the default comments logbook) to logbook id 1.  To find the logbook ID, use Select * FROM LOGBOOK.  Logbook 0 will not be listed and IS ALWAYS the Comments logbook.   

14. Get number of instances where no disinfectant residual was measured but HPC was not measure (SWTP B value) from a LIMS.  This is an example of a Self-Join. 

SELECT COUNT(*) FROM (
Select H.SAMPLED_DATE, H.LOCATION_NAME,H.RESULT_VALUE HPC,FC.RESULT_VALUE F_CL2
FROM SAMP_TEST_RESULT H LEFT OUTER JOIN SAMP_TEST_RESULT FC ON H.SAMPLED_DATE=FC.SAMPLED_DATE AND FC.ANALYSIS='F_CL2' AND H.LOCATION_NAME=FC.LOCATION_NAME
WHERE H.ANALYSIS='HPC' AND H.SOURCE IN ('200') AND H.SAMPLE_TYPE = '03' AND H.TEST_STATUS='C' AND H.STATUS='C') R
WHERE F_CL2 IS NULL

15. Get the number of instances where disinfectant residual was non detect (defined in this case as a result <0.05) and HPC was not measured:

SELECT COUNT(*) FROM (
Select FC.SAMPLED_DATE, FC.LOCATION_NAME,FC.RESULT_VALUE F_CL2,H.RESULT_VALUE HPC
FROM SAMP_TEST_RESULT FC LEFT OUTER JOIN SAMP_TEST_RESULT H ON FC.SAMPLED_DATE=H.SAMPLED_DATE AND H.ANALYSIS='HPC' AND FC.LOCATION_NAME=H.LOCATION_NAME
WHERE FC.ANALYSIS='F_CL2' AND FC.SOURCE IN ('200') AND FC.SAMPLE_TYPE = '03' AND FC.TEST_STATUS='C' AND FC.STATUS='C' AND FC.SAMPLED_DATE >= #SD# AND FC.SAMPLED_DATE <= #ED#) R WHERE F_CL2<.05 and HPC IS NULL

16. Get results and return NULL if no data for a range of variables:

Select V.Varid,Name,Units, TEXTValue from Vardesc V LEFT OUTER JOIN (SELECT VARID,TEXTVALUE FROM DATATBL WHERE DATESTAMP = '12/1/2016') D
ON V.VARID=D.VARID
WHERE NAME LIKE '%FLOW%' AND VARTYPE ='P'
ORDER BY VARNUM