Project and Portfolio Management Practitioners Forum
cancel

closed requests within the current month

SOLVED
Go to solution
Highlighted
patrick-sa
Super Contributor.

closed requests within the current month

Hi,how do i formulate a query that returns closed requests within the current month.for instance ,the query below returns all the requests,except closed and cancelled ones.how do i structure it to return closed requests that have been closed within the month of july.
thanks

SELECT r.REQUEST_ID,
r.DESCRIPTION,
r.VISIBLE_PARAMETER3,
r.ASSIGNED_TO_NAME,
r.STATUS_NAME,
r.LAST_UPDATE_DATE
FROM KCRT_REQUESTS_V r,
KNTA_REFERENCES_V s,
PM_PROJECT_TYPES t
WHERE r.REQUEST_ID = s.TARGET_ID
AND r.REQUEST_TYPE_ID = t.RISK_REQUEST_TYPE_ID
AND s.SOURCE_ID = t.PROJECT_ID
AND r.STATUS_NAME!='Cancelled'
AND r.STATUS_NAME!='Closed'
AND r.BATCH_NUMBER = 1
7 REPLIES
Mahen M
Acclaimed Contributor.

Re: closed requests within the current month

Quick Solution, Even we can reduce the Query cost.

SELECT r.REQUEST_ID,
r.DESCRIPTION,
r.VISIBLE_PARAMETER3,
r.ASSIGNED_TO_NAME,
r.STATUS_NAME,
r.LAST_UPDATE_DATE
FROM PRODPITG.KCRT_REQUESTS_V r,
PRODPITG.KNTA_REFERENCES_V s,
PRODPITG.PM_PROJECT_TYPES t
WHERE r.REQUEST_ID = s.TARGET_ID
AND r.REQUEST_TYPE_ID = t.RISK_REQUEST_TYPE_ID
AND s.SOURCE_ID = t.PROJECT_ID
AND r.STATUS_NAME!='Cancelled'
AND r.STATUS_NAME!='Closed'
AND r.BATCH_NUMBER = 1

Union

SELECT r.REQUEST_ID,
r.DESCRIPTION,
r.VISIBLE_PARAMETER3,
r.ASSIGNED_TO_NAME,
r.STATUS_NAME,
r.LAST_UPDATE_DATE
FROM PRODPITG.KCRT_REQUESTS_V r,
PRODPITG.KNTA_REFERENCES_V s,
PRODPITG.PM_PROJECT_TYPES t
WHERE r.REQUEST_ID = s.TARGET_ID
AND r.REQUEST_TYPE_ID = t.RISK_REQUEST_TYPE_ID
AND s.SOURCE_ID = t.PROJECT_ID
AND r.STATUS_NAME='Closed'
And r.Last_update_date >= to_date('2009-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND r.BATCH_NUMBER = 1

Regards,
Mahendran M
patrick-sa
Super Contributor.

Re: closed requests within the current month

Thanks,my instance doesnt like PRODPITG.PM_PROJECT_TYPES t,moans with "table or view doesnt exist"-not a major problem for now-.how can i replace the And r.Last_update_date >= to_date('2009-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') with an automatic "month notifier" .i'm creating a custom report and i want it to run on demand without me having to input the start of every month.i'm looking for an equivalent of sysmonth-sysdate.so that if we get to august it automatically know we're in august and checks for requests that are And r.Last_update_date >= to_date('2009-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')??

thanks
patrick-sa
Super Contributor.

Re: closed requests within the current month

i'm running 7.5 sp3
Mahen M
Acclaimed Contributor.

Re: closed requests within the current month

So Sorry remove that PRODPITG.... that is my schema

Regards,
Mahendran M
Mahen M
Acclaimed Contributor.

Re: closed requests within the current month

SELECT add_months(last_day(sysdate),-1)+1 from dual

The above snippet may help.

Regards,
Mahendran M
Mahen M
Acclaimed Contributor.
Solution

Re: closed requests within the current month

Second part may be like this.

SELECT r.REQUEST_ID,
r.DESCRIPTION,
r.VISIBLE_PARAMETER3,
r.ASSIGNED_TO_NAME,
r.STATUS_NAME,
r.LAST_UPDATE_DATE
FROM PRODPITG.KCRT_REQUESTS_V r,
PRODPITG.KNTA_REFERENCES_V s,
PRODPITG.PM_PROJECT_TYPES t
WHERE r.REQUEST_ID = s.TARGET_ID
AND r.REQUEST_TYPE_ID = t.RISK_REQUEST_TYPE_ID
AND s.SOURCE_ID = t.PROJECT_ID
AND r.STATUS_NAME='Closed'
And r.Last_update_date >= (add_months(last_day(sysdate),-1)+1)
AND r.BATCH_NUMBER = 1

Regards,
Mahendran M
patrick-sa
Super Contributor.

Re: closed requests within the current month

many thanks.it works