Calculate Median using Sql

Epicor ERP 10
, ,
1

Hi , I came across this solution that I thought is worth sharing maybe you guys come up or find a different solution. to calculate the median. Please let me know what was your approach. I went this way cause I have little experience with SSRS and I am not sure how to implement this using groups and group variables.

Thanks a lot.

Here is the article that I found

And here is how I use it. Basically I need to get the Median for each shop drawer and each priority (Green,Blue and With) given a period of time.

select
[ShopDrawingKPI].[OrderDtl_Company] as [ShopDrawingKPI_OrderDtl_Company],
[ShopDrawingKPI].[OrderDtl_OrderNum] as [ShopDrawingKPI_OrderDtl_OrderNum],
[ShopDrawingKPI].[OrderDtl_OrderLine] as [ShopDrawingKPI_OrderDtl_OrderLine],
[ShopDrawingKPI].[OrderDtl_PartNum] as [ShopDrawingKPI_OrderDtl_PartNum],
[ShopDrawingKPI].[OrderDtl_LineDesc] as [ShopDrawingKPI_OrderDtl_LineDesc],
[ShopDrawingKPI].[SysUserFile_UserID] as [ShopDrawingKPI_SysUserFile_UserID],
[ShopDrawingKPI].[SysUserFile_Name] as [ShopDrawingKPI_SysUserFile_Name],
[ShopDrawingKPI].[UDCodes_CodeID] as [ShopDrawingKPI_UDCodes_CodeID],
[ShopDrawingKPI].[UDCodes_CodeDesc] as [ShopDrawingKPI_UDCodes_CodeDesc],
[ShopDrawingKPI].[ReasonUDCodes_CodeID] as [ShopDrawingKPI_ReasonUDCodes_CodeID],
[ShopDrawingKPI].[ReasonUDCodes_CodeDesc] as [ShopDrawingKPI_ReasonUDCodes_CodeDesc],
[ShopDrawingKPI].[PriorityUDCodes_CodeID] as [ShopDrawingKPI_PriorityUDCodes_CodeID],
[ShopDrawingKPI].[PriorityUDCodes_CodeDesc] as [ShopDrawingKPI_PriorityUDCodes_CodeDesc],
[ShopDrawingKPI].[OrderRel_ReqShopDrawingDate_c] as [ShopDrawingKPI_OrderRel_ReqShopDrawingDate_c],
[ShopDrawingKPI].[OrderRel_ApprovedByEngDate_c] as [ShopDrawingKPI_OrderRel_ApprovedByEngDate_c],
[ShopDrawingKPI].[Calculated_RecordType] as [ShopDrawingKPI_Calculated_RecordType],
[ShopDrawingKPI].[Calculated_ReqSDvsApprEng_DayDiff] as [ShopDrawingKPI_Calculated_ReqSDvsApprEng_DayDiff],
(PERCENTILE_CONT(0.5) within group(order by ShopDrawingKPI.Calculated_ReqSDvsApprEng_DayDiff)
over (partition by ShopDrawingKPI.SysUserFile_Name,ShopDrawingKPI.PriorityUDCodes_CodeID)) as [Calculated_Median]
from dbo.ShopDrawingKPI as ShopDrawingKPI

2

Do you need to do this for a built-in report or a BAQ report?

Must it be don in SSRS? Or do you have access to the BAQ?

3

can be done in three stages
http://www1.udel.edu/evelyn/SQL-Class3/SQL3_Stat.html

2 Likes
4

Its an external BAQ and it could be done in crystal but we are moving towards SSRS cause Epicor won’t support it in further version, that’s what i heard.