Ever wondered what the hell my job is? Well, this morning I was at specification meeting, and this afternoon I started to rewrite the core of a SQL statement for a report. This statement gives me the “detail” numbers per project. What is missing now is aggregating these projects by sales-people and sales-region.
-- Projektleiter pro Projekt
SELECT PP.PROJECT_NR,
PP.PERSON_NR
FROM PROJECT_PERSON PP,
PROJECT P
WHERE P.PROJECT_NR = PP.PROJECT_NR (+)
AND PP.TYPE_UID (+) = 5123;
-- Purchase Order Date pro Projekt
SELECT PS.PROJECT_NR,
MIN(PS.STAGE_DATE)
FROM PROJECT_STAGE PS
WHERE PS.WEIGHT = 100
AND PS.STAGE_DATE >= TRUNC(SYSDATE,'YEAR') AND PS.STAGE_DATE < ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 12)
GROUP BY PS.PROJECT_NR;
-- Weight of the Current Stage pro Projekt
select p.project_nr, psc.weight
from project p, project_stage psc
and p.project_nr = psc.project_nr (+)
and p.stage_uid = psc.uc_uid (+)
-- Permission Fragment
AND ((#GLOBAL_MANAGEMENT_REPORTS# != 96800) OR (S.UC_UID IN (
SELECT REGION_UID
FROM PERSON_REGION
WHERE PERSON_NR = :ORS.nUserPersonNr)))
AND ((#GLOBAL_MANAGEMENT_REPORTS# != 84253) OR (P.PROJECT_NR IN (
SELECT PROJECT_NR
FROM COMPANY_PROJECT CP, COMPANY_PERSON COP
WHERE CP.COMPANY_NR = COP.COMPANY_NR
AND COP.PERSON_INTERN_NR = :ORS.nUserPersonNr)))
-- New statement, unweighted, months -- for projects only, no total at the end
SELECT P.PROJECT_NR,
P.CODE_NAME,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),0), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) JAN,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),1), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) FEB,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),2), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) MAR,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),3), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) APR,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),4), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) MAY,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),5), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) JUN,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),6), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) JUL,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),7), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) AUG,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),8), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) SEP,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),9), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) OCT,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),10), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) NOV,
SUM(DECODE(TO_CHAR(ps.stage_date, 'YYYY-MM'),
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),11), 'YYYY-MM'),
DECODE(W.WIN, NULL, NVL(L.PRICE, PT.POTENTIAL), W.PRICE),
0)) DEC,
SUM(DECODE(TRUNC(ps.stage_date, 'YEAR'),
TRUNC(SYSDATE,'YEAR'),
DECODE(MOD(TO_CHAR(SYSDATE,'MM'),
TO_CHAR(ps.stage_date,'MM')+1),
TO_NUMBER(TO_CHAR(SYSDATE,'MM')),
DECODE(W.WIN, NULL, L.PRICE, W.PRICE),
0),
PT.POTENTIAL)) CUR,
SUM(DECODE(TRUNC(ps.stage_date, 'YEAR'),
TRUNC(ADD_MONTHS(SYSDATE,12),'YEAR'),
DECODE(W.WIN, NULL, L.PRICE, W.PRICE),
PTN.POTENTIAL)) NXT
from project p,
project_turnover pt, project_turnover ptn,
(select s.project_nr project_nr, min(nvl(s.stage_date,s.planed_date)) stage_date
from project_stage s
where s.weight = 100
and nvl(s.stage_date,s.planed_date) >= trunc(sysdate,'YEAR')
and nvl(s.stage_date,s.planed_date) < add_months(trunc(sysdate,'YEAR'), 12)
group by s.project_nr) ps,
(select project_nr,
user_offer_nr,
1 win,
nvl(sum(trunc(nvl(op.price,0),2)*trunc(nvl(op.quantity,1),2)),0) price
from offer o, offered_product op
where op.offer_nr (+) = o.offer_nr
and winning_offer = 1
and o.offer_date >= trunc(sysdate,'YEAR') and o.offer_date < add_months(trunc(sysdate,'YEAR'),24)
and o.offer_date = (select max(offer_date)
from offer x
where x.project_nr = o.project_nr
and winning_offer = 1)
group by project_nr, user_offer_nr, offer_date) w,
(select project_nr,
user_offer_nr,
null win,
nvl(sum(trunc(nvl(op.price,0),2)*trunc(nvl(op.quantity,1),2)),0) price
from offer o, offered_product op
where op.offer_nr (+) = o.offer_nr
and o.offer_date >= trunc(sysdate,'YEAR') and o.offer_date < add_months(trunc(sysdate,'YEAR'),24)
and o.offer_date = (select max(offer_date)
from offer x
where x.project_nr = o.project_nr)
group by project_nr, user_offer_nr, offer_date) l
WHERE p.project_nr = w.project_nr (+)
and p.project_nr = l.project_nr (+)
and p.project_nr > 0
and p.project_nr = ps.project_nr (+)
and p.project_nr = pt.project_nr (+)
and pt.year (+) >= trunc(sysdate,'YEAR') and pt.year (+) < add_months(trunc(sysdate,'YEAR'),12)
and p.project_nr = ptn.project_nr (+)
and ptn.year (+) >= add_months(trunc(sysdate,'YEAR'),12) and ptn.year (+) < add_months(trunc(sysdate,'YEAR'),24)
AND P.PROJECT_NR = 515000000
group by P.PROJECT_NR,
P.CODE_NAME
/
-- TEST DATA
--> sql fragment for testing:
AND P.PROJECT_NR = 515000000
-- create potentials
delete from offered_product where offer_nr in (select offer_nr from offer where project_nr = 515000000);
delete from offer where project_nr = 515000000;
update project_stage set weight=0, planed_date=null where project_nr = 515000000;
delete from project_turnover where project_nr = 515000000;
insert into project_turnover (project_nr, year, turnover, potential)
values (515000000, trunc(sysdate,'YEAR'), 30000, 40000);
insert into project_turnover (project_nr, year, turnover, potential)
values (515000000, trunc(add_months(sysdate,12),'YEAR'), 35000, 45000);
--> now CUR should be 40000 and NXT should be 45000
-- create a non-winning offer for this year
insert into offer (offer_nr, company_nr, user_offer_nr,
status_uid, payment_type_uid, payment_cond_uid,
project_nr, offer_date, reg_date)
values (offer_seq.nextval, 521000001, 'Alex0',
0, 0, 0,
515000000, sysdate, sysdate);
insert into offered_product (offer_nr, product_uid, quantity, price)
values(offer_seq.currval, 6401, 3, 333);
insert into offered_product (offer_nr, product_uid, quantity, price)
values(offer_seq.currval, 6402, 6, 333);
--> since the project stage is not set, this should still return 0 for the current month!
-- create project stages
select * from project_stage where project_nr = 515000000;
update project_stage set weight = 50, planed_date = sysdate + 10 where project_nr = 515000000 and uc_uid = 1059;
update project_stage set weight = 60, planed_date = add_months(sysdate,1) + 10 where project_nr = 515000000 and uc_uid = 1060;
update project_stage set weight = 100, planed_date = add_months(sysdate,2) + 10 where project_nr = 515000000 and uc_uid = 1061;
--> now the data for the current month +2 and for CUR should be 3*999=2997
update project_stage set planed_date = add_months(planed_date,-3) where project_nr = 515000000;
--> now the data for the previous month should be 3*999=2997, but for CUR it should be 0, because it is in the past
update project_stage set stage_date = add_months(planed_date,3) where project_nr = 515000000 and weight = 100;
--> now the data for the current month +2 should be 2997 again, because the reached date is better than planned date
update project_stage set stage_date = add_months(planed_date,-12) where project_nr = 515000000 and weight = 100;
--> now the stage is not in the current year, therefore the potential should be shown again: 40000
update project_stage set stage_date = add_months(planed_date,1) where project_nr = 515000000 and weight = 100;
-- create a non-winning offer for this year
insert into offer (offer_nr, company_nr, user_offer_nr,
status_uid, payment_type_uid, payment_cond_uid,
project_nr, offer_date, reg_date)
values (offer_seq.nextval, 521000001, 'Alex1',
0, 0, 0,
515000000, sysdate+1, sysdate);
insert into offered_product (offer_nr, product_uid, quantity, price)
values(offer_seq.currval, 6401, 3, 111);
insert into offered_product (offer_nr, product_uid, quantity, price)
values(offer_seq.currval, 6402, 6, 111);
--> this is a newer offer, so the data should show 9*111=999 for MAR and CUR
update offer set winning_offer = 1
where user_offer_nr = 'Alex0';
--> now the old offer is a winning offer and the data should show 2997 for MAR and CUR
-- subselects for debugging
select project_nr,
user_offer_nr,
null win,
nvl(sum(trunc(nvl(op.price,0),2)*trunc(nvl(op.quantity,1),2)),0) price
from offer o, offered_product op
where op.offer_nr (+) = o.offer_nr
and o.offer_date >= trunc(sysdate,'YEAR') and o.offer_date < add_months(trunc(sysdate,'YEAR'),24)
and o.offer_date = (select max(offer_date)
from offer x
where x.project_nr = o.project_nr)
group by project_nr, user_offer_nr, offer_date;
--> returns 515000000, 2997
select s.project_nr project_nr, min(nvl(s.stage_date,s.planed_date)) stage_date
from project_stage s
where s.weight = 100
and nvl(s.stage_date,s.planed_date) >= trunc(sysdate,'YEAR')
and nvl(s.stage_date,s.planed_date) < add_months(trunc(sysdate,'YEAR'), 12)
and s.project_nr = 515000000
group by s.project_nr;
#Software
(Please contact me if you want to remove your comment.)
⁂
man look at those subselects
– karl 2005-03-08 06:59 UTC
---
hmm and another comment here.. thinking of moving from usemod to your odd software 😄
– karl 2005-03-08 07:00 UTC
---
Hehe... Oddmuse rocks. ;) (not being particularly impartial, here...)
– Alex Schroeder 2005-03-08 08:34 UTC