CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Will Balderstone
Will Balderstone

Reputation: 25

Sum and Convert isnt working

I am doing a sum in SQL Server and I am trying to do this financial year against last financial year percentage difference, with the code shown below:

declare @LastYear table (
[year] nvarchar(40),
total int )

insert @LastYear ([year],[total])
select '2018', count(a.caseno) from activity a
where a.activedate between '2017-04-01' and GETDATE()-365

declare @ThisYear table (
[year] nvarchar(40),
total int)

insert @ThisYear([year],[total])
select '2018', count(a.caseno) from activity a
where a.activedate between '2018-04-01' and GETDATE()

select t.year,sum(convert(decimal(18,2),((t.total/l.total)-1),18))  from 
@ThisYear t
left join @LastYear l on t.year = l.year
group by t.year

The answer that I get from it is -1. The answer of the sum should be -0.06. Could you please advide where I am going wrong please?

Upvotes: 2

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The problem is integer division:

t.total / l.total

This returns an integer.

The simplest solution is:

t.total * 1.0 / l.total

Upvotes: 2

apomene
apomene

Reputation: 14389

on you table you declare total as int. You should declare it float. Try:

declare @LastYear table (
[year] nvarchar(40),
total float
)

Upvotes: 1

Related Questions