Author: AeroX

Calculating Height, Weight and BMI from Incomplete Data

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 (Weight \div Height^{2} = BMI).

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: Weight \div \left ( Height \div 100 \right )^{2}. Which in SQL is:

SELECT [BMI] = [Weight] / SQUARE([Height] / 100)
FROM ...

So to calculate the person’s Height (cm) from their Weight (kg) and BMI (kg/m²). The following formula will work: \sqrt{Weight \div BMI} \times 100. Which is expressible in SQL as:

SELECT [Height] = SQRT([Weight] / [BMI]) * 100
FROM ...

And to calculate the person’s Weight (kg) from their Height (cm) and BMI (kg/m²). The formula is: BMI \times \left ( Height \div 100 \right )^{2}. Which again in SQL is:

SELECT [Weight] = [BMI] * SQUARE([Height] / 100)
FROM ...

These can then all be combined in SQL to – where possible – fill in the blanks automatically:

SELECT
     [Height] = ISNULL([Height], SQRT([Weight] / [BMI]) * 100     )
    ,[Weight] = ISNULL([Weight], [BMI]    * SQUARE([Height] / 100))
    ,[BMI]    = ISNULL([BMI]   , [Weight] / SQUARE([Height] / 100))
FROM ...

Querying SQL’s Distribution Database

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.

Example:

SELECT
	 [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])
ORDER BY
	 [Publication Name] ASC
	,[Article Name] ASC
;

View on StackOverflow

Combining MERGE with EXISTS and EXCEPT

By combining EXISTS with 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.

Example:

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 (
	SELECT
		 [T].[Code]
		,[T].[Description] COLLATE Latin1_General_CS_AS
	EXCEPT SELECT
		 [S].[Code]
		,[S].[Description] COLLATE Latin1_General_CS_AS
) THEN
	UPDATE SET
		 [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;