Grouping date to form a week and extract the count done by the user for that week?

I got an aggregate with below columns...

UserName, OfficeLoc, #ofActions,#ofhours ( all these are group by columns).

I also have startdate and Enddate columns in the aggregate.

Requirement: While displaying the group by columns, i wanted have columns displaying a week(min of start date)+7 going up to the max of EndDate.  For each user, corresponding row to display the bifurcation of #ofhours in the weekly slot created

E.g.

UserName |OfficeLoc | #ofActions | #ofHours |4/14/2021 | 4/21/2021 | 4/28/2021 | 5/5/2021 | 5/12/2021 | ...

Sunil                 XYZ             100                     20              5                     6                  4                       0                 5


Require an assistance in resolving above problem...

Regards

Sunil

Hi Sunil,

this is a very interesting exercise although I see some limitations just looking at it. 

Using aggregates you cannot rename the columns dinamically. Also the number of columns cannot be generated dinamically.


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.