我的应用程序中有一个活动表。它具有项目ID,活动ID,基准完成日期,实际完成日期,计划完成日期和延迟完成日期。
我想在每个日期列中获取项目每个星期的活动ID计数。然后,我想获取这些日期列中的每一个,并将它们相对绘制。
最终结果将是一条曲线,显示第二周的活动数量与第二周的实际活动数量,第二周的计划活动数量以及第二周的后期活动数量。
我可以这样做来显示数据...
SELECT ACTIVITY.PROJECTOBJECTID,
ACTIVITY.OBJECTID,
TRUNC(ACTIVITY.BASELINEFINISHDATE,'W')+7 AS BLFIN,
TRUNC(ACTIVITY.ACTUALFINISHDATE,'W')+7 AS AFIN,
TRUNC(ACTIVITY.FINISHDATE,'W')+7 AS FIN,
TRUNC(ACTIVITY.REMAININGLATEFINISHDATE,'W')+7 AS LFIN
FROM PXRPTUSER.ACTIVITY ACTIVITY
WHERE (ACTIVITY.PROJECTOBJECTID = :POID)
但是,现在我有四个日期列。理想情况下,我想要四个计数列和一个日期列。这是我必须使用的数据示例。
PID AID BLFIN AFIN FIN LFIN
39987 5874494 2/22/2015 2/22/2015 6/15/2015
39987 5874495 2/22/2015 2/22/2015 6/15/2015
39987 5874496 2/22/2015 2/22/2015 6/15/2015
39987 5874497 2/22/2015 2/22/2015 6/15/2015
39987 5874498 2/22/2015 2/22/2015 6/15/2015
39987 5874499 4/22/2015 4/22/2015 6/15/2015
39987 5874500 2/22/2015 2/22/2015 6/15/2015
39987 5874501 2/22/2015 2/22/2015 6/15/2015
39987 5874502 4/8/2015 4/8/2015 6/15/2015
39987 5874503 4/8/2015 4/8/2015 6/15/2015
39987 5874504 4/8/2015 4/8/2015 6/15/2015
39987 5874505 5/15/2015 5/15/2015 6/15/2015
39987 5874506 5/15/2015 5/15/2015 6/15/2015
39987 5874507 4/8/2015 4/8/2015 6/15/2015
39987 5874508 4/8/2015 4/8/2015 6/15/2015
39987 5874509 4/8/2015 4/8/2015 6/15/2015
39987 5874510 4/8/2015 4/8/2015 6/15/2015
39987 5874511 4/8/2015 4/8/2015 6/15/2015
39987 5874512 4/8/2015 4/8/2015 6/15/2015
39987 5874513 4/8/2015 4/8/2015 6/15/2015
39987 5874514 4/8/2015 4/8/2015 6/15/2015
39987 5874515 4/8/2015 4/8/2015 6/15/2015
39987 5874516 4/8/2015 4/8/2015 6/15/2015
39987 5874517 4/8/2015 4/8/2015 6/15/2015
39987 5874537 2/22/2015 2/22/2015 6/15/2015
39987 5874538 2/22/2015 2/22/2015 6/15/2015
39987 5874539 2/22/2015 2/22/2015 6/15/2015
39987 5874540 2/22/2015 2/22/2015 2/22/2015
39987 5874542 2/22/2015 2/22/2015 6/15/2015
39987 5874543 2/22/2015 2/22/2015 6/15/2015
39987 5874544 2/22/2015 2/22/2015 6/15/2015
39987 5874545 2/22/2015 2/22/2015 6/15/2015
39987 5874546 2/22/2015 2/22/2015 6/15/2015
39987 5874547 2/22/2015 2/22/2015 6/15/2015
39987 5874548 2/22/2015 2/22/2015 6/15/2015
39987 5874549 2/22/2015 2/22/2015 6/15/2015
39987 5874550 2/22/2015 2/22/2015 6/15/2015
39987 5874551 2/22/2015 2/22/2015 6/15/2015
39987 5874552 2/22/2015 2/22/2015 6/15/2015
39987 5874553 2/22/2015 2/22/2015 6/15/2015
39987 5874554 2/22/2015 2/22/2015 6/15/2015
下面的查询给出了描述的结果。WKN列是ISO标准中的星期数,您可能需要将其更改为其他名称。“ WW”。另外,如果您有不同年份的日期,则必须在查询中连接年份(在bf,af,pf和lf子查询中将year附加到wkn)。
with
bf as (select to_char(blfin, 'iw') wkn, count(aid) cnt
from activity group by to_char(blfin, 'iw')),
af as (select to_char(afin, 'iw') wkn, count(aid) cnt
from activity group by to_char(afin, 'iw')),
pf as (select to_char(fin, 'iw') wkn, count(aid) cnt
from activity group by to_char(fin, 'iw')),
lf as (select to_char(lfin, 'iw') wkn, count(aid) cnt
from activity group by to_char(lfin, 'iw'))
select wkn, nvl(bf.cnt, 0) as baseline, nvl(af.cnt, 0) as actual,
nvl(pf.cnt, 0) as planned, nvl(lf.cnt, 0) as late
from bf
full join af using (wkn)
full join pf using (wkn)
full join lf using (wkn)
where wkn is not null
order by wkn
结果:
WKN BASELINE ACTUAL PLANNED LATE
--- -------- -------- -------- --------
08 24 0 24 1
15 14 0 14 0
17 1 0 1 0
20 2 0 2 0
25 0 0 0 40
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句