SUM Availability by Timeperiods

[Keri Taylor]

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!

[Martti Kinnunen]

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

[Keri Taylor]

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!!

[Keri Taylor]

i think this works now.