I have a long term need for a median function, I was wondering if anyone has or knows of some code or user defined functions somewhere that would do this. Ideally you could use it just liket the rest of the aggregate functions like AVG, etc.
I read the follow query about calculating median posted by Daivd Portaon 10/8/03.CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOTNULL)INSERT INTO SomeValues VALUES ('A',1)INSERT INTO SomeValues VALUES ('B',2)INSERT INTO SomeValues VALUES ('C',3)INSERT INTO SomeValues VALUES ('D',4)INSERT INTO SomeValues VALUES ('E',5)SELECT S1.valuex AS medianFROM SomeValues AS S1, SomeValues AS S2GROUP BY S1.valuexHAVING SUM(CASE WHEN S2.valuex <= S1.valuexTHEN 1 ELSE 0 END)[color=blue]>= ((COUNT(*) + 1) / 2)[/color]AND SUM(CASE WHEN S2.valuex >= S1.valuexTHEN 1 ELSE 0 END)[color=blue]>= (COUNT(*)/2 + 1)[/color]I have difficulty to understand the having clause. If S1 and S2 arethe same table, what it means by S2.valuex >= S1.valuex? Could somegive me a help?Also, if I have a table structured as:classID field1 field2 field3c1 1 2 3c1 4 5 6c1 7 8 9c2 9 8 7c2 6 5 4c2 3 2 1Is there a way to create a user-defined function that can get themedian for each field as easy as the average function. Such asselect distinct classID,median(field1),median(field2),median(field3)from [tablename]group by classIDThanks in advance
I have been trying to find a T-SQL function that would calculates a Median statistical value for me. I am runnnig on SS 2005? Any examples of using this function would be greatly appreciated.
I recently had to use my own little median technique again on a report here at work, and had posted it before, but wasn't sure if anyone had seen it. I have read Celko's and others techniques for generating a median and haven't seen one more efficent.
Does anyone have a better way they can think of? I think this bad boy is pretty short & efficient.
First, if you want to return the middle number or the higher one next to the middle if there is an even number:
SELECT x.Value AS median FROM Vals x CROSS JOIN Vals y GROUP BY x.Value HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0)
Change the " IN (1,0)" to "IN (-1,0)" to get the lower value if there is an even # of values.
Basically, we are saying compare each number to all possible numbers, and add up values of 1,0 or -1 depending if the first number is less, equal or higher than the second. The number that returns 0 is right in the middle ... If there is no middle, a -1 or 1 is returned. There will never be a 0 and (-1 or 1) at the same time returned.
To get the financial median (avg of the 2 values middle values if there is an even number), you need to encapsulate the results of the above into a subquery, allow for not just (-1,0) but all three (-1,0,1) and then take the AVG of the values returned.
That is,
SELECT Avg(Median) as Median FROM ( SELECT x.Value AS median FROM Vals x CROSS JOIN Vals y GROUP BY x.Value HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0,-1) ) A
If there is an even number of values, the lower and higher middle ones are averaged. If there is an odd number, only the middle value is returned and averaged (which of course has no effect).
Most other techniques used several COUNT(*) subqueries which this one avoids.
I have a table that contains the following: customer (ID of customers) product (Product description numeric value) UOM (unit of measure like each or pak) avgprice (Avg proce that this product and uom was sold)
I need to find the median value for a product, uom. Then I need create a table that shows product,uom,avgprice,median grouped by product and uom I have been at this for two days with no luck
Hi All, I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;
Here is my SQL, hope someone can modify it and thanks in advance.
Code:
SELECT DISTINCT instance, exec_time AS median FROM (SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time) sub_a ORDER BY 1 DESC) sub_1 UNION ALL SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time DESC) sub_b ORDER BY 1) sub_2)
I have looked all over the web to try to find some very basic / simple explanations of how to get a median value from a group of records in a table but with no luck
the problem i am having is that all the information i find is always centered around getting a median using every single row in the table. except i have groups of data in the table and want to work out a median for each group. the group is identified by 4 different columns (the 5th column is what i want to get the median on but for each group not the entire table) and i want to produce a resulting table that has 1 row for each group and therefore contains the median value for the group instead of the individual numbers that it currently has. e.g. the current table is like this
column1 column2 column3 column 4 column5(median of this)
value 1 value 2 value 3 value 4 1.2 value 1 value 2 value 3 value 4 1.0 value 1 value 2 value 3 value 4 1.5 value 2 value 3 value 4 value 5 0.2 value 2 value 3 value 4 value 5 0.4 etc...
and i need a query to get the results to show like this
column1 column2 column3 column 4 column5
value 1 value 2 value 3 value 4 1.0 value 2 value 3 value 4 value 5 0.3 etc...
This is driving me crazy and i will be very helpful if anyone can help
the statement i need to add it to is:
select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate, avg(srtot)as meansrtot, avg(sreff)as meansreff, avg(BFRaw)as BF_Rawmean, avg(BFTGV)as BF_TGVmean, count(srtot)as countsrtot, count(sreff)as countsreff from bodyparametersf where (srtot is not null) OR (sreff is not null) group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate
I need to calculate Median on each calculated result from the query below. There is one Median function available in SQL2K but it is not working. Can anyone help me in this regard.
------------------------------------------------------------------------ SELECT INS.Code As [code],INS.FinYr as [YEAR], ' ' As [FIRE BUSINESS], (INSRev.FI_NetPremLessIns / INSRev.FI_GrPremium) * 100 As [Rention Ratio], (INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100 As [Claim Ratio], ((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpenses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100 As [Expense Ratio], ((INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100)+(((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpe nses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100) As [Combine Ratio], (INSRev.FI_ClosingBal/INSRev.FI_NetClaimPaid) As [Unexpired Risk Reserve to Net Claim(x)],(INSRev.FI_MgmtExpenses/INSRev.FI_AdjNetPremium)*100 As [Management Expenses to Adj. Net Premium], (INSRev.FI_AgencyCommPaid/INSRev.FI_AdjNetPremium)*100 As [Agency Commissioned to Adj. Net Premium]
FROM (InsuranceGen As INS LEFT JOIN INSURANCEGen As INS1 ON (INS1.FinYr=INS.FinYr-1 AND INS.Code=INS1.Code)) LEFT JOIN INSRevGen as INSRev ON (INS.Code=INSRev.Code AND INS.FinYr=INSRev.FinYr) WHERE INS.Code IN ('ABC1','ABC2','ABC3') AND INS.FinYr=2005 ORDER BY INS.Code, INS.FinYr ----------------------------------------------------------------------
Using SQL Server 2005, I have the following query to calculate the median sales of each quarter over the past 5 years:
WITH CompMedian AS
(
SELECT SoldDate, SoldPrice, ROW_NUMBER() OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) ORDER BY SoldPrice) AS RowNum, COUNT(*) OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))) AS Cnt FROM tbl_Orders WHERE Status = 'Sold'
SELECT Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) AS CompDate, AVG(SoldPrice) AS CompMedian
FROM CompMedian
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
GROUP BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))
ORDER BY CompDate;
Now my client would like me to change the query so that each quarter would represent the median for the past 12 months ending with that quarter. I've been looking at this for hours and I'm at a loss. Anyone have any thoughts?
I am converting a report created using Crystal Reports 10 to Reporting Services. The report contains a list of items with dollar values. The original report displays both the Average and Median value. I can easily ( using avg(Field1.Value!) ) determine the average but cannot find a function to determine the median.
I used the Median() function in a calculated measure in a cube. All seemed well until one of my users, a statistician, pointed out that the displayed values were incorrect. I investigated this and finally built the simplest cube with three records and the displayed median does not make sense, i.e. equal what a median value should be.
The 3 records have loan amounts as a measure with these values: $102,500, $168,400, and $172,181 and loan number keys of 1, 2, and 3. That's it.
The median should be $168,400 since the number of items is odd and that is the middle value.
But the cube calculated measure displays $170,290.50. It appears to be taking the average of the middle row and the next value.
When I increase the number of records I get the same odd behavior but it not only alternates (of course) with odd and even (since they use different formulats for odd and even) but the odd wrong results alternate within themselves. The apparent calculations for 5 or 7 records are different compared to 3, 9, 11, 13, 15, 17 records. The first set seems to be calculating odd number of items median by averaging the middle value + the value BEFORE the middle value while the second set of odd rows (3, 9, etc) seem to be calculating median by averaging the middle value + the value AFTER the middel value. The even numbers result in the larger of the two middle values being selected instead of the average (financial median) or the lower number (as one poster claimed for statistical median).
I'm using custom code in an expression to calculate the median of a column. It works fine up to a point. Like if there are 35 rows in the result set (or up to some number) but when it gets bigger results, like 42 rows or more it doesn't work, the median is -1. This is the custom code I'm using (found online).
I don't see anything limiting the count, but it comes back with a -1 median so I think that means the count is not > 0.
I have a column in the report called arrival_to_complete which is like: 53 min
I create a column with expression: =MAX(Code.AddValue(Val(Fields!Arrival_to_complete.Value))) to fill the array using just the number part of the column. Then in the report I have 'Median =' <expression>, where the expression is: =Code.GetMedian() I run the report with 2 parameters a begin date and an end date. I don't see where any of this should be limiting the median calculation so I don't get why it works sometimes and not other times.
Dim values As New System.Collections.ArrayList Function AddValue(newValue As Decimal) If values is Nothing Then values = New System.Collections.ArrayList End If values.Add(newValue)
I am trying to calculate the median value using one of the measures and a dimension value.
Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:
opid time median
1 55
2 23
3 23
Total 23
The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.
What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.
I am using a calculated field Median with the following expression.
I need to calculate a median on a column in a table. The code I have is:
Code: Select gender, CASE when gender = 'F' then 'Female' when gender = 'M' then 'Male' else 'Unknown' end as test, datediff(day, [admit_date], getdate()) as 'datediffcal', from [tbl_record] How do I calculate the median on the datediffcal column?
It doesn't matter if the resultset only shows the median result. So if the output shows:
The table has 5 columns. They are S1,S2,S3,S4 and M
I need the best way to calculate the MEDIAN for each row and store it in M. i.e. M=median(S1,S2,S3,S4)
I've come across articles where they've calculated medians on table rows, not table columns. But my requirement involves table columns. I guess transposing the columns into rows and then calculating median should be possible, but if I do that for 10000 rows using a cursor, then it would take a loooooong time.
============================ ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]
@StartDate date ,@EndDate date as --== Check if count is even or odd declare @modulo int select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate ) --=== Get Median
[Code] ....
My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?
I attach also a sample of the view
============== ALTER PROCEDURE [dbo].[sp_ED_Measures] @StartDate date, @EndDate date, @Hospital varchar(5) AS BEGIN SET NOCOUNT ON;
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages. Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String) Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType) Dim Connection As New SqlConnection(ConnectionString) Dim Command As New SqlCommand(QueryString, Connection) Command.Connection.Open() Return Command.ExecuteReader()End Function It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader). Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!
When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
hi i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting.. chakri
I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Incorrect syntax near '.'.
Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476
SELECT dv.product , dv.itemname , dv.u_vlgx_plc, dv.shorted , dv.onhand , dv.po_num , t10.docduedate FROM (SELECT t3.product , t7.itemname , t2.u_vlgx_plc, t3.shorted , t4.onhand , t6.cardname AS t6_cardname, MIN( CASE WHEN t8.linestatus = 'O' THEN t9.docnum ELSE NULL END) po_num FROM (SELECT t0.product product , SUM( CASE WHEN t0.qty_topick <> t0.qty_picked THEN t0.qty_topick - t0.qty_picked ELSE 0 END) shorted FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date GROUP BY t0.product ) t3 INNER JOIN comparison.dbo.vlgxplc t2 ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS LEFT JOIN (SELECT t0.product AS product, SUM(t0.quantity) AS onhand FROM rbeacon.dbo.binlocat t0 GROUP BY t0.product ) t4 ON t3.product = t4.product INNER JOIN wbau.dbo.oitm t5 ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.ocrd t6 ON t5.cardcode = t6.cardcode INNER JOIN wbau.dbo.oitm t7 ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.por1 t8 ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.opor t9 ON t8.docentry = t9.docentry WHERE t3.shorted <> 0 GROUP BY t3.product , t7.itemname , t2.u_vlgx_plc, t3.shorted , t4.onhand , t6.cardname ) dv
OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10
GROUP BY dv.product , dv.itemname , dv.u_vlgx_plc , dv.shorted , dv.onhand , t10.docduedate, dv.po_num , dv.t6_cardname ORDER BY dv.u_vlgx_plc, dv.t6_cardname, dv.product
I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
I have created the following query... and need to get the total records display for my report. I have tried adding in the count(*) function to my select list, but I get errors. Any help is appreciated.