I have a table that holds timetracking data of users and looks somewhat like this:
projectid activityid worklocationid year week day hours
1 22 21 2014 37 09/16/2014 8
2 22 4 2014 37 09/15/2014 8.5
So if a user saves hours on one project for every day of the week, there are 5 rows in the database that only differ in the "day" and "hours" columns. I need to display this data on a webpage in a timetable format, without having to query the database for each cell to get the hours for a specific day. I'd like to generate a view that has one row per week for one project/activity/worklocation combination:
projectid activityid worklocationid year week mon tue wed thu fri sat sun total
1 22 21 2014 37 8 8.5 9 8 8 41.5
I tried solving this with subselects and putting the days and hours values into one column respectively, separated by semicolons, but failed miserably because the table has over one million records.
My current approach would be using an OLAP cube, but I'm facing an issue:
There might be one entry for one week, e.g. on Monday, or one each for Monday and Thursday, or five for each day of the week, or none at all. So if I want to save them in an ordered format, I have to determine which row belongs to which day of the week. Is there a way to do this that scales up to this large amount of records?
We're using Oracle 11g.
Please feel free to point out if I missed to write down additional information.