SQLTeam.com | Weblogs | Forums

Divide Won't work

Hello.

I can't get the following to divide:

Difference_No_Per_2 = ((SUM(Case When ah.EntryDate Between '12/01/2020' AND '12/02/2020' Then 1 Else 0 End) - SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/02/2019' Then 1 Else 0 End)) / SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/02/2019' Then 1 Else 0 End))

The result I get is zero.

Where am I going wrong? Thanks.

Add a * 1.0 to make sure you avoid pure integer division:

Difference_No_Per_2 = CAST(((SUM(Case When ah.EntryDate Between '12/01/2020' AND '12/02/2020' Then 1 Else 0 End) * 1.0 - SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/02/2019' Then 1 Else 0 End)) / SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/02/2019' Then 1 Else 0 End)) AS decimal(9, 1))

In SQL Server, 99/100 would be 0.

obviously it must be going to the ELSE in you case statement. which might mean
something wrong here

EntryDate Between '12/01/2020' AND '12/02/2020'

What is the result of each individual statement?

SUM(Case When ah.EntryDate Between '12/01/2020' AND '12/02/2020' Then 1 Else 0 End)
SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/02/2019' Then 1 Else 0 End)

Are you trying to find the percentage of entries this year from the total? If so - wouldn't you need this statement after the division?

SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/02/2029' Then 1 Else 0 End) + SUM(Case When ah.EntryDate Between '12/01/2020' AND '12/02/2020' Then 1 Else 0 End)

And don't forget about integer division...if you have, for example a total of 5from the first part and divide that by 10 for the second part (e.g. 5 / 10) the result will be 0 and not 0.5. To get to 0.5 in that situation you need to change one of the parts to a numeric value:

SUM(Case When ah.EntryDate Between '12/01/2020' AND '12/02/2020' Then 1 Else 0 End) * 1.0

Thank you. It worked.

Format(Cast(((SUM(Case When ah.EntryDate Between '12/01/2020' AND '12/07/2020' Then 1 Else 0 End) *1.0 - SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/07/2019' Then 1 Else 0 End)) / SUM(Case When ah.EntryDate Between '12/01/2019' AND '12/07/2019' Then 1 Else 0 End)) AS decimal(9,2)),'P0')

I would not recommend using Format - it is incredibly slow and not needed here. The CAST is only needed to define a specific return...Adding format returns the value as a percentage...instead of using format you can do this:

 Select cast(5 * 1.0 / 10 * 100.0 As decimal(9,2))

This returns 50.00 which is much better than return a string value of '50 %'.