如何从日期列计算百分比 [英] how to calculate percentage from date columns

问题描述

请在下表中找到，

有注册时间 - 当机票被报告时

resolution_deadline当机票应该被解决时

resolution_time当票证被解决时

resolution_elapsetime - 时间刻度从reg_time开始直到解决方案



这里我们需要知道特定事件是否已经越过截止日期为75％和100％



如果是75％和否则分别是

please find below table ,
have reg time -- when the ticket was reported
resolution_deadline when the ticket should be resolved
resolution_time when the ticket was resolved
resolution_elapsetime --time ticks start from reg_time until resolution

Here we need to know whether the particular incident has crossed the deadline when 75% and 100%

say yes if 75% and No respectively

reg_time          resolution_deadline resolution_elapsetime  resolution_time  Ticket_id
3/25/15  5:18 AM  4/1/15  9:18 AM     203                    3/25/15 8:41 AM  10
3/25/15 12:39 PM  4/2/15  8:30 AM     120                    3/26/15 6:30 AM  11
3/27/15  8:07 AM  4/3/15 12:07 PM     240                    3/27/15 12:07 PM 12
3/31/15  5:56 AM  4/7/15  9:56 AM     394                    3/31/15 2:12 PM  13
3/31/15  5:58 AM  4/7/15  9:58 AM     420                    4/1/15  4:58 AM  14
3/31/15  6:04 AM  4/7/15 10:04 AM     59                     3/31/15 7:03 AM  15
3/31/15  6:05 AM  4/7/15 10:05 AM     57                     3/31/15 7:02 AM  16

实际输出

Actual Output

reg_time    resolution_deadline resolution_elapsetime   resolution_time Ticket_id   75% 100%
3/25/15 5:18 AM 4/1/15 9:18 AM  203 3/25/15 8:41 AM 10  NO  NO
3/25/15 12:39 PM    3/26/15 5:30 AM 120 3/26/15 6:30 AM 11  NO  YES
3/27/15 8:07 AM 4/3/15 12:07 PM 240 3/27/15 12:07 PM    12
3/31/15 5:56 AM 4/7/15 9:56 AM  394 3/31/15 2:12 PM 13
3/31/15 5:58 AM 4/7/15 9:58 AM  420 4/1/15 4:58 AM  14
3/31/15 6:04 AM 4/7/15 10:04 AM 59  3/31/15 7:03 AM 15
3/31/15 6:05 AM 4/7/15 10:05 AM 57  3/31/15 7:02 AM 16

推荐答案

检查出来

Check this out

select 
	 reg_time, 
	 resolution_deadline, 
	 resolution_elapsetime,   
	 resolution_time, 
	 Ticket_id, 
     datediff(minute,reg_time,resolution_time) as [minutes to solve],
	 datediff(minute,reg_time,resolution_deadline) as [max minutes allowed],
	 (cast(datediff(minute,reg_time,resolution_time) as float) /cast( datediff(minute,reg_time,resolution_deadline) as float)) *100 as [percent to solve],
	 case when (cast(datediff(minute,reg_time,resolution_time) as float) /cast( datediff(minute,reg_time,resolution_deadline) as float)) > 75 then 'Yes' else 'No' end as [75%],
	 case when (cast(datediff(minute,reg_time,resolution_time) as float) /cast( datediff(minute,reg_time,resolution_deadline) as float)) > 100 then 'Yes' else 'No' end as [100%]

我包含了选择中的步骤，这样你就可以看到发生了什么。



希望有所帮助^ _ ^

Andy

I included the steps in the select so you can see what's going on.

Hope that helps ^_^
Andy

这篇关于如何从日期列计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！