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 %'.