MS SQL Server :: Scripts To Calculate Mean Stdevs Count And Percentile For Some Data

Two Count Queries - Calculate % Change In Number Of Orders

Mar 12, 2015

I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.

I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.

View 2 Replies View Related

Power Pivot :: Calculate Sales Quantity Since Last Inventory Count?

Sep 30, 2015

I have an inventory table with the following columns

[Item], [Quantity], [Date]

I have a sales table with the following columns

[Item], [Customer], [Quantity], [Date]

In addition to this I have a normal date table.

I want to create a measure that calculates

CurrentInventory:=LastInvenoryCountInTable-SalesQuantitySinceThen

I don't know where to start. Is this possible to do with DAX?

View 10 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

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

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) :: 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 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

How To Calculate Data Entry By Monthly

Mar 3, 2008

Hi, I'm using SQL 2005, and I want get the total of data for February as an example. My table table is like this

submitdt
-----------
01/02/2008
25/02/2008
29/02/2008
01/03/2008
02/03/2008

so total submit date for february is 3. Your idea is highly appreciated.thanks you so much

View 7 Replies View Related

Transact SQL :: Calculate Data Based On Position

Jun 4, 2015

I have a table "PrintArea" with below table structure.

The Details column have " " separated data.

CREATE TABLE [dbo].[PrintArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccessDate] [datetime] NULL,
[IPAddress] [varchar](16) NULL,
[SerialNum] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Details] [varchar](2048) NULL,
)

This is how the data lookis like.

INSERT [dbo].[PrintArea] ([ID], [AccessDate], [IPAddress], [SerialNum], [Description], [Details]) VALUES (104280, CAST(0x0000A28A00A88310 AS DateTime), N'70.89.39.161', N'InSign-f529c824-85a6-4a64-8c59-e9f786d718f3-YM2AS31367,BFEBFBFF000306A9', N'Printing', N'Headboard_Insert_4
1
1
7938
5579

[Code] ...

I want to calculate totalAreaPrinted based on below formula from Details column.

View 2 Replies View Related

T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.

Oct 12, 2006

Hi there,

if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.

Thanks very much,

Manny

View 2 Replies View Related

Nvarchar Fields With Time Data - Calculate Difference In Hours

Sep 19, 2014

I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.

View 3 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

How To Count A Data

Dec 29, 2014

my data is like this.

---------code----------------name
ACT706022774CCI0---------CCI
ACT706022774CCI0---------CCI
CDF892782322CCI4---------CCI
CDF892782322CCI4---------CCI
CDF892782322CCI4---------CCI

i want a query that show data like this.

---------code--------------------name
---------ACT ----------------------2
---------CDF-----------------------3

i want to count ACT code and cdf code

View 2 Replies View Related

Query For Data Count

Jul 3, 2014

I have a table having code

code
12345678
21345678
45789612
12345678
21345678
21345678
12347586

I want result on the basis of first 2 digit the repeatation of code is count as 1.

code count
12 2
21 1
45 1

View 4 Replies View Related

COUNT Depending On Data

Jul 20, 2005

Hello.I have one serious problem with COUNT in TSQL.I use MS SQL Server 2000.I would like to count rows depending on data in it.What I meat is:I have fields like:region | month | year | some_count |-------|-------|------|-------------|LA | 1 | 2003| 4 |LA | 2 | 2003| 2 |LA | 3 | 2003| 1 |LA | 4 | 2003| 6 |VV | 1 | 2003| 3 |VV | 2 | 2003| 7 |VV | 4 | 2003| 20 |VV | 6 | 2003| 3 |BB | 2 | 2002| 1 |etc...And what I would like to get from it is:region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...-------|----------|---------|-----------|----------|----------|-----------|--LA | 4 | 2 | 1 | 6 |0 | 0 | 0VV | 3 | 7 | 0 | 20 | 0| 3 | 0BB | 0 | 1 | 0 | 0 |0 | 0 | 0etc...Is it possible to do it in views? if yes then how?I could use temp tables for each month but it is not solution when I have 3yearsand more later then it will be round 40 temporary tables.Thanks for any response.Regards,Casper

View 3 Replies View Related

Acceptable Data Based On Count?

Jan 16, 2007

I have x amount of tests for a person on a particular day. There are criteria to invalidate a test. I can filter all the bad tests away, but now I need to only accept 2 valid tests per day, even if there are more tests. The tests are filtered according to person ID and then put in order by date. I need to accept the first 2 valid test based on the criteria and reject the others. Any suggestions

View 2 Replies View Related

Count Of Contacts - Data Subset?

Jan 29, 2014

I am trying to get counts of contacts.

So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?

But for some reason it's giving me counts of products.

select distinct
a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]
from vwContact c

[Code] .....

View 1 Replies View Related

Different Ways To Count Data Rows?

Aug 21, 2015

I want to know that how many ways we have to count the data in sql.

I know only one way..that is. using count keyword.. are their any ways to find out the other ways..

View 2 Replies View Related

Accumulating Data Derived From COUNT Functions

Nov 10, 2004

Hi all,

I'm in a situation where I need to accumulate values from several columns in each of the rows returned in a result set and to then display each accumulation value for each row at the end of each row.

For example, if I had the following...

------------------------------------------------------
name - car - van - bus - total
------------------------------------------------------
Terry - 55 - 34 - 12 - 101
John - 01 - 23 - 05 - 029
etc etc
------------------------------------------------------

Where 'total' column is an accumuliation of the 'car', 'van', and 'bus' columns. The values for the columns 'car', 'van', and 'bus' are all values that have been derived from COUNT function. So what I want is to somehow genarate the values for the 'total' column (car + van + bus).

Now I have tried the following SQL, but to no avail

Code:

SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(COUNT(pveh.name1) + SUM(COUNT(pveh.name2))
FROM planvehicles AS pveh

... and I also tried...

SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(Car + Van)
FROM planvehicles AS pveh

Thanks

Tryst

View 12 Replies View Related

Data Flow Transformation: Count And Sum The Column Value

Nov 8, 2007

Hi,

I have one data flow control. Source is SQL server and destination is flat file destination. I have one derived column placed in between these two. This functionality works fine. I would like to sum one column data and count total no. of columns and put it in global variable. How can I achieve it?

Thanks,

View 7 Replies View Related

Retrieve Columns And Display Count For Two Different Data Sources

Jan 19, 2012

I am using Query Writer (should be SQL 2005) and have included the following code.

The end result: -should retrieve columns and display the count for two different data sources that were added by personnel in a specific department.

Problem: results are returned but not accurate. The code below works just fine and returns the results for all spot buy orders added by personnel in the sales department. However, I want to add a column in the same report that also counts blanket orders from the exact same table added by personnel in the sales department. The database uses views (v) in lieu of dbo.

Parameters:
@Add_Date_From SMALLDATETIME='',
@Add_Date_To SMALLDATETIME='',
@Last_name VARCHAR(50)='',
@First_Name VARCHAR(50)=''

Query:
SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.PO_Type) AS 'Spot Buy Added'

[Code] ....

If I substitute COUNT(T4.PO_Type) AS 'Spot Buy Added' with COUNT(T4.PO_Type) AS 'Blanket Added' I also get accurate results for the blanket order. IE separately they work just fine. If I try to combine the two that is where the trouble begins.

What am I doing incorrectly when I try to add the criteria/code for the additional column to count the blanket orders?

View 9 Replies View Related

SQL Server 2014 :: Select Data And Count In View

May 26, 2015

How can I select data from a table and row counts from multiple tables in a view. For example:

Select * from Settings -- it gets 1 row only
Select count(*) from NewApps where Status = 'False'
Select count(*) from myUsers where Status = 'Pending'

I just want to get them all in 1 view...

View 1 Replies View Related

Data Access :: Count Number Of Times Value Changed

Jul 28, 2015

Some logic for calculating the count for changed values.

E.g. :--

ID      VAL
1 10
1 20
1 20
1 20
1 10
1 10
1 10
1 20
1 10

Here in the above table my value changed from 10 to 20 and so.., so Val column value changed 4 times, so I need the result like :

id      count
1         4

View 7 Replies View Related

Row Count In Flat File Data Source Using SSIS

Jan 29, 2008

Hi,

I am trying to impliment a SSIS package where data source is a Flat file(.csv) file and destination is a sql server database.

The problem is my data source a flat file which consists of thousands of rows which are manually entered, so there is always a chance that in some rows they may miss a column value while entering data which results in an error.

Example: My flat file has headers like Sln, Name, Age, Designation. While entering data they may miss age and type it as 1,aaa,Consultant,,

Using SSIS package i want to track all row number in the flat file where data is entered wrongly so that i can correct only that row instead of checking all rows each time when my SSIS package throughs an error. I want to get all the row numbers in a sql server database which are wrongly entered.

Any suggestions are sincerly apriciated.

Thanks in advance

Regards,
gcs.

View 11 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....

Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Design For Loading Data Without Knowlged Of Datatype Or Column Count

Jul 17, 2006

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?
Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?
Does this make sense? Surely other foplks have run into this....
My thanks!

View 1 Replies View Related