Group by date and find median of processing time

Question

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?

you need an aggregation function for the columns not in group by. did you try putting AVG(...) around datediff(...)? and maybe you need an alias for the convert(...) column and use that alias in group by. — maraca, Jul 21 '22 at 13:16
Which DBMS are you using (given your use of `[` and `]` I'm guessing Microsoft SQL Server)? — Mark Rotteveel, Jul 21 '22 at 13:53
SQL is the query language shared by dozens of database systems. In the future, please ensure you tag your DBMS ([tag:sql-server] in your case). When you add the SQL tag, you also get a popup that reminds you of this. — Mark Rotteveel, Jul 21 '22 at 16:22

score 0 · Answer 1 · answered Jul 21 '22 at 23:25

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

score 0 · Answer 2 · answered Jul 22 '22 at 06:55

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;

Group by date and find median of processing time

2 Answers2