T-SQL (SS2K8) :: Calculation For Each Value By A Percentage Using Round

Jul 23, 2014

I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values ​​for the percentage (also using round).

IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo
Create Table #Redondeo (Orden int Identity(1,1),Valores money)
Insert into #Redondeo
Select 71374.24 Union Select 16455.92
Union Select 56454.20 Union Select 9495.18

[Code] ...

RESULT: 177673.74; 37311.49

View 6 Replies


ADVERTISEMENT

Percentage Calculation Of Column

Aug 27, 2013

I have a column Bill_amount from bill_details table , i want to find 100%,30% of that column.

View 2 Replies View Related

Percentage Calculation In An Expression

Sep 20, 2007

I have a dataset named Diagnosis_Code_Analysis which has as part of the SQL query:
COUNT(DISTINCT IntOOS) AS Cases which is a simple case count. In my tabular report I have a column with the following expression: =Sum(Fields!Cases.Value) and this works fine.

Now, my next column I want to see the percentage of the whole for each case and I am using the following expression:
=Sum(Fields!Cases.Value)/Sum(Fields!Cases.Value, "Diagnosis_Code_Analysis")

My results are always 100% for each case (line item). I have tried changing the format to P and even 0.00% but it still reports 100%.

Can someone give me a better method to produce this percentage?

View 1 Replies View Related

Reporting Services :: Row Percentage Calculation In SSRS 2008 R2

May 11, 2015

I have a matrix in one of my SSRS report data is like below

Row as Branch
Column as Tasks (Progress, Completed , cancelled, In Complete)

Branch   Progress  Completed  cancelled  In Complete    Total    %(Complete/Total)
Aus           1             2               3                 4                    9                      22.2%
NZ            1             2               3                5                    11                      18.88%
US           0              0               3                 3               6                          0.00%

How can do in the SSRS matrix %(Completed/Total) percentage calculation ?

View 4 Replies View Related

Transact SQL :: Update Incoming Rows Based On Percentage Calculation

Apr 2, 2015

This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.

Requirements:

1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.

2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"

System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade Amount 

SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $      25,68,458.15

[Code] .....

View 36 Replies View Related

SQL Server 2008 :: Update Incoming Rows Based On Percentage Calculation

Mar 13, 2015

I have below records coming in from source files

ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S

In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.

I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.

ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT

The order of the records coming in can be different order, they need not be subsequent.

This is happening in SQl Server 2008.

View 3 Replies View Related

SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage

Dec 4, 2014

I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.

I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?

select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id

id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00

View 5 Replies View Related

T-SQL (SS2K8) :: Round Value To The Nearest

Sep 16, 2014

I have a column called as NDM$ What I want do it round it the nearest value example I am giving below

34.100->34%

39.8->40

35.4->35 some thing like that.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Round A Date To The Nearest Second

Sep 8, 2014

how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.

Also, the solution should not be too cumbersome because it will be used in a high volume environment.

View 5 Replies View Related

T-SQL (SS2K8) :: Difference In Seconds To Round To Minute?

May 20, 2014

I'm trying to get the difference between the two times in minutes with the seconds rounded up or down

As an example the difference in the times:

1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,

using

select
cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))

I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?

View 5 Replies View Related

T-SQL (SS2K8) :: Rounding Off A Percentage Result?

Apr 30, 2014

how to remove the trailing chars from the end of a percentage calculation.

For instance:

SELECT
CAST(NULLIF(t.SLAHours,0) - t.TotalDownTime as REAL) /
CAST(NULLIF(t.SLAHours,0) as REAL) * 100 as [%Availability]
FROM DBName.dbo.TableName t

This gets me '99.00294' (example)

I may have gone about this totally the wrong way - but I just want to trim the unwanted chars from the end - I've managed to trim some off by using REAL but I only need the basic percentage number.

Both source columns are INT data type.

View 8 Replies View Related

T-SQL (SS2K8) :: How To Find And Add Column Of Percentage In Table

Mar 16, 2015

i made a procedure to the below output now i need to add column percentage depends on these output

block Response Heads Dept Actuals
1Sales Sales01.Sales (net of Sales Tax)1087.5999999999999
1Finance Sales02.LESS:-EXCISE DUTY 22.800000000000001
1Sales Sales03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60000000000002
3Materials & productionCOGS 05.Material Cost 376.70000000000005

(.i.e)

block Response Heads Dept Actuals percentage
2HR HR 04.Personnel Cost 170.60 170.60*100/1064.8(dept=03.Net
Sales)
3Materials & productionCOGS 05.Material Cost 376.70 376.70*100/1064.8(dept=03.Net
Sales)

How to write make a query for that?

View 4 Replies View Related

T-SQL (SS2K8) :: Percentile Calculation For Each Value

Dec 29, 2014

I am using sql server 2008 to calculate the percentile value for each column.

--Script
CREATE TABLE #Table (Score Varchar(4),Percentile int)
INSERT INTO #Table (Score)
VALUES
('80'),('55'),('125'),('99'),('75'),('130'),('37'),('73'),('151')

select * from #Table

I would like the result to be shown in 25,50,75,100 percentile values . Here is the example

Score | percentile
80 | 75
37 | 25
151 | 100
75 | 50

View 9 Replies View Related

T-SQL (SS2K8) :: Time Calculation From Datetime

Oct 29, 2014

I am trying to produce a report that will show a duration in minutes of a time when a room was occupied for a category. Whilst I have the start and end dates and times, the end user must be able to specify not only a range of dates, but a start and endtime of hours in the day in which they are interested in (it will be applied to all days in the range - they are not allowed to specify a different start/endtime per day).

The example I have is a date range of 6 to 17 October, but they only want the times from 09:00 to 21:00, so if a room was occupied from 08:00 to 11:00 they would only want to know the duration as 120 minutes (09:00 to 11:00) not 180.

The data is supplied by a third party, and duration in minutes is supplied, but it is not much use when they are not interested in the 'real' duration.

CREATE TABLE [dbo].[Evts](
[Location_name] [nvarchar](200) NULL,
[Event_Category] [nvarchar](500) NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Duration] [int] NULL

[Code] ....

---but the expected output is

Location_nameEvent_Categoryduration
RS8CM240
RS8OD480 -- eliminating 08:00 - 09:00 on 17th
RS8OOT300 -- eliminating 08:00 - 09:00 on 13th
RS8SCC150
RS8SODT1740
RS8 SODT180
RS8SODTB60
RS8SODTNT180
RS8TR60

Looking for info regarding to the minute calculation when the start time specified by the user differs from that of the actual startime?

View 2 Replies View Related

T-SQL (SS2K8) :: Calculation Of Total Discount With Variable

Feb 2, 2015

I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount. I thought to have something like:

DECLARE @Disc float
SET @Disc = 0
SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100
FROM UserDiscounts
WHERE UserID = 123

but, seems, it does not work.

Looking for single query without any loops?

View 4 Replies View Related

T-SQL (SS2K8) :: Varying Calculation For Fixed Number Of Fields

Aug 19, 2014

I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.

Fields Used: PERIOD ('MON-yyyy') and VALUE

The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))

This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?

View 1 Replies View Related

T-SQL (SS2K8) :: Calculation - Get Records Based On 100 / 80 / 60 / 40 Miles Radius

Feb 9, 2015

I have zip code database and i am trying to get the records based on 100/80/60/40 miles radius. Below the sample structure of my table

Create table ZipCodes(ID bigint primary key identity(1,1),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50),Latitude float,Longitude float)If i pass the Zipcode as '10021'

I need to get zipcodes and other details of my table which is 20 mile radius circle.

I Google through and got couple of article explains about using Haversine Formula. Also, following link has a function to calculate the distance.

View 9 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)

Jan 30, 2006

Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt

View 3 Replies View Related

Round Up

Sep 14, 2006

X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL? 

View 1 Replies View Related

Round Up And Round Down In Sql

Sep 9, 2007

 I want to do a simple thing but it seems to be behaving not as i am expectingI want to round number either up or down....e.g: 4.3 should round to 4  4.7 should round to 5when i use the round function like this:  83/17=4.88round( 83/17 , 0 ) it gives the answer as 4....when i was expecting it to be 5.... i know there is a ceiling function...but depending on the value of the division sometimes i want it to round up and sometimes round down. how can i do this? hope this makes sense. thanks  

View 3 Replies View Related

Round Bug

Mar 25, 1999

Has anyone been experiencing problems with rounding to 2 decimal places in SQL 7? I have a bunch of queries that generate web reports. Under 6.5 everything was fine. Now under 7.0 any number that needs to be rounded to 2 decimals is actually giving me several decimal places.

Here is a simplified version of what I am doing:
DECLARE @x real
DECLARE @y real
SELECT @x = 223.1
SELECT @y = 59.7

SELECT ROUND((@x/@y),2)

Result should be 3.74. But instead I am getting 3.7400000000000002

If anybody has heard if Microsoft has declared this as a known bug please let me know.

Thanks for your time.

View 1 Replies View Related

Round ?

Aug 22, 2002

I have the statement below which I use in an update

select (round(sum(tottime/60),2)) as ttime from vw_cms_suptime
where vw_cms_suptime.[tracking number] = 970
tracking.[tracking number]

Even though I have the round the statement returns a value
of 5.0000000000000003E-2

Is there something wrong with the round?

View 1 Replies View Related

Round The Value

Apr 17, 2008

Hi,

I need to display the value of a certain varible rounded to two digits after decimal.

For example :

value of a is 1346.8500

I need to get the value of a as 1346.85

Please help me

View 4 Replies View Related

Round To Near Value

Jan 4, 2007

hi,i have one criteria

if if i have time as 1:15 min means it has to show 1:15,if i have 1:20 min it has too be rounded and it has to show 1:30 min.
can any one give me query for this
thanks in anvance

View 3 Replies View Related

Round Off

May 14, 2007

How to round off the value .579 into .6 using round function.i tried doing it but in vain.

View 3 Replies View Related

Round Off

Jul 30, 2007

how do i round off value 0.23 to 0.2.
i am using this inside a scalar function and the return type is numeric(6,2).
so how do i get 0.2 and not 0.23

View 4 Replies View Related

How Do I Round The Value ?

Aug 10, 2007

Hi

I have datacloum called 'price' (float) and using the below code in my stored procedure

cast(price * 1.175 as decimal(19, 2)) as [item-price]

I am getting the price as (ex1: 23.58, ex2: 114.25, ....etc)

So How do I round the value(price) to (ex1: 23.99, ex2: 114.99)

Advance thanks

View 3 Replies View Related

Round Off

Aug 23, 2007

i want to round off 0.23 to 0.25 ,i am trying it with round function but can't get it.

View 3 Replies View Related

How ROUND?

Jul 20, 2005

Hi,i need to round:3° decimal between 1 and 5 LOW3° decimal between 6 and 9 UPThanks

View 1 Replies View Related

Round

Oct 10, 2007



There is a filed in a table of type decimal(12, 8)

when running a select query on this table for this field i.e.
select field1 from table1

the data is shown like:
102.12500000
104.12500000

And therefore the report shows the same figure.

How do I get this field to show up in the report as 4 decimal places. i.e.
102.1250
104.1250

Tried the properties of the cell to format the text into a number but there is only 2 decimal places there.
Even tried the expression by using Rnd(field1). This does not seem to do what I am after.


Thanks

View 1 Replies View Related

Round A Number

Jun 26, 2000

How do I write sql syntax to round the last digit. For example, 12.152 should be 12.15. My current sql syntax does not round the last digit as follows:

Select tblARInvoiceDetail.UnitPrice * tblARInvoiceDetail.Quantity AS ChargeBeforeDiscount
FROM tblARInvoices INNER JOIN
tblARInvoiceDetail ON
tblARInvoices.ARInvoiceID = tblARInvoiceDetail.ARInvoiceID


The tblARInvoiceDetail.UnitPrice column is numeric;length 9;precision 18
The Quantity column is integer;length 4; precision 10;scale 0

Any Suggestions?
Thanks,
Denise

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved