Median Calculation

Transact-SQL
1

Hi Everyone,

I am a newbie to SQL and I hope anyone could help me with the following queries. I have attached a sample table.

  1. Calculate a median visit duration in hours broken down by the months.
  2. Calculate a median visit duration difference assessment centre broken down by year/month.
    Please find the table and my queries below:
    DECLARE @CustomerBase TABLE(ID int
    ,CustomerRef varchar(255)
    ,AssessmentCtrCode int
    ,AssessmentCenter varchar(255)
    ,AppointmentStart Datetime
    ,Appointmentend Datetime
    ,VisitDuration float
    )

INSERT INTO @CustomerBase
VALUES
(1,'938c0edc2bdd30a26920784505f409ca',11025, 'Remseck','2017-03-12 13:11:00.000','2017-03-12 16:39:00.000', 3.47) ,
(2,'83b4309c8139de2049e9d88153d836d7',11025 ,'Remseck','2016-11-29 15:01:00.000','2016-11-29 18:57:00.000',3.93) ,
(3,'19ade720684228cc0ff6271c08bf510c',11025,'Remseck','2016-06-09 08:21:00.000','2016-06-09 10:48:00.000', 2.45) ,
(4,'adb9811f04b9607ae298261093bc2e3e',11025,'Remseck','2015-06-28 09:58:00.000','2015-06-28 12:26:00.000', 2.47),
(5,'de3b1c4ceab5e6edfcc68ff8c5e764e5',11025,'Remseck','2017-11-14 15:13:00.000','2017-11-14 19:39:00.000', 4.43),

(6,'a7e54ab56d396ecb6189e08e40a2dd80',11027,'Stuttgart','2017-09-28 11:43:00.000','2017-09-28 16:50:00.000', 5.12) ,
(7,'0e94f195950a4d32ca13a42a4c497baf',11027 ,'Stuttgart','2017-06-08 09:52:00.000','2017-06-08 14:28:00.000',4.60) ,
(8,'ceb063487c1e23083ab80e5d138eb54f',11027,'Stuttgart','2017-12-08 14:55:00.000','2017-12-08 19:34:00.000', 5.65) ,
(9,'e9ee32358afb91159ae2929f14263658',11027,'Stuttgart','2018-04-21 15:13:00.000','2018-04-21 20:00:00.000', 4.78),
(10,'d40c6f27b8ce464815ce1c4c5f291360',11027,'Stuttgart','2018-01-11 12:55:00.000','2018-01-11 17:28:00.000', 4.55)

SELECT ID
,CustomerRef
,AssessmentCtrCode
,AssessmentCenter
,AppointmentStart
,Appointmentend
,VisitDuration
,FORMAT (AppointmentStart , 'yyyy-MM' ) AS [YYYYMM]
,datepart(year,AppointmentStart) AS [Year]
,datename(Month,AppointmentStart) AS [Month]
,'' AS [Median Visit Duration In Hours Broken Down By Months]
,'' AS [Median Visit Duration Difference between Assessmentcentre broken down by year/month]
FROM @CustomerBase

Any help will be apprciated.
Thanks
Angel

2

Welcome Angel.

how do you get 3.47 for ID = 1

2017-03-12 13:11:00.000 2017-03-12 16:39:00.000

Adding a little extra few minutes to overcharge :slight_smile: Is this center owned by Bernie Madoff?

3

Hi and thanks for your prompt reply.
The 3.47 was calculated as the duration in hours between AppointmentStart and AppointmentEnd
,cast(DATEDIFF(second, AppointmentStart, AppointmentEnd) / 3600.0 as DECIMAL(10, 2)) AS DurationInHrsFraction

Thanks
Angel

4 
'2017-03-12 13:11:00.000'
'2017-03-12 16:39:00.000'

3 hours 28 minutes. Or do I need some black coffee with no sugar?

5

Need more coffee :slight_smile: The value is converted to a decimal and so you get 3.47 hours. If the difference was 3 hours and 30 minutes you would get 3.5 hours, 3 hours 45 minutes would be 3.75 hours, etc...

6

so it's a Bernie Madoff trick?

'2017-03-12 13:11:00.000'
'2017-03-12 16:39:00.000'

If I just do a count on my hands and toes I see 3 hours 28 minutes. pouring cup #3

7 
Select 28 / 60.0

Gives us 0.466666 which is rounded to 0.47 (decimal value). Might need a full pot...:slight_smile:

8

covfefe bath

9

To the request - check out these references: https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-cont-transact-sql?view=sql-server-ver15

10

Hi Yosiasz

Thanks. I am not a supper SQL hence I needed help from people such as you and others. It appears you are taking meek of me. Please, I need solution if you can so that I can learn and become like you. There is no need for all of that.

Thanks
Angel

11

Hi Angel

Please Relax ..

There is something called "Learning How to Learn"

Any Tom Dick and Harry Can become Expert of Experts
Very Very Very Quickly

And if they so Wish can become Geniuses ( 1000 times better than experts )

ALL within very short span of time .. ( Even Overnight )

By .. Copying the ways of DeepMind Alpha Zero is one example

Another thought You might be driving a BMW ..or might be very good at lots of things which i have no clue about ( SQL does not mean jack .. sh**)

12

Most Importantly

You can Do it by Yourself
Without any help from ANYBODY