我有一个oracle查询要在下面计数百分比:
select to_char(ltrim(round((1 - n / c) * 100) || '%')) as total
from (select count(*) c
from wa_sew_tbl_emp_info
where status = 'Attend'
and shift = case
when to_char(sysdate, 'HH24:MI') >= '07:00'
and to_char(sysdate, 'HH24:MI') <= '19:29' then
'Morning'
when to_char(sysdate, 'HH24:MI') >= '19:30'
and to_char(sysdate, 'HH24:MI') <= '23:59' then
'Night'
when to_char(sysdate, 'HH24:MI') >= '00:00'
and to_char(sysdate, 'HH24:MI') <= '06:59' then
'Night'
end)
,(select count(*) n
from (select s.badgeid_fk
from wa_sew_tbl_emp_info s
,wa_ga_tbl_employees e
where s.badgeid_fk = e.badgeid
and s.status = 'Attend'
and s.shift = case
when to_char(sysdate, 'HH24:MI') >= '07:00'
and to_char(sysdate, 'HH24:MI') <= '19:29' then
'Morning'
when to_char(sysdate, 'HH24:MI') >= '19:30'
and to_char(sysdate, 'HH24:MI') <= '23:59' then
'Night'
when to_char(sysdate, 'HH24:MI') >= '00:00'
and to_char(sysdate, 'HH24:MI') <= '06:59' then
'Night'
end
and s.badgeid_fk not in
(select empid
from wa_sew_tbl_results
where system_date between to_date(case
when to_char(sysdate, 'HH24:MI') >= '07:00'
and to_char(sysdate, 'HH24:MI') <= '19:29' then
to_char(sysdate, 'YYYY-MM-DD') || ' 07:00'
when to_char(sysdate, 'HH24:MI') >= '19:30'
and to_char(sysdate, 'HH24:MI') <= '23:59' then
to_char(sysdate, 'YYYY-MM-DD') || ' 19:30'
when to_char(sysdate, 'HH24:MI') >= '00:00'
and to_char(sysdate, 'HH24:MI') <= '06:59' then
to_char(sysdate, 'YYYY-MM-DD') || ' 00:00'
end
,'YYYY-MM-DD HH24:MI')
and to_date(case
when to_char(sysdate, 'HH24:MI') >= '07:00'
and to_char(sysdate, 'HH24:MI') <= '19:29' then
to_char(sysdate, 'YYYY-MM-DD') || ' 19:29'
when to_char(sysdate, 'HH24:MI') >= '19:30'
and to_char(sysdate, 'HH24:MI') <= '23:59' then
to_char(sysdate, 'YYYY-MM-DD') || ' 23:59'
when to_char(sysdate, 'HH24:MI') >= '00:00'
and to_char(sysdate, 'HH24:MI') <= '06:59' then
to_char(sysdate, 'YYYY-MM-DD') || ' 06:59'
end
,'YYYY-MM-DD HH24:MI'))))
该查询功能是获得百分比。
您可以看到上述代码,日期将根据今天的日期和时间范围自动设置。
日期时间范围查询:
'07:00' - '19:29'好
'19:30' - '23:59'OK
现在,当我们准时时:00.00-06.59,我想要基于:
的查询'19:30' - '23:59',日期和
'00:00' - '06:59'今天的日期。
您可以参考此小提琴
在系统日期/时间基于代码的问题是,一旦您设置它,它就会变得多余。我认为您的sqlfiddle返回0%,因为它返回0%。
此外,还不清楚您在问什么。您能否使用以下内容来表达您对下表所示的每个" s.ysdate"值的期望。请注意,从查询中提取Y,Y2,X1,X2的列。
在SQL小提琴
上可用with S as (
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss') as ysdate from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 1/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 2/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 3/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 4/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 5/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 6/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 7/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 8/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+ 9/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+10/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+11/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+12/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+13/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+14/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+15/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+16/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+17/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+18/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+19/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+20/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+21/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+22/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+23/24 from dual union all
select to_date('2017-10-15 07:01:00','yyyy-mm-dd hh24:mi:ss')+24/24 from dual
)
SELECT
s.ysdate "s.ysdate"
, case
when to_char(s.ysdate, 'HH24:MI') >= '07:00'
and to_char(s.ysdate, 'HH24:MI') <= '19:29' then
'Morning'
when to_char(s.ysdate, 'HH24:MI') >= '19:30'
and to_char(s.ysdate, 'HH24:MI') <= '23:59' then
'Night'
when to_char(s.ysdate, 'HH24:MI') >= '00:00'
and to_char(s.ysdate, 'HH24:MI') <= '06:59' then
'Night'
end
as y
, case
when to_char(s.ysdate, 'HH24:MI') >= '07:00'
and to_char(s.ysdate, 'HH24:MI') <= '19:29' then
'Morning'
when to_char(s.ysdate, 'HH24:MI') >= '19:30'
and to_char(s.ysdate, 'HH24:MI') <= '23:59' then
'Night'
when to_char(s.ysdate, 'HH24:MI') >= '00:00'
and to_char(s.ysdate, 'HH24:MI') <= '06:59' then
'Night'
end
y2
, to_date(case
when to_char(s.ysdate, 'HH24:MI') >= '07:00'
and to_char(s.ysdate, 'HH24:MI') <= '19:29' then
to_char(s.ysdate, 'YYYY-MM-DD') || ' 07:00'
when to_char(s.ysdate, 'HH24:MI') >= '19:30'
and to_char(s.ysdate, 'HH24:MI') <= '23:59' then
to_char(s.ysdate, 'YYYY-MM-DD') || ' 19:30'
when to_char(s.ysdate, 'HH24:MI') >= '00:00'
and to_char(s.ysdate, 'HH24:MI') <= '06:59' then
to_char(s.ysdate, 'YYYY-MM-DD') || ' 00:00'
end
,'YYYY-MM-DD HH24:MI')
as x1
, to_date(case
when to_char(s.ysdate, 'HH24:MI') >= '07:00'
and to_char(s.ysdate, 'HH24:MI') <= '19:29' then
to_char(s.ysdate, 'YYYY-MM-DD') || ' 19:29'
when to_char(s.ysdate, 'HH24:MI') >= '19:30'
and to_char(s.ysdate, 'HH24:MI') <= '23:59' then
to_char(s.ysdate, 'YYYY-MM-DD') || ' 23:59'
when to_char(s.ysdate, 'HH24:MI') >= '00:00'
and to_char(s.ysdate, 'HH24:MI') <= '06:59' then
to_char(s.ysdate, 'YYYY-MM-DD') || ' 06:59'
end
,'YYYY-MM-DD HH24:MI')
as x2
from s
order by 1
结果:
| s.ysdate | Y | Y2 | X1 | X2 |
|----------------------|---------|---------|----------------------|----------------------|
| 2017-10-15T07:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T08:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T09:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T10:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T11:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T12:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T13:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T14:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T15:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T16:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T17:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T18:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T19:01:00Z | Morning | Morning | 2017-10-15T07:00:00Z | 2017-10-15T19:29:00Z |
| 2017-10-15T20:01:00Z | Night | Night | 2017-10-15T19:30:00Z | 2017-10-15T23:59:00Z |
| 2017-10-15T21:01:00Z | Night | Night | 2017-10-15T19:30:00Z | 2017-10-15T23:59:00Z |
| 2017-10-15T22:01:00Z | Night | Night | 2017-10-15T19:30:00Z | 2017-10-15T23:59:00Z |
| 2017-10-15T23:01:00Z | Night | Night | 2017-10-15T19:30:00Z | 2017-10-15T23:59:00Z |
| 2017-10-16T00:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T01:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T02:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T03:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T04:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T05:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T06:01:00Z | Night | Night | 2017-10-16T00:00:00Z | 2017-10-16T06:59:00Z |
| 2017-10-16T07:01:00Z | Morning | Morning | 2017-10-16T07:00:00Z | 2017-10-16T19:29:00Z |