REDS-58000-Bridgeport-Hospital-Notebook-Quine-01

REDS-58000-Bridgeport-Hospital-notebook-quine-01.doc

Appendix E – Common SQL Commands (Mar 18, 2017)

OVERALL SYNTAX ORDER

SELECT * [or column name(s)]

FROM table_name

WHERE column_1 = value_1

AND column_2 = value_2;

GROUP BY column_name ASC|DESC;

ORDER BY column_name ASC|DESC;

[JOIN FORMAT FORMAT]

SELECT * [or column name(s)]

JOIN CODE

FROM table_name1 p

INNER JOIN table_name2 n -- default join is inner

ON p.column1 = n.c

FROM table_name

WHERE column_1 = value_1

AND column_2 = value_2;

GROUP BY column_name ASC|DESC;

ORDER BY column_name ASC|DESC;

[JOIN FORMAT EXAMPLES]

-- use REDS3_Stage_Q1 - FIRST WORKING JOIN .. 10 lines

SELECT top 10 p.TopographySID, p.Topography, n.LocalLabId, n.TopographySID

FROM [X1].[VA_Topography] p

INNER JOIN [X1].[VA_UniqueTestIdentifier] n -- default join is inner

ON p.TopographySID = n.TopographySID

-- use REDS3_Stage_Q1 - WORKING LEFT JOIN preview .. 1,587,435 lines with 1 million cases of NULL [VA_UniqueTestIdentifier] variables

SELECT p.TopographySID, p.Topography, n.LocalLabId, n.TopographySID

FROM [X1].[VA_Topography] p

LEFT OUTER JOIN [X1].[VA_UniqueTestIdentifier] n

ON p.TopographySID = n.TopographySID

Background

SQL, 'Structured Query Language', is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and helps maintain the integrity of databases, regardless of size.

Here's an appendix of commonly used commands.

Commands

ALTER TABLE

ALTER TABLE table_name ADD column datatype;

ALTER TABLE lets you add columns to a table in a database.

AND

SELECT column_name(s)

FROM table_name

WHERE column_1 = value_1

AND column_2 = value_2;

AND is an operator that combines two conditions. Both conditions must be true for the row to be included in the result set.

AS

SELECT column_name AS 'Alias'

FROM table_name;

AS is a keyword in SQL that allows you to rename a column or table using an alias.

ASC

ORDER BY ASC is used to sort the result-set in ascending order (default)

AVG

SELECT AVG(column_name)

FROM table_name;

AVG() is an aggregate function that returns the average value for a numeric column.

BETWEEN

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value_1 AND value_2;

The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text or dates.

BY à GROUP BY

COUNT

SELECT COUNT(column_name)

FROM table_name;

COUNT() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL.

CREATE TABLE

CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);

CREATE TABLE creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.

DELETE

DELETE FROM table_name WHERE some_column = some_value;

DELETE statements are used to remove rows from a table.

DESC

ORDER BY DESC is used to sort the result-set in descending order (default = ASC)

DISTINCT à SELECT DISTINCT

FROM à DELETE FROM

GROUP BY

SELECT COUNT(*)

FROM table_name

GROUP BY column_name;

GROUP BY is a clause in SQL that is only used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

IN

SELECT column_name

FROM table_name

WHERE column_name IN (value_1, value_2, value_3)

IN is an operator that filters the result set to only include rows where any condition is true (avoids need for a long string of OR statements).

INNER JOIN

SELECT column_name(s) FROM table_1

JOIN table_2

ON table_1.column_name = table_2.column_name;

An inner join will combine rows from different tables if the join condition is true.

INSERT

INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);

INSERT statements are used to add a new row to a table.

JOIN … see LEFT JOIN, RIGHT JOIN, OUTER JOIN

LEFT JOIN … joining 2 tables while preserving all the lines of the left table

LIKE

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern;

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column. …

PATTERN CODES (DBQ):

“%” means wildcard string like “*” in other computer programs

“_” [underscore] means any character like “?” in any other computer program

LIMIT

SELECT column_name(s)

FROM table_name

LIMIT number;

LIMIT is a clause that lets you specify the maximum number of rows the result set will have.

MAX

SELECT MAX(column_name)

FROM table_name;

MAX() is a function that takes the name of a column as an argument and returns the largest value in that column.

MIN

SELECT MIN(column_name)

FROM table_name;

MIN() is a function that takes the name of a column as an argument and returns the smallest value in that column.

OR

SELECT column_name

FROM table_name

WHERE column_name = value_1

OR column_name = value_2;

OR is an operator that filters the result set to only include rows where either condition is true.

ORDER BY

SELECT column_name

FROM table_name

ORDER BY column_name ASC|DESC;

ORDER BY is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically.

OUTER JOIN

SELECT column_name(s) FROM table_1

LEFT JOIN table_2

ON table_1.column_name = table_2.column_name;

An outer join will combine rows from different tables even if the the join condition is not met. Every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the righttable.

RIGHT JOIN … joining 2 tables while preserving all the lines of the right table

ROUND

SELECT ROUND(column_name, integer)

FROM table_name;

ROUND() is a function that takes a column name and an integer as an argument. It rounds the values in the column to the number of decimal places specified by the integer.

SELECT

SELECT column_name FROM table_name;

SELECT statements are used to fetch data from a database. Every query will begin with SELECT.

SELECT DISTINCT

SELECT DISTINCT column_name FROM table_name;

SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s).

SELECT * FROM

SELECT *

FROM [X1].[Dim_T_TestSiteYearMonth]

WHERE Site = 689 and Year = 2015

ORDER BY count desc

STDEV

SELECT STDEV(column_name)

FROM table_name;

STDEV() is a function that takes the name of a column as an argument and returns the standard deviation of all the values in that column (including date/time fields).

SUM

SELECT SUM(column_name)

FROM table_name;

SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

TOP

SELECT TOP 10 *

SELECT TOP 10 *

SELECT TOP 50 (column_name)

SELECT TOP 50 (column_name)

FROM table_name;

TOP is a function that takes the name of a column as an argument and returns the TOP ”X” entries in that column.

UPDATE

UPDATE table_name

SET some_column = some_value

WHERE some_column = some_value;

UPDATE statments allow you to edit rows in a table.

VAR

SELECT VAR(column_name)

FROM table_name;

VAR() is a function that takes the name of a column as an argument and returns the variance of all the values in that column (including date/time fields).

WHERE

SELECT column_name(s)

FROM table_name

WHERE column_name operator value;

WHERE is a clause that indicates you want to filter the result set to include only rows where the following condition is true.

Made in NYC © 2016 Codecademy / DBQuine @2017

Appendix F – SQL Queries

--Highlights

select top 50 *

from [X1].[VA_LabChemTest] --Va name id, name

select top 50 *

from [X1].[VA_Topography] --Va specimen id, specien

select top 50 *

from [X1].[VA_UniqueTestIdentifier] --TestId, Va test id, Va specimen id

select top 10 *

from [X1].[Dim_RawExtractDateRange]

--List of tests by site year month count

select *

from [X1].[Dim_T_TestSiteYearMonth]

where Site = 689 and Year = 2015

order by count desc

--What is the Yoinc code?

select *

from [X1].[T6_Output] --Yoinc assignments

where TestId = 415369

select * from [X1].[Dim_Loinc_New_View] --Yoinc codes

--What is the LOINC code for the testId? 2160-0 and 12191-3

select top 50 *

from [X1].[Dim_T_LoincToId]

where TestId = 415369

--Seeking High Numbered LOINC Codes (July 14, 2016) – works 35 seconds

select DISTINCT LOINC

from [X1].[VA_RawExtract] --raw data from the VA

r.LocalLabId and u.TopographySID = r.TopographySID

where LOINC > '38000-0'

group by LOINC

order by LOINC

--Seeking High Numbered YOINC Codes WE MADE (July 14, 2016) – works 0 seconds

select r.LoincFinal

from [X1].[T6_Output] r -- assigned YOINC codes per TestId

where LoincFinal LIKE '_____-_' and LoincFinal > '38000-0'

group by LoincFinal

order by LoincFinal

-- CONCLUDE: YOINC codes above 38686-2 (e.g. the 54 assigned YOINC codes between 38875-1 and 78012-2) are unique to us

--See High Numbered YOINC Code Map to Loinc Codes (July 18, 2016) – SUCCESS 0 seconds

select "Yoinc", "Loinc"

from [X1].[Yoinc_Hierarchy_Raw] r

where Yoinc LIKE '_____-_' and Yoinc > '38000-0'

group by Yoinc, Loinc

order by Yoinc, Loinc

--See Year and Site each High Numbered YOINC Code (July 18, 2016) – SUCCESS 0 seconds

select Yoinc, Site, Year

from [X1].[Dim_T_YoincSiteYearMonth]

where Yoinc LIKE '_____-_' and Yoinc > '38000-0'

group by Yoinc, Site, Year

order by Yoinc, Site, Year

[5,473 line output ... artifact of George assigning a new YOINC code potentially back to the beginning of the test database for a specific test]

--Assorted Working Queries

select LoincFinal

from [X1].[T6_Output]

where LoincFinal LIKE '_____-_' and LoincFinal > '38000-0'

group by LoincFinal

order by LoincFinal

select top 100 LocalLabId, TopographySID, Id

from [X1].[VA_UniqueTestIdentifier]

group by TopographySID, LocalLabId, Id

order by TopographySID, LocalLabId, Id

select Loinc, Year, Month

from [X1].[TestIdentifierDemo_NewLoinc_FirstLoincTotal]

group by Year, Month, Loinc

order by Year, Month, Loinc

select Yoinc

from [X1].[M6_Results_Quantitative]

where Yoinc LIKE '_____-_' and Yoinc > '38000-0'

group by Yoinc

order by Yoinc

-- Sample [X1].[VA_RawExtract] data

select top 10 *

from [X1].[VA_RawExtract]

Id LocalLabId TopographySID LOINC Year Mon Site Result Unit Abn RefHi RefLo #

74983605 800000000 800000749 21467-6 2008 9 436 <64 TITER NULL 64 0 1

74983842 800000000 800000749 21467-6 2009 8 436 comment TITER NULL 64 0 1

74983686 800000001 800000749 21468-4 2008 9 436 <64 TITER NULL 64 0 1

74983687 800000001 800000749 21468-4 2009 8 436 <64 TITER NULL 64 0 1

74983606 800000002 800000749 3333-2 2006 5 436 121 ng/mL NULL NULL NULL 1

74983688 800000002 800000749 3333-2 2006 3 436 235 ng/mL NULL NULL NULL 1

74983843 800000002 800000749 3333-2 2007 5 436 <20 ng/mL NULL NULL NULL 1

74983607 800000003 800000091 *Missing* 2006 8 436 1 NULL H* NULL "NEG" 48

74983608 800000003 800000091 *Missing* 2006 10 436 canc NULL NULL NULL "NEG" 6

--Sample [X1].[VA_Topography] data

select top 10 *

from [X1].[VA_Topography]

TopographySID Topography

-1 *Missing*

0 *Unknown at this time*

800000000 PARATHYROID GLAND

800000001 THROAT

800000002 URINE & BLOOD

800000003 FLUID, NOT OTHERWISE SPECIFIED

800000004 ABDOMINAL FLUID

800000005 CATH TIP

800000006 SPORE STRIP

800000007 WATER

--Sample [X1].[ VA_UniqueTestIdentifier] data

select top 10 *

from [X1].[VA_UniqueTestIdentifier]

Id LocalLabId TopographySID

1 0 1400003604

2 800000000 800000749

3 800000001 800000749

4 800000002 800000749

5 800000003 800000091

6 800000003 800000499

7 800000004 800000683

8 800000005 800000749

9 800000007 800016015

10 800000007 800001050

--Temporary.sql Fluids (bad dates) and Duplicate Check REDS 3/6/2017

/* 2016Q1Q2 Encounters Duplicates – George Hauser

--2015q3q4 all sites, 2016q1q2 yale/src only

--SRC - BB_Utilization

--Yale - REDS3_Map_Q1

--BP - REDS3_Prod_BH

--2016Q1Q2 Bridgeport = Stripe

Yale - 2016q1 to 2016Q2 - Reds3_Map_Q1

SRC - 2016q1 to 2016Q2 - BB_Utilization

Bridgeport - 2016q1q4 - Reds3_Prod_BH

Bridgeport - 2016Q1Q2 - Stripe

*/

-- Yale - 2015 H2

use REDS3_Map_Q1

select distinct m.EncounterId

into #yale_2015q34

from Elig_VisitList v

join DeIdMaster m on m.EncounterId = v.Csn

join RC_Encounters f on f.EncounterId = m.RecipEncounterId

where v.Qtr in ('2015q3', '2015q4')

group by m.EncounterId

-- Yale - 2016 H1

use REDS3_Map_Q1

select distinct m.EncounterId

into #yale_2016q12

from Elig_VisitList v

join DeIdMaster m on m.EncounterId = v.Csn

join RC_Encounters f on f.EncounterId = m.RecipEncounterId

where v.Qtr in ('2016q1', '2016q2')

group by m.EncounterId

-- BH 2015 H2

use Reds3_Prod_BH

select distinct m.EncounterId

into #bp_2015q34

from Elig_VisitList v

join DeIdMaster m on m.EncounterId = v.Csn

join RC_Encounters f on f.EncounterId = m.RecipEncounterId

where v.Qtr in ('2015q3', '2015q4')

group by m.EncounterId

-- BH 2016 H1

use Stripe

select distinct m.EncounterId

into #bp_2016q12

from Elig_VisitList v

join DeIdMaster m on m.EncounterId = v.Csn

join RC_Encounters f on f.EncounterId = m.RecipEncounterId

where v.Qtr in ('2016q1', '2016q2')

group by m.EncounterId

-- SRC 2015 H2

use BB_Utilization

select distinct m.EncounterId

into #src_2015q34

from Elig_VisitList v

join DeIdMaster m on m.EncounterId = v.Csn

join RC_Encounters f on f.EncounterId = m.RecipEncounterId

where v.Qtr in ('2015q3', '2015q4')

group by m.EncounterId

-- SRC 2016 H1

use BB_Utilization

select distinct m.EncounterId

into #src_2016q12

from Elig_VisitList v

join DeIdMaster m on m.EncounterId = v.Csn

join RC_Encounters f on f.EncounterId = m.RecipEncounterId

where v.Qtr in ('2016q1', '2016q2')

group by m.EncounterId

/*

-- intersect 1 - set NO DUPLICATES

select * from #yale_2015q34

intersect

select * from #src_2015q34

-- intersect 2 - set

select * from #yale_2015q34

intersect

select * from #bp_2015q34

-- intersect 3 - set NO DUPLICATES

select * from #yale_2015q34

intersect

select * from #yale_2016q12

-- intersect 4 - set NO DUPLICATES

select * from #yale_2015q34

intersect

select * from #src_2016q12

-- intersect 5

select * from #yale_2015q34

intersect

select * from #bp_2016q12

-- intersect 6

select * from #src_2015q34

intersect

select * from #bp_2015q34

-- intersect 7 NO DUPLICATES

select * from #src_2015q34

intersect

select * from #yale_2016q12

-- intersect 8 NO DUPLICATES

select * from #src_2015q34

intersect

select * from #src_2016q12

-- intersect 9 NO DUPLICATES

select * from #src_2015q34

intersect

select * from #bp_2016q12

-- intersect 10

select * from #bp_2015q34

intersect

select * from #yale_2016q12

-- intersect 11 set

select * from #bp_2015q34

intersect

select * from #src_2016q12

-- intersect 12 set

select * from #bp_2015q34

intersect

select * from #bp_2016q12

-- intersect 13 set NO DUPLICATES

select * from #yale_2016q12

intersect

select * from #src_2016q12

-- intersect 14 set

select * from #yale_2016q12

intersect

select * from #bp_2016q12

-- intersect 15 set

select * from #src_2016q12

intersect

select * from #bp_2016q12

*/

-- INTERSECT CODE

--2015q3q4 all sites, 2016q1q2 yale/src only

--SRC - BB_Utilization

--Yale - REDS3_Map_Q1

--BP - REDS3_Prod_BH

--2016Q1Q2 Bridgeport = Stripe

select *

into #dupEnc

from (

-- intersect 2 - done

select *, 'Yale_2015Q34' Location, 'Yale' Site from (select * from #yale_2015q34 intersect select * from #bp_2015q34) t union all

select *, 'BP_2015q34', 'BP' from (select * from #yale_2015q34 intersect select * from #bp_2015q34) t union all

-- intersect 5 done

select *, 'yale_2015q34', 'Yale' from (select * from #yale_2015q34 intersect select * from #bp_2016q12) t union all

select *, 'bp_2016q12', 'BP' from (select * from #yale_2015q34 intersect select * from #bp_2016q12) t union all

-- intersect 6 done

select *, 'src_2015q34', 'SRC' from (select * from #src_2015q34 intersect select * from #bp_2015q34) t union all

select *, 'bp_2015q34', 'BP' from (select * from #src_2015q34 intersect select * from #bp_2015q34) t union all

-- intersect 10 done

select *, 'bp_2015q34', 'BP' from (select * from #bp_2015q34 intersect select * from #yale_2016q12) t union all

select *, 'yale_2016q12', 'Yale' from (select * from #bp_2015q34 intersect select * from #yale_2016q12) t union all

-- intersect 11 done

select *, 'bp_2015q34', 'BP' from (select * from #bp_2015q34 intersect select * from #src_2016q12) t union all

select *, 'src_2016q12', 'SRC' from (select * from #bp_2015q34 intersect select * from #src_2016q12) t union all

-- intersect 12 done

select *, 'bp_2015q34', 'BP' from (select * from #bp_2015q34 intersect select * from #bp_2016q12) t union all

select *, 'bp_2016q12', 'BP' from (select * from #bp_2015q34 intersect select * from #bp_2016q12) t union all

-- intersect 14 done

select *, 'yale_2016q12', 'Yale' from (select * from #yale_2016q12 intersect select * from #bp_2016q12) t union all

select *, 'bp_2016q12', 'BP' from (select * from #yale_2016q12 intersect select * from #bp_2016q12) t union all

-- intersect 15 done

select *, 'src_2016q12', 'SRC' from (select * from #src_2016q12 intersect select * from #bp_2016q12) t union all

select *, 'bp_2016q12', 'BP' from (select * from #src_2016q12 intersect select * from #bp_2016q12) t

) t

select distinct Location

from #dupEnc

select *

from #dupEnc d

--2. Run a encounters against Clarity to decide the correct encounter

--Note: the count should equal the number of input encounters

IF OBJECT_ID('tempdb..#dupEncLoc', 'U') IS NOT NULL DROP TABLE #dupEncLoc

select e.EncounterId,

case HOSPITAL_AREA_ID