BMI is often used as a measure of obesity when performing population analysis. It is easy to calculate and only requires a person’s Height and Weight. The basic formula is Weight in Kilograms divided by Squared Height in Meters ().
Sometimes when working with large datasets, you may find that you only have part of the picture. I have noticed that some clinical systems will just log the BMI and either the Height or Weight and not all three measurements. That missing measure can, however, be calculated if the other two are present.
For most uses, we are interested in having the Height in Centimetres (cm), Weight in Kilograms (kg) and BMI (kg/m²); So from this point on those are the units that I will be using.
Calculating the BMI from Weight (kg) and Height (cm), is easily worked out by dividing the Height by 100 to convert it to metres and using the standard formula: . Which in SQL is:
SELECT [BMI] = [Weight] / SQUARE([Height] / 100)
So to calculate the person’s Height (cm) from their Weight (kg) and BMI (kg/m²). The following formula will work: . Which is expressible in SQL as:
SELECT [Height] = SQRT([Weight] / [BMI]) * 100
And to calculate the person’s Weight (kg) from their Height (cm) and BMI (kg/m²). The formula is: . Which again in SQL is:
SELECT [Weight] = [BMI] * SQUARE([Height] / 100)
These can then all be combined in SQL to – where possible – fill in the blanks automatically:
[Height] = ISNULL([Height], SQRT([Weight] / [BMI]) * 100 )
,[Weight] = ISNULL([Weight], [BMI] * SQUARE([Height] / 100))
,[BMI] = ISNULL([BMI] , [Weight] / SQUARE([Height] / 100))
Simple Regex for cleaning up Read Codes (Read v2 or CTV3). Does no validation, removes trailing dots and local modifications.
When trying to query what tables are being replicated most people know about the
is_replicated flag in
sys.tables, however, fewer know that there is a lot more information available about replication in the
distribution database. With the
distribution database it is possible to query what Articles (tables/views/objects etc.) are published and which Publications they are available in.
[P].[publication] AS [Publication Name]
,[A].[publisher_db] AS [Database Name]
,[A].[article] AS [Article Name]
,[A].[source_owner] AS [Schema]
,[A].[source_object] AS [Object]
FROM [distribution].[dbo].[MSarticles] AS [A]
INNER JOIN [distribution].[dbo].[MSpublications] AS [P]
ON ([A].[publication_id] = [P].[publication_id])
[Publication Name] ASC
,[Article Name] ASC
View on StackOverflow
EXCEPT you can quickly and efficiently compare whether two columns are equal (including when they are both
NULL). Using this method if the column on both sides is
NULL then they will be treated as equal; and if one side is
NULL and the other not, they will be correctly treated as not equal.
SELECT ... FROM ...
WHERE EXISTS( SELECT [Column_A] EXCEPT SELECT [Column_B] );
The biggest power of this is realised when combining it with the
MERGE statement. By using
AND EXISTS(... EXCEPT ...) within the
WHEN MATCHED ... portion of the merge, SQL automatically checks for
NULL‘s on both sides. This is more performant than using
ISNULL(...) and the not equal to operator
... ..., as SQL can now take advantage of any indexes that exist.
MERGE INTO [TargetTable] AS [T]
USING [SourceTable] AS [S]
ON ([T].[ID] = [S].[ID])
WHEN MATCHED AND EXISTS (
,[T].[Description] COLLATE Latin1_General_CS_AS
,[S].[Description] COLLATE Latin1_General_CS_AS
[T].[Code] = [S].[Code]
,[T].[Description] = [S].[Description]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ID], [Code], [Description])
VALUES ([ID], [Code], [Description])
WHEN NOT MATCHED BY SOURCE THEN DELETE;