SQL

SQL: Infection Blood cultures

May 30, 2012
By

select * from LURS.VLU_MICRO_RESULT where order_fac_id=’HAY’ and MRN = ‘xxxxxxxx’ and (PERFORMED_TEST_CD = ’6000501′ or PERFORMED_TEST_CD = ’6000497′ or PERFORMED_TEST_CD = ’6000495′ or PERFORMED_TEST_CD = ’6000493′) AND COLLECTION_DATE BETWEEN ’2010-03-01′ and ’2010-10-31′ ;

Read more »

Teradata SQL: compare lab values, shorten mrn

May 30, 2012
By

SELECT substr(MRN,5) as ID, ORDER_FAC_ID as FACILITY, VERIFY_TECH_ID as TECH, PERFORMED_TEST_CD as TEST, TEST_RESULT as VAL, COLLECTION_DATE as COLLECTED FROM LURS.VLU_LAB_RESULT where COLLECTION_DATE BETWEEN ’2010-12-31′ AND ’2011-1-1′ AND PERFORMED_TEST_CD = ’1001700′ AND TEST_RESULT BETWEEN ‘ 90′ AND ‘ 133′ AND TEST_RESULT NOT LIKE ‘%TND%’

Read more »

Php: MySQL export and optimize

March 5, 2012
By

Name: My Utils Author: chema Description: This class can export and optimize MySQL database tables. It can create a file with SQL statements for recreating all or part of the tables of a MySQL database. The class can also execute queries to optimize MySQL database tables. URL: http://www.phpclasses.org/package/7397.html Support forum: http://www.phpclasses.org/discuss/package/7397/

Read more »

Php: database in files

March 3, 2012
By

Name: FDB Author: tobytobs Description: This class can create and manage database tables in files. It can create a table in a file setting the columns with given names. The class can perform queries to insert, update, delete and select table records. URL: http://www.phpclasses.org/package/7380.html Support forum: http://www.phpclasses.org/discuss/package/7380/

Read more »

Php: MySQL commands

February 29, 2012
By

1. Name: MySQL Query Builder Author: Rodrigo Brandão Description: This class can build and execute MySQL queries from parameters. It can build SQL statements that define query parameters such as table name, field names and values, condition clauses, etc.. Currently it can build and execute SQL statements of SELECT, INSERT, UPDATE, DELETE and REPLACE...

Read more »

Php: MySQL backup

February 26, 2012
By

Name: cDBbackup Author: Archzilon Eshun-Davies Description: This class can generate PHP scripts that are MySQL backups. It connects to a given MySQL server and extracts the list of all databases. The class generates a PHP script with commands to execute queries that recreate the database tables and insert the records with the current values....

Read more »

Php/SQL: session

February 20, 2012
By

Name: Simple Session (PDO / MySQL) Author: Volker Rubach Description: This class can store session data in MySQL using PDO. It can register its functions to handle several operations to store and retrieve data for PHP sessions from a MySQL database using PDO extension functions. The class computes a fingerprint value from the user...

Read more »

SQL: Clarity SQL and limiting results to TOP #

February 17, 2012
By

In Clarity you can’t use FETCH or LIMIT. So I just get the TOP # result, as such: SELECT TOP 2 SPECIALTY, CONTACT_DATE FROM PATIENT INNER JOIN (PAT_ENC INNER JOIN CLARITY_DEP ON PAT_ENC.DEPARTMENT_ID = CLARITY_DEP.DEPARTMENT_ID) ON PATIENT.PAT_ID = PAT_ENC.PAT_ID WHERE PATIENT.PAT_MRN_ID=’********’ AND CONTACT_DATE < ’02/17/2012′ AND SPECIALTY = ‘Nephrology’ ORDER BY CONTACT_DATE DESC It...

Read more »

SQL: import tab delimited file (.txt)

January 29, 2012
By

Data that are ready to be loaded to the staging database are saved as a tab delimited text file (.txt) and imported to database: LOAD DATA INFILE ‘raw_symptom.txt’ IGNORE INTO TABLE symptom_dimension FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;

Read more »

SQL: added command to show matches by regex or regular expression

January 29, 2012
By

Noisy data, inconsistent or irrelevant data, and duplicate data are reported with the SQL command: … WHERE symptom_name IS NULL OR t.treatment_name = “” OR t.treatment_name REGEXP “|||||| |||||| ]”)

Read more »