I was building a report for a customer this morning based on a solution that I had implemented for them. The solution was for calculating volume utilization based on a non-fixed percentage. It is something that I saw adatole use and that zackm expanded on when he joined the team at my full-time gig in the summer of 2017.
The premise is pretty simple. I built a volume custom property where the customer could insert the minimum disk space that the customer wanted before getting and alert and then using a PowerShell script and a SWQL query, I pulled that value, calculated a v_UsageCrit and v_UsageWarn percentage and wrote them into the volume custom properties. The alerting then uses % used and compares it against the v_UsageCrit and v_UsageWarn custom properties before alerting.
All good, right?
Like any good engineer, I wanted to provide the custom with a report that showed them 1) if they have volumes where they hadn't defined the minimum disk space, and 2) if the script the updates the v_UsageCrit and v_UsageWarn custom properties hadn't done its job. Easy enough right? I just write a SWQL query that checks to the two values, but I kept getting back results where I knew the values where the same and SWQL was reporting both the custom property and the calculated value as the same (95.12 = 95.12, right?)
Maybe it was a SWQL thing? (I know tdanner loves when we poke at SWQL!) So I re-wrote the query in SQL. (This is a heavily paraphrased version)
SELECT
v.caption
,v_UsageCrit
,(100-(ROUND((v.v_MinimumDiskSpace/(v.VolumeSize/1024/1024))*100,2)) ) AS [Calculated Value}
FROM Volumes v
WHERE
v_UsageCrit <> (100-(ROUND((v.v_MinimumDiskSpace/(v.VolumeSize/1024/1024))*100,2)) )
But I kept getting results back that were the same as the SWQL results. Why would two values that were the same (heck, the custom property value was inserted by the same calculation that I was comparing it against!) come back as not being equal? I thought it was a rounding issue. Nope. And then I remembered that SQL has different data types!
The custom properties were created as Floating Point Numbers. When I checked the Volumes table in SQL I saw that they were being stored as Real numbers.
But what about the calculated number? What data type was it? The answer is in SQL_VARIANT_PROPERTY
SQL_VARIANT_PROPERTY(v.v_UsageCrit,'basetype') AS [Custom Property Type]
,SQL_VARIANT_PROPERTY( (100-(ROUND((v.v_MinimumDiskSpace/(v.VolumeSize/1024/1024))*100,2)) ), 'basetype' ) AS [Calculated Base Type]
When I stuck that into my query and then ran it I saw this!
Comparing two different data types in SQL meant that they weren't actually the same! I quickly CAST the values and the query returned results as expect. An example of the logic is below.
CAST(v.v_UsageCrit AS decimal(6,2) = CAST( (100-(ROUND((v.v_MinimumDiskSpace/(v.VolumeSize/1024/1024))*100,2)) AS decimal(6,2)
The moral of the story is: If the data looks right to the human eye, it is probably SQL data types that are making your life miserable 
