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
Hi All,
I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?
For example
How to calculate 25th Percentile on Salary of these 1000 records?
Hope I am clear with my question.
Thanks in advance.
Regards,
qA
Any good way to add a percentile calculation to a table in sql server 2008 r2?
I've got 4 fields and I'd like to add a 5th labeling the records according to the percentile they fall in for one of the fields.
I have process and people want to know percentile rank (on scale of 100), I'm bit lost as I have cases when I have let say 5 observations, so if use code below I can not got above number of observations:
Is there any way to get that numbers like RankWanted ??
; WITH cte AS (
SELECT 100 Cust_ID , 0.338129 Perc, 20 RankWanted union
SELECT 300 Cust_ID , 0.487179 Perc, 40 RankWanted union
SELECT 300 Cust_ID, 0.500000 Perc, 60 RankWanted union
SELECT 400 Cust_ID, 0.541666 Perc, 80 RankWanted union
SELECT 500 Cust_ID, 0.548780 Perc, 100 RankWanted )
SELECT Cust_ID, Perc,
[code].....
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
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?
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?
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?
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.
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]);
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
How to create Percentile with MDX??
i got this function but i'm not success to follow up.
Percentile ( set, numeric_value_expr, percentile )
(set -> The set from which to get a tuple value.
numeric_value_expr -> A numeric value or an expression that returns a numeric value.
percentile -> A percentile. Must be between 0 and 100. )
Thank you
Hi, l would like to calculate 80% and 95% percentile of a column from my table in SQL server 2005. Does SQL server 2005 provides such built-in function, if not, how should l do? Any suggestion? (Suggest to MS : Please include percentile as a built-in function on the next services pack). Thanks and happy labor day!
View 6 Replies View RelatedSCORE ID PERCENTILE
0.500 1 ?
0.500 2 ?
0.450 3 ?
0.290 4 ?
0.540 5 ?
0.460 6 ?
I have SCORE and ID how can I calculate the percentile?
SCORE ID PERCENTILE
0.500 1 ?
0.500 2 ?
0.450 3 ?
0.290 4 0
0.540 5 100
0.460 6 ?
This table has a large number of rows
Hello All,
Does anyone know how to do an expression that calculate percentrank and percentiles? I have a table with a list of transactions and their response time. I need to find the 75th, 90th, 95th, and 98th percentile as well as the percentrank of <=5 seconds, 10 seconds, 30 seconds and 60 seconds.
Any help is appreciated!
I need to find patients(MRN) who are in the 95th percentile for BMI.
create table dbo.TEST
(
MRN varchar(10),
ResultValue varchar(10)
)
insert into dbo.TEST(MRN, BMI) values( '611193','25.63')
insert into dbo.TEST(MRN, BMI) values( '128845','16.93')
[Code] .....
I'm trying to create a calculated field that gives me the avg 75 percentile.
Right now I get this value by doing the following:
Create data set:
Select top 75 percent <field>
from <table>
Then I create the following calculated field
Avg(Fields!<field>.value,"<data_set_name>")
But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.
Is it possible?
Thanks!
I am stumped on a set-based approach for this one.
A cursor approach is straightforward enough, but i want to avoid that.
Here's my table:
create table StudentScores
(
id int primary key identity(1,1),
student_id int not null,
score int not null
)
with some sample data:
insert into StudentScores (student_id, score)
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 88 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 98
What I want is, for each student, what is their 90th percentile score?
For a given single student, one possibility would be:
declare @studentid int
set @studentid = 2
select top 1 @studentid as student_id, a.score as [90th percentile score]
from
(
select top 90 percent score from StudentScores
where student_id = @studentid order by score asc
) as a
order by a.score desc
But I want this for all students, and not use a cursor.
Any ideas?
Thanks!
I am writing some scripts to calculate mean (avg) stdevs count and percentile_cont for some data..I am partitioning over multiple columns e.g. partition by year,month, day order by value..I performing unnecessary overhead as I throw away all but the last (or max) value in each window. (I use cte then select max from cte).That is I only care about the avg of the window in its entirety not specific subwindows.
View 5 Replies View Related
Hi,
My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
My manager has handed me a task of converting an excel file she uses in to a web aplication.
While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.
The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).
So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
Since these calculations are required a produce a result in an online environment, what will be faster?
I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.
Any views that you can offer will be very helpful.
Thanks in advance.
PMA
I need to calculate the overall GPA for a student in a particular class.
YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000
The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.
Here's my sql:
select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA
from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725
order by
school.schname,
student,
grade,
class
Dimensions:
DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode
Period = 1
Product = 1
Status Code = InStock
Period = 1
Product = 2
Status Code = InStock
Period = 2
Product = 1
Status Code = OutOfStock
Period = 2
Product = 2
Status Code = InStock
In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)
The measure = 1 / 2 or 50%. TIA
This is a smple data in table1
sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...
I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value
select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1
thanks
My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.
There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 1 9-March-2008 Main
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)
A 1300
B 3000
C 400
So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)
A 1000
B 2600
C 400
So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
5 21-March-2008 SUB
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2
I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.
The design for creating the above sample tables is below
/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)
CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)
insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')
insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)*/
I´m working on SSRS 2005 trying to calculate YTD for the total sale. This is what I got so far:
Code Snippet
WITH MEMBER [Measures].[YTD Amount] AS
'SUM(PeriodsToDate([DATE].[Year]),[Amount])'
SELECT
{[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
[DATE].[Month].Members ON ROWS
FROM [SKY]
My Date hierarcy is:
Year
Month
When running this query all I get is very small number for Amount column, almost zero for all months and the YTD Amount Column is only showing (null).
Hello Friends
I have 3 tables
1) Product Id, ShortName
2) IncomingStockId, ProductId, Quantity, InDate
3) OutGoingStock Id, OutDate, ProductId, Quantity
I need to get the results like thisProduct name, quantity in stock today
the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent)
Thank you for your timeSara
Hi.
i have the code :
cmd = New SqlCommand("SELECT sales,country,year FROM salesTable WHERE (country = " & (CountryBlk) & " AND branch = " & (NameSnif) & " AND datepart(yyyy,year)=" & (YearBlk) & ") order by datepart(mm,year) ", cnn) cnn.Open() rdr = cmd.ExecuteReader( _ CommandBehavior.CloseConnection)
While x < 12 If rdr.Read = Nothing Then Exit While End If varcount(x) = rdr("sales")
TempMonth = rdr("year") MonthNumber(x)=datepart(DateInterval.Month,TempMonth) x = x + 1 End While
i want to calculate and put into the varcount(x) value all sales of the same month
thanks
I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.
View 4 Replies View Relatedhello everyone,
I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date,
and how do i find out the beginning date of the month and end date of the month for a certain date,
thanks
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.
I have scenerio that find out the Bandwitdh size between clint and server.
I wanted find out howmuch size of data recieveing from server at a time.
Any advice regarding this.
Thanks,
Ravi
HI,
I have the below logic for age calcuation for more than a year.
But I need age calculation for lessthan year.
Note for MAK: As per our previous post that day calucations didn't work.
DECLARE @birthday datetime, @d datetime
SELECT @birthday = '12/31/1998', @d = '12/30/1999'
SELECT datediff(yy, @birthday, @d) -
(case WHEN (datepart(m, @birthday) > datepart(m, @d)) OR
(datepart(m, @birthday) = datepart(m, @d) AND
datepart(d, @birthday) > datepart(d, @d))
THEN 1
ELSE 0
end) AS Age1
I need to use the first day of previous month, can anybody help me with that please?
Thanks in advance!!