T-SQL (SS2K8) :: Percentile With NTILE(100) For Less Than 100 Observations - How To Display On 100 Scale

Sep 15, 2015

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].....

View 2 Replies


ADVERTISEMENT

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) :: Display Numbers 0-9

Aug 8, 2014

I am having query where i am calulating datediff value, but i want to display resultser as below:

if datediff = 4 then resultset would be
0
1
2
3
4

if datediff = 9 then
0
1
2
3
4
5
6
7
8
9

how can i implement this?

View 2 Replies View Related

T-SQL (SS2K8) :: Display Dates For A Given Value?

Mar 4, 2015

I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:

Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14

I want to display the following:

Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20

Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.

View 2 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:

TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445

OUTPUT

ProductIDSeqNoBalance
111510
121445

View 3 Replies View Related

Ntile By Date

Nov 6, 2006

Hello all

Given:
SELECT orderdate, salespersonid ,
ntile(5) OVER (ORDER BY t1count DESC) AS crank
FROM test2

How do I get it to rank each sales person's (salespersonid) t1count within each orderdate ?

Thanks

View 1 Replies View Related

BCP,CTE And NTILE Problem?

May 12, 2006

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:



DECLARE @FileName varchar(50),

@bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1



SET @FileName = REPLACE('D:Test9205'+'.txt','/','-')

SET @bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -U -P -c'

EXEC master..xp_cmdshell @bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.



If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

View 1 Replies View Related

T-SQL (SS2K8) :: Combine Two Tables To Display Iteratively

May 13, 2014

I am looking for a way to combine the following two tables to get a result set that would look like this:

1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red

[Code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Display Result In Required Order

Jun 17, 2014

I have an issue while display the result in the required order. How to get the required output.

Code :

USE tempdb
GO

IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL
DROP TABLE #VersionFormat_tbl
CREATE TABLE #VersionFormat_tbl
(
[FormatID] [smallint] NOT NULL,
[Description] [varchar](50) NULL,

[Code] ....

Present output :
fileExtension FormatID Description fileExtension versionFormatTypeId
txt 1 Text txt 1
html 2 HTML html 1
xml 3 XML xml 1
pdf 4 PDF pdf 1
xls 5 Excel xls 1
doc 6 Word doc 1

Required output:
fileExtensionFormatIDDescriptionfileExtensionversionFormatTypeId
html2HTMLhtml1
xls5Excelxls1
doc6Worddoc1
pdf4PDFpdf1
txt1Texttxt1
xml3XMLxml1

View 5 Replies View Related

T-SQL (SS2K8) :: Display Values Up To 1 Decimal Without Function?

Sep 11, 2014

I am having values as below:

99.87
99.96
8.67

And my output should be as:

99.8
99.9
8.6

How can I do this

View 9 Replies View Related

T-SQL (SS2K8) :: Query To Display All Data Connected To ID In One Row

Oct 23, 2014

I have a table that I am trying to query to display all data connected to a id in one row. What is the best way to achieve this?

Something like this:

ID| CRS1 | CRS2 | CRS3| CRS4 ....
1 | A101 | A102 | A103 | A104 ....

CREATE TABLE Class
(
id int,
yr int,
trm varchar(2),
crs varchar(30)

[Code] .....

View 3 Replies View Related

T-SQL (SS2K8) :: Find Records In A String And Display

Jul 6, 2015

I am having a column which is ntext and contains below type of data.

{ "running":"true", "all":{ }, "GPAs" : [ {"type":"item", "alias":"i_11111"} ,"GPA":"1.75" }, {"element": {"type":"item", "alias":"i_11111"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_33333"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_44444"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_22222"} ,"GPA":"1.42" }, {"element": {"type":"item", "alias":"i_55555"} ,"GPA":"15" }, {"element": {"type":"item", "alias":"i_66666"} ,"GPA":"10" } ]}

above is a single row. There are many such rows are present in the table. Now I want to find all alias and GPA and display as below.

idGPA

111111.75
111111.43
333331.43
444441.43

In oracle it can be done by using REGEXP_SUBSTR. But how can we do this in SQL?

View 1 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?

Sep 18, 2015

I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:

OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C

So the logic is as follows:

I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.

Not sure how to create my Select here since the values are in the same table

View 2 Replies View Related

T-SQL (SS2K8) :: How To Display Stored Procedure Output In Html Table Format

Mar 16, 2014

i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.

View 2 Replies View Related

SQL Server 2012 :: Incorporate RANK Or NTILE With LEAD Function?

Jan 9, 2014

We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:

if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go

-- Create temp tables for sample data

create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)

-- Insert some sample data

insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')

-- Use lead function to determine how many months are between

-- consecutive dates per account

; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval

where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.

-- Show the results

select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis

Results (this is not getting me what I'm looking for):

RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122

[code]....

This is what I'm trying to achieve:

RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114

[code]....

The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.

I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.

View 5 Replies View Related

Integration Services :: Assigning Records To Groups In SSIS Like NTILE?

Aug 18, 2015

I am in middle of my transformation where I have to assign records equally among 3 different groups. I can do that in SQL using NTILE() Over() function. How do I do that in SSIS package. I have applied different business rules during transformation to get unique records and now I have to assign those records to 3 group in and generate excel report.Basically, I will need to have another column which will have those group numbers.

View 6 Replies View Related

Percentile With MDX ???

Dec 14, 2007

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

View 5 Replies View Related

Percentile

Apr 28, 2006

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 Related

PERCENTILE

Jan 13, 2008

SCORE 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

View 1 Replies View Related

Percentile Calculation

Oct 11, 2005

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

View 7 Replies View Related

Percentile And PercentRank

Jun 20, 2007

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!

View 8 Replies View Related

Find 95th Percentile For BMI

Mar 3, 2015

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] .....

View 9 Replies View Related

Create A Calculated Field That Gives Me The Avg 75 Percentile

Mar 22, 2007

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!

 

 

 

 

View 9 Replies View Related

Find 90th Percentile Scores For Each Student

Sep 15, 2006

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!

View 6 Replies View Related

T-SQL (SS2K8) :: Display Row As 2 Days Ago / 1 Hours 34 Minutes Ago / 11 Minutes Ago

Apr 21, 2015

My table as data as follow,

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))

[code].....

View 6 Replies View Related

Scripts To Calculate Mean Stdevs Count And Percentile For Some Data

Jul 26, 2015

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

SQL Server 2008 :: Adding Percentile Calculation To A Table

Sep 16, 2015

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.

View 4 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Precision And Scale?

Nov 8, 2004

Hello,

I have been trying to develop an automatic way of programmatically accessing datasources and performing some predefined(-supported) processing on them.

The question I would like to ask you people has to do with numeric fields. What exactly is precision? Is it the maximum length in digits of a field, or is there more to it? What about a "field's scale", what is it and how does it affect a field's value handling?

Thank you very much,

Dave

View 1 Replies View Related

Bar Chart Scale

Sep 7, 2007



Can you have different scales on a bar chart. I want to chart sales and quantity. Oracle lets you label the top of the bar chart as money and the bottom as quantity.

I am dividing my sales by one million and quantity by one thousand to make them similar in size on the same chart. I am using the sum of the sales/1000000 as a point label. Is there any way to limit the number of decimal places displayed. Currently it is displaying something like 1.94889312043; 1.95M would be better.

Thank you.

View 2 Replies View Related

How Do I Get It To A Precisions Scale Of .00?

Aug 6, 2007



I have set the output columns to decimal and data scale of 2. And have also set the field to be 0.00, and in the csv desination file it always puts .000000, How can I get it to be 0.00?

Thanks you for the help

View 4 Replies View Related

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related







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