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.
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.
(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. )
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!
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.
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.
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.
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,
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.
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
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
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
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
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?
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.
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?
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.
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
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!
