I've got a piece of code (stored procedure) written in T-SQL that inserts into a table several data from a temporal table as shown here:
INSERT INTO [myschema].[CalculatedData]( Year, Month, Line, Car, Service, Route, Trip, Stop, Qty, CalculatedMean ) SELECT Year, Month, Line, Car, Service, Route, Trip, Stop, COUNT(*), AVG(Duration), FROM @TableToCalculate GROUP BY Year, Month, Line, Car, Service, Route, Trip, Stop;
As you can see, I calculate the average (mean) of all the values. Easy, considering the built-in function.
Now, what I want to do is to calculate the median. There isn't any function to calculate it, but as the median is equal to calculating the 50th percentile, this can be used. But I don't know how to do it.
Any help, please?