perform floating point addition in SQL stored procedure

Hi..
Is there any way to add the value of 4 column and at the same time print the result together with another column?

I have this stored procedure:

CREATE PROCEDURE sp_queuelist AS
BEGIN
DECLARE @.temp1 As Decimal
DECLARE @.temp2 As Decimal
DECLARE @.cash As Decimal
DECLARE @.cheque As Decimal
DECLARE @.card As Decimal
DECLARE @.nets As Decimal
DECLARE @.bill As Decimal
DECLARE @.company As Decimal

SELECT
@.cash=Cash,@.cheque=Cheque,@.card=Card,@.nets=Nets,
@.bill=Bill,@.company=Company
FROM QUEUE
ORDER BY QNo

SET @.temp1 = @.cash + @.cheque + @.card + @.nets
SET @.temp2 = @.bill +@.company

Select QNO,
PCNo,
PName,
@.temp1 As totalCash,
@.temp2 As totalContract,
Doctor

FROM QUEUE
ORDER BY QNo
END
GO

Basically I want to add 4 columns: cash+cheque+card+nets into totalCash and bill+company to totalContract.

All the 6 field types are decimal.

I want to calculate temp1 and temp2 and then select the rest of the column to be displayed in the datagrid.

However, this stored procedure gives me 2 problems:
1. It gives the rounding result of the addition of decimal number, not the decimal itself.
2. All the rows in datagrid display the same result for totalCash and totalContract, which is the total from the last row in the table.

I seldom use stored procedure.
Is there any way to solve this problem?
Any suggestion is most welcomed.
Thank you in advanced.
Sincerely

Agustina(1) you can try changing the decimal to float.

(2) in the design of your table, you can set the formula for the column as sum of the other 4 columns. that way you dont need to worry abt doing the addition. anytime you make any change in any of the columns, the computed column is automatically updated.
if you need more help in this approach, let me know.

HTH.

Perform an experssion in desing mode

Hi all,

SSIS, I would like to perform/validate an experssion in design mode. In particular, i would like to see the value of the varibles before running the whole package. I tried several time in Command Windows or Immedite Window unsuccessfully. Do those windows can only be used in running mode? Or how can I do that in other way?

Thanks

Where are you performing the expression? In the Control Flow or Data Flow.

This BOL entry describes how to evaluate an expression at design time in the Control Flow: http://msdn2.microsoft.com/en-us/library/ms141698.aspx

Expressions in the data flow are validated automatically at design time and any errors will be raised - but they can not be evaluated at design time.

Debug windows are only available when the package is running in the debugger.

Donald Farmer

|||

That's mean I must create varibles in Control flow or date flow even though i just want to see the result of 1+1? Can't it like in VB or C# IDE?

|||

I'm still not clear where you are using the expression, so it's difficult to help in detail. You can evaluate Property Expressions, but not expressions in the data flow.

Donald

Perform aggregate against group value

Is there a way that I can perform my aggregate function agains the value in
group row instead of details row?
Because I do not show numeric values in the details but group row.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET DevelopersDid you try the Previous aggregate function? Just place it into the group
header like =Previous(Fields!Country.Value) and it should work. Note: the
previous function has only one argument.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Is there a way that I can perform my aggregate function agains the value
> in group row instead of details row?
> Because I do not show numeric values in the details but group row.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
>|||Hi Robert,
I tried Previous as you suggested but this time I endup with empty
datacell..
However, please excuse me that I was not clear enough previously, I want to
perform this aggregate against the group in the footer. So I tried to use
the full previous function as described in the help "Previous(Expression,
AggFunction, PreviousScope, AggScope)" but it basically fails as you've
mentioned.
Anyway, do you have a solution for me?
I would like to sum my group values in the footer.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET Developers
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
> Did you try the Previous aggregate function? Just place it into the group
> header like =Previous(Fields!Country.Value) and it should work. Note: the
> previous function has only one argument.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "SevDer" <sevder@.newsgroup.nospam> wrote in message
> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>|||> I would like to sum my group values in the footer.
Maybe I'm missing something, but assuming you group on
=Fields!FieldName.Value, just adding an expression like
=Sum(Fields!FieldName.Value) in the table footer should sum the group
values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:%23rqJeUw2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Hi Robert,
> I tried Previous as you suggested but this time I endup with empty
> datacell..
> However, please excuse me that I was not clear enough previously, I want
> to perform this aggregate against the group in the footer. So I tried to
> use the full previous function as described in the help
> "Previous(Expression, AggFunction, PreviousScope, AggScope)" but it
> basically fails as you've mentioned.
> Anyway, do you have a solution for me?
> I would like to sum my group values in the footer.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
>> Did you try the Previous aggregate function? Just place it into the group
>> header like =Previous(Fields!Country.Value) and it should work. Note: the
>> previous function has only one argument.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "SevDer" <sevder@.newsgroup.nospam> wrote in message
>> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>>
>

Perf Mon

Hi,
SQL Server 2005 sp1
The object General Statistics: User Connections has a value of 1400
The object General Statistics:Logical Connections has avalue of 1000
Does anyone know why there is a differenece in the values?
Thanks,
YanHi
Is this applicable?
http://support.microsoft.com/kb/922118
John
"Yan" <yaniv.etrogi@.gmail.com> wrote in message
news:eLbfLtibIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Hi,
> SQL Server 2005 sp1
>
> The object General Statistics: User Connections has a value of 1400
> The object General Statistics:Logical Connections has avalue of 1000
>
> Does anyone know why there is a differenece in the values?
>
> Thanks,
> Yan
>|||You may also want to check if this is occuring!
http://support.microsoft.com/kb/937745
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eNN0sgybIHA.4652@.TK2MSFTNGP06.phx.gbl...
> Hi
> Is this applicable?
> http://support.microsoft.com/kb/922118
> John
>
> "Yan" <yaniv.etrogi@.gmail.com> wrote in message
> news:eLbfLtibIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> SQL Server 2005 sp1
>>
>> The object General Statistics: User Connections has a value of 1400
>> The object General Statistics:Logical Connections has avalue of 1000
>>
>> Does anyone know why there is a differenece in the values?
>>
>> Thanks,
>> Yan
>

Perf Mon

Hi,
SQL Server 2005 sp1
The object General Statistics: User Connections has a value of 1400
The object General Statistics:Logical Connections has avalue of 1000
Does anyone know why there is a differenece in the values?
Thanks,
Yan
Hi
Is this applicable?
http://support.microsoft.com/kb/922118
John
"Yan" <yaniv.etrogi@.gmail.com> wrote in message
news:eLbfLtibIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Hi,
> SQL Server 2005 sp1
>
> The object General Statistics: User Connections has a value of 1400
> The object General Statistics:Logical Connections has avalue of 1000
>
> Does anyone know why there is a differenece in the values?
>
> Thanks,
> Yan
>
|||You may also want to check if this is occuring!
http://support.microsoft.com/kb/937745
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eNN0sgybIHA.4652@.TK2MSFTNGP06.phx.gbl...
> Hi
> Is this applicable?
> http://support.microsoft.com/kb/922118
> John
>
> "Yan" <yaniv.etrogi@.gmail.com> wrote in message
> news:eLbfLtibIHA.4196@.TK2MSFTNGP04.phx.gbl...
>

Percentages of subtotals

I have a matrix report with subtotals on the end of each row. I want to put
in brackets the percentage of the subtotal that each value is in the row. I
am not sure how to do this. Does anyone have any ideas?On Sep 27, 4:00 pm, "Fresno Bob" <nos...@.nospam.com> wrote:
> I have a matrix report with subtotals on the end of each row. I want to put
> in brackets the percentage of the subtotal that each value is in the row. I
> am not sure how to do this. Does anyone have any ideas?
You should be able to use an expression similar to the following:
=(Fields!SomeField.Value/Sum(Fields!SomeField.Value) ) * 100
-or- if this doesn't work due to automatic summing in the matrix
control, you might need to do something like this (I think):
=(Sum(Fields!SomeField.Value)/Total) * 100
For the brackets, you will need to select F4 (Properties Window) once
you have selected the field. In the Format section enter an expression
similar to the following:
="[#,.00%]"
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Percentage of occurrences of a value

I want to be able to retrieve the percentage that one value occurs in a field in a group. For example, a field that has either "Yes" or "No", if there are 3 "Yes" values and 1 "No" value (using mixed real and fake SQL..)

SELECT DATEPART(m,datefield), PECENTAGEOFAVALUE(YesNoField, 'Yes')
FROM sampletable
GROUP BY DATEPART(m,datefield)
Result
75

How can I do this? I know I can get the denominator by just doing a COUNT(*), but how do I count "Yes" only?

Thanks,
Kayda

Here it is..

Code Snippet

Create Table #data (

[Id] int ,

[Response] Char

);

Insert Into #data Values('1','Y');

Insert Into #data Values('2','N');

Insert Into #data Values('3','Y');

Insert Into #data Values('4','Y');

Insert Into #data Values('5','N');

Insert Into #data Values('6','N');

Insert Into #data Values('7','N');

Select

Isnull(Sum(Case When Response='Y' Then 1 End),0) / Sum(1.0) *100,

Isnull(Sum(Case When Response='N' Then 1 End),0) / Sum(1.0) *100

From

#Data

Code Snippet

--For Your query

SELECT

DATEPART(m,datefield)

Isnull(Sum(Case When YesNoField='Yes' Then 1 End),0) / Sum(1.0) * 100 PecentageofYes,

Isnull(Sum(Case When YesNoField='No' Then 1 End),0) / Sum(1.0) * 100 PecentageofNo

FROM

sampletable

GROUP BY

DATEPART(m,datefield)

|||

Here is an example:

Code Snippet

create table #t (

c1 datetime not null,

c2 char(1) not null

);

insert into #t values('20070101','y');

insert into #t values('20070115','n');

insert into #t values('20070125','y');

insert into #t values('20070205','y');

insert into #t values('20070217','n');

insert into #t values('20070225','n');

insert into #t values('20070227','n');

;with sums

as

(

select

convert(char(6), c1, 112) as ym,

sum(case when c2 = 'y' then 1 else 0 end) as sum_y,

sum(case when c2 = 'n' then 1 else 0 end) as sum_n,

count(*) over() as cnt

from

#t

group by

convert(char(6), c1, 112)

)

select

ym,

(sum_y * 100.00) / nullif(cnt, 0) as avg_y,

(sum_n * 100.00) / nullif(cnt, 0) as avg_n

from

sums

order by

ym

drop table #t

go

AMB

Percentage Difference Calculation!

I have a table as follows:
Year Month Value %Diff
2004 Jul 200
Aug 100
etc etc
2003 Jul 300
Aug 400
etc etc
I need to be able to calculate the % difference for each month compared to
the same month for the previous year. The Previous() function is close to
what I need but not close enough. Any help would be much appreciated?If you have control over the source query, a LEFT OUTER JOIN ... ON
a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
each row to calculate the diff. As you can see from this post and others,
my philosophy is not to jump through major coding hoops in the report if you
can easily solve it in the SQL query.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>I have a table as follows:
> Year Month Value %Diff
> 2004 Jul 200
> Aug 100
> etc etc
> 2003 Jul 300
> Aug 400
> etc etc
> I need to be able to calculate the % difference for each month compared to
> the same month for the previous year. The Previous() function is close to
> what I need but not close enough. Any help would be much appreciated?|||I should have mentioned in my SQL examples below that a and b are aliases
for the same table.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OZdTrAb0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> If you have control over the source query, a LEFT OUTER JOIN ... ON
> a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
> each row to calculate the diff. As you can see from this post and others,
> my philosophy is not to jump through major coding hoops in the report if
> you can easily solve it in the SQL query.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>>I have a table as follows:
>> Year Month Value %Diff
>> 2004 Jul 200
>> Aug 100
>> etc etc
>> 2003 Jul 300
>> Aug 400
>> etc etc
>> I need to be able to calculate the % difference for each month compared
>> to
>> the same month for the previous year. The Previous() function is close
>> to
>> what I need but not close enough. Any help would be much appreciated?
>

Percentage Column in Matrix

Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?

Thank you!

(Correct!)

store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC

10

10%

20

20%

70

70%

100

100%

25

50%

25

50%

50

100%

EURP C2 CHINA

(Wrong)

store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC

10

6%

20

13%

70

46%

100

25

16%

25

16%

50

EURP C2 CHINA

Can somebody help me or give me a hint? (or my question is not clear enough?)
If this kind of report is doable, I will ask my team member change their JSP report to MS RS.
Thank you!!