1

Please help me in converting Excel formula:

F=MEDIAN(MOD(B2,1),$E$2,$D$2)

where B2 is Date value and E2 is Endtime and D2 is start Time. 

Example Date: 5/2/2016 22:35
Start Time: 12:00:00 AM 
End Time: 11:59:59 PM

I tried this but no correct output:

declare @dtColumn  datetime
declare @dtColumn1  time
set @dtColumn='5/2/2016  10:35:00 PM'
set @dtColumn1=CONVERT(char(8),CAST(AVG(CAST(@dtColumn AS FLOAT) - FLOOR(CAST(@dtColumn AS FLOAT))) AS datetime),108)
print @dtColumn1

I should get output as 0.94 from Excel formula. Please help me how to convert it to SQL Server query.

halfer
  • 18,701
  • 13
  • 79
  • 158
Mahesh
  • 25
  • 4
  • Result from formula= 0.94 – Mahesh Jul 06 '16 at 09:38
  • There is no standard median aggregation function in SQL Server. Read this: http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server – ttaaoossuu Jul 06 '16 at 09:41
  • I need query for Median and MOD in SQL for Date and start and end time as given in example – Mahesh Jul 06 '16 at 10:07
  • When you run the SQL, what result do you get? You've stated what you are aiming for, always state what you are getting currently as well. – halfer Jul 06 '16 at 10:26
  • Here I am want Turn around time calculation from above formula – Mahesh Aug 04 '16 at 10:32
  • Formula IF(NETWORKDAYS($BD$2,$BD$2,$BA$2:$BA$60),MEDIAN(MOD($BD$2,1),$BB$2,$BC$2),$BC$2)-MEDIAN(NETWORKDAYS(J2,J2,$BA$2:$BA$60)*MOD(J2,1),$BB$2,$BC$2) – Mahesh Aug 04 '16 at 10:32

0 Answers0