Roll-up daily time slice for specific start and end date

[Anon Anon]

Hi,

 

I have a requirement that I am running out of ideas on how to fulfill. Have been trying this for some time now.

 

The attached code 'mp_data_dump_obs_selection_daily.sql', gives me the Task Date and Task Hours on a Daily basis.

 

• this report generates by regular calendar dates (any date you enter in the parameters), but it gives me the daily tasks instead of rolling up to a total.  So for example, if I worked on a task each day for 1 hour called Coding, it would give me the information as follows:
  • Monday, Windows 1 hr
  • Tuesday, Windows 1 hr, etc.

 

The attached code 'mp_data_dump_obs_selection.sql' gives me Task Date and Task Hours on a Weekly basis.

 

• This is based on the fiscal calendar or Monday to Sunday data.  It rolls up the information by task, so you can a total rather than breaking it out by day.  But if my month falls in the middle of the week, then when I run the report to gather what the actual hours are for a given month, I have to subtract out the end of the previous month and the start of the next month to get the correct month data.

 

When I run the actual data for a given month, I have to use the MP-Data Dump: OBS Selection Report and then subtract out the hours prior to the given month and after. So for example in the April report, I had to go in and remove the hours from 3/31 and also 5/1 – 5/4 to give me the total number of hours for the month of April.

 

The above report can only give me the Monday to Sunday numbers, hence the reason explained above and the other report gives me every task for the week.

 

Here is what I need:

 

• Start and End Date – I need to be able to enter my time period that would use the daily period not the weekly period (that is given to me in the OBS Selection Report).  For example, I need to enter 8/1 to 8/31 and get that time period, even though those dates may end up mid-week.

 

• Task Hours – I need these to roll up to a total for the week, not give me a daily time for each task.  So if I entered one hour each day for Not at Work, I need the report to show 5 hrs to Not at Work for the week.  

 

I basically need to combine both the reports. However, one report gets it data from timesheet tables and another gets it data from timeslice tables.

 

Can somebody help me with the same? Thanks in advance.

Attachment(s)

mp_data_dump_obs_selection_daily.sql.zip   1 KB 1 version

mp_data_dump_obs_selection.sql.zip   2 KB 1 version

[navzjoshi00]

Not going thro' your code, have you tried using the below ?

WEEKLYRESOURCEACTCURVE (ID = 55556)

 

NJ

[Anon Anon]

I have tried using 'WEEKLYRESOURCEACTCURVE' but it's not helping me.

[Deactivated User]

If you are querying the pr_blb_slices.slice then you should be able to restrict the time frame with prj_blb_slices.slice_date 's greater than and less than

[Anon Anon]

Hello urmas,

 

Thanks for your reply. Using prj_blb_slices.slice_date 's greater than and less than, I am able to restrict the time frame. However, my client's requirement is a little bit more. Please see below if you would be able to help.

 

I need to be able to modify the code of 'mp_data_dump_obs_selection_daily.sql' so as to :

 

1) roll-up or total the task hours on a weekly basis instead of daily.

     - Can do this using weekly time slices but here is the problem in below point.

2) the task hours should show data with start date as from the date I enter to Sunday, instead of Monday to Sunday numbers.

     For example, for the month of April, 2014, I need to able to get the total of the 1st week data from 1st April to 6th April, excluding 31st May, which is the actual start of the week.

 

Regards,

Deepak

[Anon Anon]

Okay so my requirement has changed slightly:

 

 

Currently the daily report shows as (example):

 

Task Name      Resource ID      Task Date      Task Hours

abc                         123                   4/1                     8.50

abc                         123                    4/2                     7.50

abc                         123                   4/3                     8.50

What I need to do:

 

Task Name      Resource ID           Task Date                Task Hours

abc                     123                        4/1 – 4/30         <Total for the month, for that task, as per the month start and end date entered>

xyz                     123                        4/1 – 4/30          <Total for the month, for that task, as per the month start and end date entered >

Can somebody help me modify the 'mp_data_dump_obs_selection_daily.sql' code to achieve the above?

[Anon Anon]

I'm a bit lost about what you are (now) trying to do...

 

In your original post you had a problem where your "weekly query" was not breaking out months - this is just because that weekly query is adding up time from timesheets/time-entries and I assume that you are setting up your timeperiods weekly and not breaking them over month-end (in every Clarity system I have seen, there will usually be two timeperiods at month end and so you would not get that problem).

 

In your last post you talk about modifying your "daily query" to add up data at month level; I think you have already been given the answer for that (which is "use a different slice") - your code is currently using the 'Daily Timeentry Actuals' slice - so have you tried just changing that line to use one of the monthly slices instead?

[Anon Anon]

Hello Dave...thanks for your response...

 

regarding my last post, when I tried using one of the monthly slices, I didnt get any results, is it that I need to configure them?

 

Is there any way using Sum function, etc. I can add up the daily totals to display monthly data for a specific user for a specific task?

[Anon Anon]

Yes and Yes!

 

You would need to check how your slices are configured - the limit of what data you can display will be dictated by the configuration (which is why personally I would try to use a query that did not rely on the slices, much like your 'weekly query' but I would ensure my timeperiods were set up to not 'confuse' months)

 

SUM-ing the daily data to get monthly data is possible of course, but typically daily slices are set up over a much smaller period than a monthly slice, so functionally this would be a rather odd way of going about this (the right functional solution is either to configure the monthly slice to give you the data you need or use the timesheet data (but as I have said 3 times now that is dictated by how you set up your timeperiods).

 

(both of these comments are really functional ones rather than technical ones)

[Anon Anon]

David, appreciate your reply.

 

I need to check how the time slices are configured in our system. Would be doing that in some time. Regarding the time periods, it is configured from Monday to Sunday (doesn't break over at month end, unless the last day is a Sunday). Hence I don't think I can use that as the requirement is to select specific start and end dates or rather exact monthly start date and exact monthly end date.

 

As configuring the monthly time slice may take time, and this requirement needs to be completed urgently, so as a temporary solution, are you aware, how technically I can roll up the daily slices to give the monthly totals. Somehow, using the Sum function is not helping or I am not able to use it correctly. Any ideas?

[Anon Anon]

In SQL you would need to SUM the relevant column and GROUP BY the things you are not summing.

[Anon Anon]

Hi,

 

I have modified the code as per the suggestion and also getting the required output too. However, there is a yellow line highlighting my select clause in SQL developer and when I keep my mouse over it, it says 'Select Clause' inconsistent with 'Group By' clause. The query runs fine, but why I am worried is when I copy the modified query to my Actuate report, there I get an error stating 'SQL Command not ended properly.

 

Could somebody help me fix what's wrong with my Group By clause? Query copied below.

 

Thanks,
Deepak

 

SELECT DISTINCT PCF.project_code

                AS project_id,

             PCF.project_name

                AS project_name,

                  Decode(PP.status, 0, 'Not Approved',

                                  1, 'Approved',

                                  'Rejected')

                AS project_status,

Zz_matrix_billrate_sec_fct(pcf.project_id, Zz_rates_fct(nrc.resource_id, pcf.project_id, t.prid, tsh.slice_date, t.prfinish), :gUserID) bill_rate,

Decode(PCF.is_active, 1, 'Yes',

                      'No')

                AS project_active,

GLCODE.account_code

                AS Accounting_Unit,

CP.prj_type

                AS project_type,

Decode(CP.mpchargeid, 1, 'Yes',

                      0, 'No',

                      'NA')

                AS chargeable_name,

PCF.manager_last_name || ' ' || PCF.manager_first_name Project_Manager,

OBS1.obs_type_name,

OBS1.level0_name,

OBS1.level1_name,

OBS1.level2_name,

OBS1.level3_name,

OBS1.level4_name,

OBS1.level5_name,

OBS1.level6_name,

OBS1.level7_name,

OBS1.level8_name,

OBS1.level9_name,

' ' AS EmptyField,

RCF.resource_id,

RCF.last_name || ' ' || RCF.first_name AS resource_name,

RCF.manager_last_name || ' ' || RCF.manager_first_name manager_name,

rcf.role_name,

nls.name

AS employment_type,

CR.gen

AS gendername,

Decode(RCF.is_active, 1, 'Active',

                      'InActive')

AS resource_active,

C.city,

cost_center.account_code,

OBS3.obs_type_name

                AS obs_type_name_2,

OBS3.level0_name

                AS level0_name_2,

OBS3.level1_name

                AS level1_name_2,

OBS3.level2_name

                AS level2_name_2,

OBS3.level3_name

                AS level3_name_2,

OBS3.level4_name

                AS level4_name_2,

OBS3.level5_name

                AS level5_name_2,

OBS3.level6_name

                AS level6_name_2,

OBS3.level7_name

                AS level7_name_2,

OBS3.level8_name

                AS level8_name_2,

OBS3.level9_name

                AS level9_name_2,

T.prname

                AS TaskName,

task_info.phase_code

                AS phase_code,

task_info.phase_name

                AS phase_name,

task_info.task_sequence

                AS task_outline,

count(TO_CHAR(Tsh.slice_date, 'MON'))/5

                AS Records,

sum(Tsh.slice/5)

                AS Hours,

CC.prname

                AS charge_code,

Nvl(mr.targetbillrate, 0)

                target,

Decode(TS.prstatus, 0, 'Unsubmitted',

                    1, 'Submitted',

                    2, 'Rejected',

                    3, 'Approved',

                    4, 'Posted',

                    5, 'Adjusted',

                    'Not Submitted')

                AS TimeStatus,

TC.prname

                TypeCodeName,

apm.address_name

                vendor_code

 

 

FROM   prj_blb_slicerequests tsr

       join prj_blb_slices tsh

         ON tsr.id = tsh.slice_request_id

       join prtimeentry TE

         ON tsh.prj_object_id = te.prid

       join prtimesheet TS

         ON TS.prid = TE.prtimesheetid

       join prassignment A

         ON TE.prassignmentid = A.prid

       join prtask T

         ON A.prtaskid = T.prid

       join nbi_project_current_facts PCF

         ON T.prprojectid = PCF.project_id

       join prj_projects PP

         ON PCF.project_id = PP.prid

       join odf_ca_project CP

         ON PCF.project_id = CP.id

       left outer join prchargecode CC

                    ON CC.prid = TE.prchargecodeid

       left outer join prtypecode TC

                    ON TC.prid = TE.prtypecodeid

       left outer join nbi_dim_obs OBS1

                    ON OBS1.obs_unit_id = PCF.obs1_unit_id

       left outer join (SELECT gl.object_id,

                               A.account_code,

                               ad.allocation_percent

                        FROM   (SELECT id,

                                       object_id,

                                       allocation_type

                                FROM   pac_chg_allocations

                                WHERE  allocation_type = 'PROJECT_CHARGEBACKS')

                               GL,

                               (SELECT ad.id,

                                       ad.allocation_id

                                FROM   pac_chg_allocs_dates ad

                                WHERE  ad.from_date <= SYSDATE

                                       AND ( ad.to_date >= SYSDATE

                                              OR ad.to_date IS NULL )

                                ORDER  BY ad.allocation_id) gldate,

                               pac_chg_allocs_details ad,

                               pac_chg_gl_accounts A

                        WHERE  gl.id = gldate.allocation_id

                               AND gldate.id = ad.allocation_dates_id

                               AND ad.account_code_id = A.id

                        ORDER  BY gl.object_id) glcode

                    ON PCF.project_id = GLCODE.object_id

       join (SELECT task_id,

                    phase_code,

                    phase_name,

                    task_name,

                    task_sequence

             FROM   cust_phase_rollup_v) task_info

         ON T.prid = task_info.task_id

       join nbi_resource_current_facts RCF

         ON TS.prresourceid = RCF.resource_id

       left outer join pac_mnt_resources mr

                    ON rcf.resource_id = mr.id

       left outer join apmaster apm

                    ON mr.vendor_code = apm.vendor_code

       left outer join nbi_dim_obs OBS3

                    ON RCF.obs1_unit_id = OBS3.obs_unit_id

       join odf_ca_resource CR

         ON RCF.resource_id = CR.id

       left outer join (SELECT id,

                               account_code

                        FROM   pac_chg_gl_accounts) cost_center

                    ON CR.cc = cost_center.id

       left outer join mp_res_rates_costs_v nrc

                    ON rcf.resource_id = nrc.resource_id

       left outer join (SELECT principal_id,

                               city

                        FROM   srm_contacts

                        WHERE  principal_type = 'RESOURCE') C

                    ON RCF.resource_id = C.principal_id

       left outer join prj_obs_associations oa

                    ON oa.table_name = 'SRM_RESOURCES'

                       AND OA.record_id = RCF.resource_id

       join nbi_dim_obs_flat ofp

         ON oa.unit_id = ofp.child_obs_unit_id

       join nbi_dim_obs_flat ofc

         ON ofp.child_obs_unit_id = ofc.parent_obs_unit_id

       join srm_resources rr

         ON rcf.resource_id = rr.id

       join cmn_captions_nls nls

         ON rr.person_type = nls.pk_id

            AND nls.table_name = 'CMN_LOOKUPS'

            AND nls.language_code = 'en'

WHERE  tsr.request_name = 'Daily Timeentry Actuals'

       AND tsh.slice > 0

       AND ts.prstatus < 5

       AND pcf.project_code = '016166'

       AND tsh.slice_date >= To_date('1-MAY-14')

       AND tsh.slice_date <= To_date('31-MAY-14')

group by PCF.project_code, PCF.project_name, Decode(PP.status, 0, 'Not Approved', 1, 'Approved', 'Rejected'),

Zz_matrix_billrate_sec_fct(pcf.project_id, Zz_rates_fct(nrc.resource_id, pcf.project_id, t.prid, tsh.slice_date, t.prfinish), :gUserID),

Decode(PCF.is_active, 1, 'Yes', 'No'), GLCODE.account_code, CP.prj_type, Decode(CP.mpchargeid, 1, 'Yes', 0, 'No', 'NA'),

PCF.manager_last_name || ' ' || PCF.manager_first_name, OBS1.obs_type_name, OBS1.level0_name, OBS1.level1_name, OBS1.level2_name,

OBS1.level3_name, OBS1.level4_name, OBS1.level5_name, OBS1.level6_name, OBS1.level7_name, OBS1.level8_name, OBS1.level9_name, ' ',

RCF.resource_id, RCF.last_name || ' ' || RCF.first_name, RCF.manager_last_name || ' ' || RCF.manager_first_name,

rcf.role_name, nls.name, CR.gen, Decode(RCF.is_active, 1, 'Active', 'InActive'), C.city, cost_center.account_code,

OBS3.obs_type_name, OBS3.level0_name, OBS3.level1_name, OBS3.level2_name, OBS3.level3_name, OBS3.level4_name, OBS3.level5_name,

OBS3.level6_name, OBS3.level7_name, OBS3.level8_name, OBS3.level9_name, T.prname, task_info.phase_code, task_info.phase_name,

task_info.task_sequence, CC.prname, Nvl(mr.targetbillrate, 0),

Decode(TS.prstatus, 0, 'Unsubmitted', 1, 'Submitted', 2, 'Rejected', 3, 'Approved', 4, 'Posted', 5, 'Adjusted', 'Not Submitted'),

TC.prname, apm.address_name, vendor_code

[Anon Anon]

That "yellow line" - that might just be an oddity in SQL*Developer - your GROUP BY clause is quite complex, perhaps it is just confused.  If your SQL runs OK then the database should be happy enough.

 

Similarly, the error you are getting in Actuate might just be coincidence - that 'SQL Command not ended properly' error pops up often when you have just mistyped/miscopied something that the SQL parser is not expecting (does Actuate expect a terminating semi-colon right at the end for example? (I don't know the answer to that))

 

--

 

(Also, whilst I still am not going to try to debug your code, I would suggest that needing a DISTINCT at the start of your SQL means that there is something wrong in the logic of your SQL ; what I mean is, if you have joined all your data correctly then the results should implicitly be 'distinct', if you find you need to add a DISTINCT clause to get rid of 'duplicate rows' then there is probably something wrong in your code.  In short - remove the DISTINCT; does it still give the right results = "all good", if you are now getting duplicate data, check your code)

[Anon Anon]

Thanks David for looking into this.

 

Regarding the Distinct clause whether needed or not, I am going to look into the same and get back in some time.

 

In the mean-time, just wanted to update that 'Actuate' doesn't expect a semi-colon at the end of the code. In fact if I enter a semi-colon at the end of the code, Actuate throws up an error.

[Anon Anon]

Hi David,

 

As per you suggestion, I have removed the distinct column and the query works fine.

 

Do you know any way I can add the slice from date and to date columns in the query?

 

Regards,

Deepak

[Anon Anon]

MAX and MIN on the tsh.slice_date would give you this I think?

[Anon Anon]

Thanks David!!! :-)

[Anon Anon]

Hi,

 

My issue is still there.

 

In the below query if I un-comment the SUM function and the Group by clause, I am getting error as "SQL command not properly ended". Can somebody help me point out what's wrong with the code causing this error.

I have cross-checked many times but unable to determine the error.

select PCF.project_code as project_id,  PCF.project_name as project_name, 

decode(PP.status,0,'Not Approved', 1, 'Approved', 'Rejected') as project_status,

ZZ_MATRIX_BILLRATE_SEC_FCT(pcf.project_ID,zz_rates_fct(nrc.resource_id,pcf.project_id,t.prid,tsh.slice_date,t.prfinish),:gUserID) bill_rate,

decode(PCF.is_active, 1, 'Yes', 'No') project_active, 

GLCODE.account_code as Accounting_Unit, 

CP.prj_type as project_type, 

decode(CP.mpchargeid, 1, 'Yes', 0, 'No', 'NA') chargeable_name, 

PCF.manager_last_name || ' ' || PCF.manager_first_name Project_Manager,

OBS1.obs_type_name, OBS1.level0_name, OBS1.level1_name, OBS1.level2_name,

OBS1.level3_name, OBS1.level4_name, OBS1.level5_name, OBS1.level6_name,

OBS1.level7_name, OBS1.level8_name, OBS1.level9_name,

' ' as EmptyField,

RCF.resource_id, RCF.last_name || ' ' || RCF.first_name as resource_name,

RCF.manager_last_name || ' ' || RCF.manager_first_name manager_name,

rcf.role_name,

nls.name as employment_type,

CR.gen as gendername,

decode(RCF.is_active,1,'Active','InActive') as resource_active,

C.city, cost_center.account_code,  OBS3.obs_type_name as obs_type_name_2,

OBS3.level0_name as level0_name_2, OBS3.level1_name as level1_name_2,

OBS3.level2_name as level2_name_2, OBS3.level3_name as level3_name_2,

OBS3.level4_name as level4_name_2, OBS3.level5_name as level5_name_2,

OBS3.level6_name as level6_name_2, OBS3.level7_name as level7_name_2,

OBS3.level8_name as level8_name_2, OBS3.level9_name as level9_name_2,

T.prname as TaskName, task_info.phase_code as phase_code,

task_info.phase_name as phase_name, task_info.task_sequence as task_outline,

Tsh.slice_date as fact_date,

/* sum(Tsh.slice/5) Hours, */

CC.prname as charge_code,

nvl(mr.targetbillrate,0) target,

decode(TS.prstatus, 0, 'Unsubmitted', 1, 'Submitted', 2, 'Rejected', 3, 'Approved', 4, 'Posted', 5, 'Adjusted', 'Not Submitted') TimeStatus,

TC.prname TypeCodeName,

apm.address_name vendor_code

 

 

from prj_blb_slicerequests tsr

JOIN prj_blb_slices tsh ON tsr.id = tsh.slice_request_id

JOIN prtimeentry TE ON tsh.prj_object_id = te.prid

JOIN prtimesheet TS ON TS.prid = TE.prtimesheetid

JOIN prAssignment A ON TE.prassignmentid = A.prid

JOIN prtask T ON A.prtaskid = T.prid

JOIN nbi_project_current_facts PCF ON T.prprojectid = PCF.project_id

JOIN prj_projects PP ON PCF.project_id = PP.prid

JOIN odf_ca_project CP ON PCF.project_id = CP.id

LEFT OUTER JOIN  prChargecode CC ON CC.prid = TE.prchargecodeid

LEFT OUTER JOIN prtypecode TC ON  TC.prid = TE.prtypecodeid

LEFT OUTER JOIN nbi_dim_obs OBS1 ON OBS1.obs_unit_id = PCF.obs1_unit_id 

 

 

  LEFT OUTER JOIN (select gl.object_id, A.ACCOUNT_CODE, ad.allocation_percent

        from (SELECT ID, OBJECT_ID, ALLOCATION_TYPE  FROM PAC_CHG_ALLOCATIONS 

        WHERE ALLOCATION_TYPE = 'PROJECT_CHARGEBACKS') GL,

       (select ad.id, ad.allocation_id

          from pac_chg_allocs_dates ad

         where ad.from_date <= sysdate and (ad.to_date >= sysdate or ad.to_date is null)

        order by ad.allocation_id) gldate,

         pac_chg_allocs_details ad,  PAC_CHG_GL_ACCOUNTS A

        where gl.id = gldate.allocation_id

          and gldate.id = ad.allocation_dates_id

          and ad.ACCOUNT_CODE_ID = A.ID

        order by gl.object_id) glcode ON PCF.project_id = GLCODE.OBJECT_ID

 

 

JOIN (select task_id, phase_code, phase_name, task_name, task_sequence from cust_phase_rollup_v) task_info ON T.prid = task_info.task_id

 

 

JOIN nbi_resource_current_facts RCF ON TS.prresourceid = RCF.resource_id

 

 

LEFT OUTER JOIN   pac_mnt_resources mr ON rcf.resource_id = mr.id

LEFT OUTER JOIN apmaster apm on mr.vendor_code = apm.vendor_code

LEFT OUTER JOIN  nbi_dim_obs OBS3 ON RCF.obs1_unit_id = OBS3.obs_unit_id

JOIN odf_ca_resource CR ON   RCF.resource_id = CR.id

LEFT OUTER JOIN (select id, account_code from pac_chg_gl_accounts) cost_center ON  CR.CC = cost_center.id

LEFT OUTER JOIN  mp_res_rates_costs_v nrc ON  rcf.resource_id = nrc.resource_id

LEFT OUTER JOIN (select principal_id, city from srm_contacts where principal_type = 'RESOURCE') C ON RCF.resource_id = C.principal_id

 

LEFT OUTER JOIN  prj_obs_associations oa ON  oa.table_name = 'SRM_RESOURCES' and OA.record_id = RCF.resource_id

JOIN nbi_dim_obs_flat ofp ON oa.unit_id = ofp.child_obs_unit_id

JOIN nbi_dim_obs_flat ofc ON ofp.child_obs_unit_id = ofc.parent_obs_unit_id

JOIN srm_resources rr ON rcf.resource_id = rr.id

JOIN cmn_captions_nls nls ON rr.person_type = nls.pk_id AND nls.table_name = 'CMN_LOOKUPS' and nls.language_code = 'en'

 

 

where 

  tsr.request_name = 'Daily Timeentry Actuals' and

  tsh.slice > 0 and

  ts.prstatus < 5 AND

pcf.project_code = '016166'

/*

group by PCF.project_code, 

PCF.project_name, 

decode(PP.status,0,'Not Approved', 1, 'Approved', 'Rejected'),

ZZ_MATRIX_BILLRATE_SEC_FCT(pcf.project_ID,zz_rates_fct(nrc.resource_id,pcf.project_id,t.prid,tsh.slice_date,t.prfinish),:gUserID),

decode(PCF.is_active, 1, 'Yes', 'No'),

GLCODE.account_code, 

CP.prj_type,

decode(CP.mpchargeid, 1, 'Yes', 0, 'No', 'NA'), 

PCF.manager_last_name || ' ' || PCF.manager_first_name,

OBS1.obs_type_name,

OBS1.level0_name,

OBS1.level1_name,

OBS1.level2_name,

OBS1.level3_name,

OBS1.level4_name,

OBS1.level5_name,

OBS1.level6_name,

OBS1.level7_name,

OBS1.level8_name,

OBS1.level9_name,

RCF.resource_id,

RCF.last_name || ' ' || RCF.first_name,

RCF.manager_last_name || ' ' || RCF.manager_first_name,

rcf.role_name,

nls.name,

CR.gen,

decode(RCF.is_active,1,'Active','InActive'),

C.city,

cost_center.account_code,

OBS3.obs_type_name,

OBS3.level0_name,

OBS3.level1_name,

OBS3.level2_name,

OBS3.level3_name,

OBS3.level4_name,

OBS3.level5_name,

OBS3.level6_name,

OBS3.level7_name,

OBS3.level8_name,

OBS3.level9_name,

' ',

T.prname,

task_info.phase_code,

task_info.phase_name,

task_info.task_sequence,

Tsh.slice_date,

CC.prname,

nvl(mr.targetbillrate,0),

decode(TS.prstatus, 0, 'Unsubmitted', 1, 'Submitted', 2, 'Rejected', 3, 'Approved', 4, 'Posted', 5, 'Adjusted', 'Not Submitted'),

TC.prname,

apm.address_name */

[Kathryn Ellis]

you can sum up the daily values using the Datamart ISO Standard Calendar Dates.

 

in the WHERE clause join the slice data to the calendar data..... something like this.....

(incomplete code provided)

 

-------------------------------------------------------------------------------------------------

SELECT....

, nbi_dim_calendar_time_v.time_key

, nbi_dim_calendar_time_v.time_label

, nbi_dim_calendar_time_v.hierarchy_level

....

FROM....

WHERE...

    (prj_blb_slices.slice_date >= nbi_dim_calendar_time_v.period_start_date

and prj_blb_slices.slice_date <= nbi_dim_calendar_time_v.period_end_date )

and nbi_dim_calendar_time_v.language_code = 'en'

and nbi_dim_calendar_time_v.hierarchy_level = 'MONTH'

GROUP BY...

, nbi_dim_calendar_time_v.time_key

, nbi_dim_calendar_time_v.time_label

, nbi_dim_calendar_time_v.hierarchy_level

-------------------------------------------------------------------------------------------------

 

I hope this helps out!