I am trying to create a query that will give me a ratio calculation based on certain counts from two columns which are working however unless there is data in the table, it won't be able to calculate a ratio.

To mitigate this I want my script to convert all null values to a 1.

This is my current script (I'm removing the rest of the joins because it isn't necessary to include)

SELECT DISTINCT TB1.Person,
TB2.AllocatedCount as AllocatedCount,
TB3.UnallocatedCount as UnallocatedCount,
AllocatedCount * 1.0 / UnallocatedCount * 1.0 as Ratio,

The above yields the following results which work perfectly provided the table has data for both columns:

I want to change the script so that all null results would represent the number 1 however the ratio doesnt seem to be calculating.

SELECT DISTINCT TB1.Person,
CASE WHEN TB2.AllocatedCount IS NULL THEN 1 ELSE TB2.AllocatedCount as AllocatedCount,
CASE WHEN TB3.UnallocatedCount IS NULL THEN 1 ELSE TB3.UnallocatedCount as UnallocatedCount,
AllocatedCount * 1.0 / UnallocatedCount * 1.0 as Ratio,

Above yields the following results:

Any idea what I'm doing wrong with my script to not calculate the ratio?

CodePudding user response：

You also need to check for NULL values when you calculate the ratio (using ISNULL() here is probably a better option):

SELECT 
   DISTINCT TB1.Person,
   ISNULL(TB2.AllocatedCount, 1) AS AllocatedCount,
   ISNULL(TB3.UnallocatedCount, 1) AS UnallocatedCount,
   ISNULL(TB2.AllocatedCount, 1) * 1.0 / ISNULL(TB3.UnallocatedCount, 1) * 1.0 AS Ratio
FROM 
   ...

CodePudding user response：

I would use ISNULL

SELECT DISTINCT TB1.Person,
isnull(TB2.AllocatedCount,1) as AllocatedCount,
isnull(TB3.UnallocatedCount,1) as UnallocatedCount,
isnull(AllocatedCount,1) * 1.0 / isnull(UnallocatedCount,1) * 1.0 as Ratio,