Group by date and find median of processing time-CodePudding

I select input date and output date from a database. I use a formula to indicate the processing time. Now, I would like the values to be grouped according to the date of receipt and the median of the processing time to be output for all grouped dates of receipt. Something like this:

The data I select:

input date | output date | processing time
2022-01-03 | 2022-01-03  | 0
2022-01-03 | 2022-01-06  | 3
2022-01-03 | 2022-01-11  | 8
2022-01-05 | 2022-01-10  | 5
2022-01-05 | 2022-01-15  | 10

The output I want:

input date | processing time
2022-01-03 | 3
2022-01-05 | 7.5

My SQL Code:

SELECT [received_date]
,CONVERT(date, [exported_on])
,DATEDIFF(day, [received_date], [exported_on]) AS processing_time
  FROM [request] WHERE YEAR (received_date) = 2022
  GROUP BY received_date, [exported_on]
  ORDER BY received_date

How can I do this? Do I need a temp table to do this, or can I modify my query?

CodePudding user response：

You could try using PERCENTILE_CONT

with cte as (
select input_date,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY processing_time) OVER(PARTITION BY input_date) as Median_Process_Time
FROM tableA
)

SELECT *
FROM cte
GROUP BY input_date, Median_Process_Time

db fiddle

Also you check check out the discussion here How to find the SQL medians for a grouping

CodePudding user response：

Here my solution. Thank you for your help.

SET NOCOUNT ON;
DECLARE @working TABLE(entry_date date, exit_date date, work_time int)

INSERT INTO @working
SELECT [received] AS date_of_entry
      ,CONVERT(date, [exported]) AS date_of_exit
      ,DATEDIFF(day, [received], [exported]) AS processing_time
  FROM [zsdt].[dbo].[antrag] WHERE YEAR([received]) = 2022 AND scanner_name IS NOT NULL AND exportiert_am IS NOT NULL AND NOT scanner_name = 'AP99'
  GROUP BY [received], [exported]
  ORDER BY [received] ASC

;WITH CTE AS
(   SELECT  entry_date,
            work_time, 
            [half1] = NTILE(2) OVER(PARTITION BY entry_date ORDER BY work_time), 
            [half2] = NTILE(2) OVER(PARTITION BY entry_date ORDER BY work_time DESC)
    FROM    @working
    WHERE   work_time IS NOT NULL
)
SELECT  entry_date,
        (MAX(CASE WHEN Half1 = 1 THEN work_time END)   
        MIN(CASE WHEN Half2 = 1 THEN work_time END)) / 2.0
FROM    CTE
GROUP BY entry_date;