I am working on a SQL query and I am very confused: The aim is
- Our Company hierarchical Structure: Division lead, Department Lead, Group Lead, Employees
- Everybody in the company has to submit efforts in HP PPM
- When the group lead is logged in, he should see own efforts as well as of the own employees -> OK
- When the department lead is logged in, he should see own efforts, efforts of his group leads and efforts of all employees of the group leads, who are member of the department lead -> OK
- By default: the query should show all efforts of last period, e.g. when group leader logs in, he sees efforts of last week of his employees -> OK
- BUT NOW: When a lead wants to see the efforts of one or more old periods, I have to set a filter field, with a TOKEN. That Token makes me crazy, because when I set into the field an old period, I see the efforts of choosen period and the efforts of the last week (period). The issue is: I always see the efforts of the last week. But when I filter to a period, I want to see the efforts of that filtered period.
CURRENT_DATE - 7 between ktmg_periods.start_date AND ktmg_periods.end_date
or ktmg_periods.period_id in ([P.PERIODS]) <-- This is the TOKEN
My validation returns me the period_id
by default -> see efforts of last week
Use filter field: see efforts of choosen period in the filter field.
Use filter field: see efforts of choosen period and of last period.
in the where clause I cannot say
([P.PERIODS]) is null and CURRENT_DATE - 7 between ktmg_periods.start_date AND ktmg_periods.end_date
because if the filter is not set the where clause doesn't see the TOKEN ([P.PERIODS])