Thanks Martti,
I am thinking i am close...
SELECT av.prj_object_id rid , SUM(av.slice) avail , to_char(tp.START_DATE,'mm/dd/yyyy') ||'-'||to_char(tp.FINISH_DATE,'mm/dd/yyyy') AS timperiod FROM PRJ_BLB_SLICES av JOIN PRJ_BLB_SLICEREQUESTS SR ON AV.SLICE_REQUEST_ID = SR.ID JOIN ( SELECT tp.prid, min(tp.PRSTART) START_DATE, max(tp.PRFINISH-1) FINISH_DATE FROM PRTIMEPERIOD tp WHERE (1=1) AND tp.PRSTART >= '1-AUG-20' AND tp.PRFINISH-1 <= '31-AUG-20' AND tp.PRSTART >= '01-AUG-20' GROUP BY tp.prid ) tp ON (1=1) AND av.slice_date BETWEEN tp.START_DATE AND tp.FINISH_DATE WHERE SR.REQUEST_NAME = 'DAILYRESOURCEAVAILCURVE' AND av.slice_date >= '1-AUG-20' AND av.slice_date <= '31-AUG-20' GROUP BY av.prj_object_id, tp.START_DATE, tp.FINISH_DATE ;
Just can't figure out why i am getting duplicate rows.
Any thoughts? Thanks!!
Original Message:
Sent: 09-23-2020 09:45 AM
From: Martti Kinnunen
Subject: SUM Availability by Timeperiods
I do not recall a query like that.
You might get started with the query in
https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?MessageKey=8c108388-13f2-401e-9b45-07d5bf4456bb&CommunityKey=7f0cbca3-5f93-4d44-a369-1a8ce98f5578&tab=digestviewer#bm8c108388-13f2-401e-9b45-07d5bf4456bb
That has the tpstart and tpfinish conditions.
The only place I know the daily availability is, is in the daily slices. So you would use Slice ID 1 name DAILYRESOURCEAVAILCURVE
Original Message:
Sent: 09-22-2020 02:34 PM
From: Keri Taylor
Subject: SUM Availability by Timeperiods
Hello Gurus,
Just wondering if anyone has a query handy for summing availability by timepriods instead of using weekly or monthly slices, since our weeks are not the typical weeks setup of Sunday-Sat or any of the like.
Thanks a bunch!