calculation between two lists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

calculation between two lists

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-23 : 17:35:28

Hello there. I am trying to create a kpi for work.

I have a history table with a total_amount column. decimal (20,2)

I write two querys.

select sum(total_amount), month_number from history_Table
where payment_status = 'unPaid'-- all unpaid in months

result

2342342 1
983938 2
933987 3
12384 4 and so on...

then query two

select sum(total_amount), month_number from history_Table
where payment_status = 'Paid'-- all paid in months

234238742 1
983988938 2
93399987 3
1238984 4 and so on...

But what i want to do is divide the first list by the second and still be listed by month.

what would be the best way to achieve this.??

I am using 2008

many thanks

MCTS / MCITP certified

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-23 : 17:44:54

[code]
select
sum(case when payment_status = 'unPaid' then total_amount end)
/
sum(case when payment_status = 'Paid' then total_amount end),
month_number
from
history_TAble
group by
month_number;[/code]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-23 : 17:48:10

thank you very much

merry christmas

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-23 : 17:56:38

could i expand the calulation for example

select
sum(case when payment_status = 'unPaid' then total_amount end)
/
sum(case when payment_status = 'Paid' then total_amount end)
x 365,
month_number
from
history_TAble
group by
month_number

??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 01:06:20

Yes You can do that

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-24 : 05:58:03

i am using the below script.

but my results are all coming out as 0
when they should be like 0.0433.

how do i convert the count(column) to produce this.

also i want to multiply my result by another number. see second batch of script. below first.

where am i going wrong with the multiply part of the code?

select [extract month],
(COUNT(case when HISTORY_STATUS not in (249) then skey end)
/
COUNT(case when HISTORY_STATUS in (249) then skey end)) --from f_debtors_stats
as 'Ave_Debtors' from f_debtors_stats
where item_code NOT in (select item_code from dbo.Excluded_Plans_from_KPI)
group by [Extract Month]

----------------------------------------------------------------------------------------

select [extract month],
(COUNT(case when HISTORY_STATUS not in (249) then skey end)
/
COUNT(case when HISTORY_STATUS in (249) then skey end)x 330)
as 'Ave_Debtors' from f_debtors_stats
group by [Extract Month]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 06:13:31

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-24 : 06:23:58

I have converted to float and decimal. see below

and still getting 0

select [extract month],
(COUNT(case when HISTORY_STATUS not in (249) then convert(float ,[Extract Month]) end)
/
COUNT(case when HISTORY_STATUS in (249) then convert(float,[Extract Month]) end))
as 'Ave_Debtors' from f_debtors_stats
where item_code NOT in (select item_code from dbo.Excluded_Plans_from_KPI)
group by [Extract Month]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 06:27:47

select [extract month],
1.0*(COUNT(case when HISTORY_STATUS not in (249) then convert(float ,[Extract Month]) end)
/
COUNT(case when HISTORY_STATUS in (249) then convert(float,[Extract Month]) end))
as 'Ave_Debtors' from f_debtors_stats
where item_code NOT in (select item_code from dbo.Excluded_Plans_from_KPI)
group by [Extract Month]

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-24 : 06:34:01

really sorry

when converting to float.

result is

0.0
0.0
0.0 and so on

select [extract month],
1.0*(COUNT(case when HISTORY_STATUS not in (249) then convert(float,[Extract Month]) end)
/
COUNT(case when HISTORY_STATUS in (249) then convert(float,[Extract Month]) end) )
as 'Ave_Debtors' from f_debtors_stats
where item_code NOT in (select item_code from dbo.Excluded_Plans_from_KPI)
group by [Extract Month]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-24 : 07:05:16

someone please help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-24 : 08:37:47

You are getting zeros because of integer division. Look at each of these selects, and you will see what I mean.


select 5/6;
select 5.0/6;
select 1.0*(5/6);

The first select gives zero because both numerator and denominator are integers. When you divide an integer by an integer, the result is truncated to the closes integer.
The second gives you a floating point number because numerator is float.
The third gives you 0.0 because the result of the integer division (5/6) is zero.

So what you need to do is this:

select [extract month],
(1.0*COUNT(case when HISTORY_STATUS not in (249) then convert(float,[Extract Month]) end)
/
COUNT(case when HISTORY_STATUS in (249) then convert(float,[Extract Month]) end) ) 
as 'Ave_Debtors' from f_debtors_stats
where item_code NOT in (select item_code from dbo.Excluded_Plans_from_KPI)
group by [Extract Month]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-11-24 : 08:42:30

is that it, bloody hell.

thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources