SQL Examples - Revision history

Salkin at 16:50, 3 November 2022

2022-11-03T16:50:20Z

← Older revision		Revision as of 16:50, 3 November 2022
Line 28:		Line 28:

	SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;		SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;

Salkin at 16:50, 3 November 2022

2022-11-03T16:50:12Z

← Older revision		Revision as of 16:50, 3 November 2022
Line 1:		Line 1:
	While the SICdb dataset is provided as csv raw data, it is designed to be used in a a relational database management system. Refer to [[Get Started]] for further information building up the database and corresponding software. Our software ~~also~~ includes one-click SQL ~~codes for hundreds of fields~~ like creatinine on day one/two/..., max/min/~~Average~~ lactate and much more.		While the SICdb dataset is provided as csv raw data, it is designed to be used in a a relational database management system. Refer to [[Get Started]] for further information building up the database and corresponding software. Our software includes hundreds of configurable one-click SQL snippets like creatinine on day one/two/..., max/min/average lactate and much more.

Salkin at 10:35, 2 November 2022

2022-11-02T10:35:09Z

← Older revision		Revision as of 10:35, 2 November 2022
Line 56:		Line 56:


	A quite flexible query solution are subqueries. These allow better configuration for the field. '''Note this is a MySQL 8.x query and may need to be adapted in other database systems ~~and the~~ SQL code snippets have been generated by our software.~~'''~~		A quite flexible query solution are subqueries. These allow better configuration for the field. '''Note this is a MySQL 8.x query and may need to be adapted in other database systems'''. These SQL code snippets have been generated by our software.

	<noinclude>=== Select Maximum Creatinine Example ===</noinclude>		<noinclude>=== Select Maximum Creatinine Example ===</noinclude>

Salkin at 10:30, 2 November 2022

2022-11-02T10:30:55Z

← Older revision		Revision as of 10:30, 2 November 2022
Line 22:		Line 22:


	=== Join Query including Unit-Of-Measurement ===		<noinclude>=== Join Query including Unit-Of-Measurement ===</noinclude>
			<includeonly>==== Join Query including Unit-Of-Measurement ====</includeonly>

	[[File:ExampleLab.jpg\|thumb\|left]]		[[File:ExampleLab.jpg\|thumb\|left]]
Line 51:		Line 52:


			<noinclude>== Subquery Examples ==</noinclude>
			<includeonly>=== Subquery Examples ===</includeonly>

	~~== Subquery Examples ==~~

	A quite flexible query solution are subqueries. These allow better configuration for the field. '''Note this is a MySQL 8.x query and may need to be adapted in other database systems and the SQL code snippets have been generated by our software.'''		A quite flexible query solution are subqueries. These allow better configuration for the field. '''Note this is a MySQL 8.x query and may need to be adapted in other database systems and the SQL code snippets have been generated by our software.'''

	=== Select Maximum Creatinine Example ===		<noinclude>=== Select Maximum Creatinine Example ===</noinclude>
			<includeonly>==== Select Maximum Creatinine Example ====</includeonly>

	SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases		SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases

	=== Select First Creatinine Example ===		<noinclude>=== Select First Creatinine Example ===</noinclude>
			<includeonly>==== Select First Creatinine Example ====</includeonly>

	SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 0 ,1 ) AS FirstCreatinineAfterAdmission FROM cases		SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 0 ,1 ) AS FirstCreatinineAfterAdmission FROM cases

	=== Select Second Creatinine Example ===		<noinclude>=== Select Second Creatinine Example ===</noinclude>
			<includeonly>==== Select Second Creatinine Example ====</includeonly>

	SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission FROM cases		SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission FROM cases

	=== Select Second Creatine With Time Example ===		<noinclude>=== Select Second Creatine With Time Example ===</noinclude>
			<includeonly>==== Select Second Creatine With Time Example ====</includeonly>

	A little more advanced example including the time-of-measurement.		A little more advanced example including the time-of-measurement.

Salkin at 10:29, 2 November 2022

2022-11-02T10:29:00Z

← Older revision		Revision as of 10:29, 2 November 2022
Line 7:		Line 7:
	One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.		One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.

	=== Simple Join Query ===		<noinclude>=== Simple Join Query ===</noinclude>
			<includeonly>==== Simple Join Query ====</includeonly>

	[[File:ExampleJoin.jpg\|left\|thumb]]		[[File:ExampleJoin.jpg\|left\|thumb]]

Salkin at 10:28, 2 November 2022

2022-11-02T10:28:21Z

← Older revision		Revision as of 10:28, 2 November 2022
Line 2:		Line 2:


			<noinclude>== Using Referenced Fields ==</noinclude>
			<includeonly>=== Using Referenced Fields ===</includeonly>

	~~== Using Referenced Fields ==~~
	One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.		One of the most important tasks is decoding the referenced fields. While our Software does this automatically the database design makes it also quite simple in raw SQL code.

Salkin at 10:21, 2 November 2022

2022-11-02T10:21:55Z

← Older revision		Revision as of 10:21, 2 November 2022
Line 65:		Line 65:

	A little more advanced example including the time-of-measurement.		A little more advanced example including the time-of-measurement.

			[[File:ExampleTime.jpg\|thumb\|right]]

	SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission ,( SELECT laboratory.Offset AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) / 3600 AS HoursAfterAdmission FROM cases		SELECT `cases`.`CaseID` AS CaseID ,( SELECT laboratory.LaboratoryValue AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) AS SecondCreatinineAfterAdmission ,( SELECT laboratory.Offset AS val FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 ORDER BY laboratory.Offset ASC LIMIT 1 ,1 ) / 3600 AS HoursAfterAdmission FROM cases

Salkin at 10:18, 2 November 2022

2022-11-02T10:18:32Z

@@ Line 21: / Line 21: @@
 === Join Query including Unit-Of-Measurement ===
-[[File:ExampleLab.jpg|frameless|left]]
+[[File:ExampleLab.jpg|thumb|left]]
   SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;
    SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0  AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases

Salkin at 10:02, 2 November 2022

2022-11-02T10:02:57Z

← Older revision		Revision as of 10:02, 2 November 2022
Line 21:		Line 21:
	=== Join Query including Unit-Of-Measurement ===		=== Join Query including Unit-Of-Measurement ===

	[[File:ExampleLab.jpg\|~~thumb~~\|left]]		[[File:ExampleLab.jpg\|frameless\|left]]

	SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;		SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;

Salkin at 10:02, 2 November 2022

2022-11-02T10:02:03Z

← Older revision		Revision as of 10:02, 2 November 2022
Line 24:		Line 24:

	SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;		SELECT caseID,Offset,LaboratoryValue,d_references.ReferenceValue as LaboratoryName, d_references.ReferenceUnit as LabUnit FROM `laboratory` LEFT JOIN d_references ON d_references.ReferenceGlobalID=laboratory.LaboratoryID;

			=== Subqueries to select select ===



			SELECT `cases`.`CaseID` AS CaseID ,( SELECT Max(laboratory.LaboratoryValue) FROM laboratory WHERE laboratory.CaseID = cases.CaseID AND laboratory.LaboratoryID = 367 AND Offset >= 0 AND Offset < 7 * 86400 ) AS MaximumCreatinineInFirstWeek FROM cases