比较两个日期的行的SQL百分比

Question

我有包含行的表incident

TICKETID   ACTUALFINISH          TARGETFINISH
100      2012-03-01 11:11:11    2012-02-01 11:11:11 
101      2012-03-01 11:11:11    2012-01-01 11:11:11 
102      2012-04-01 11:11:11    2012-06-01 11:11:11 
103      2012-05-01 11:11:11    2012-07-01 11:11:11 
104        null                        null

我想拥有目标完成大于实际完成且相反的行的百分比。

SLA    PERCENTAGE
YES    50
NO     50

with HELPTABLE as
    (select count(*) as Total

from incident as incident4

where incident4.targetfinish is not null and incident4.actualfinish is not null )


select distinct  case when incident.targetfinish>incident.actualfinish  then 
                dec((( select count(*)

 from incident as incident1

 where  incident1.targetfinish is not null and incident1.actualfinish is not null  )),10,2)/HELPTABLE.Total*100

 when incident.targetfinish<incident.actualfinish  then 

                dec(((select count(*)

from incident as incident2

where incident2.targetfinish is not null and incident2.actualfinish is not null  )),10,2)/HELPTABLE.Total*100
                    end as Percentage,

        case when incident.targetfinish>incident.actualfinish then 'Yes'
             when incident.targetfinish<incident.actualfinish then 'No'
        end as SLA



from incident

where  incident.targetfinish is not null and incident.actualfinish is not null

如果有人知道什么是错误，谢谢！

[错误代码：-206,SQL状态:42703]DB2 SQL错误：sqlcode=-206,sqlstate=42703,sqlerrmc=helptable.total,driver=3.57.82)

Answer 1

select 'YES' as SLA, 
(SUM(case when targetfinish > actualfinish  then 1.0 else 0.0 end) / count(*) ) * 100 as PERCENTAGE 
from incident 
where targetfinish is not null and actualfinish is not null
union 
select 'NO' as SLA, 
(SUM(case when targetfinish <= actualfinish  then 1.0 else 0.0 end) / count(*) ) * 100 as PERCENTAGE 
from incident
where targetfinish is not null and actualfinish is not null

http://sqlfiddle.com/#！3/2 E903/18