Hi!
Ok, i think i finally figure out the data logic regarding baseline. It seems, that when you save the baseline the resources time slices data is not saved (because the baseline = allocation). But when you change the allocation after the baseline is saved, then the baseline (previous) allocation is alos saved.
Most probably there is a possibility to write better SQL, but it seems to work for me.
-- if there is saved baseline
SELECT
full_name,
res_id,
role_id,
role_name,
SUM(ISNULL(avail_hrs,0)) avail_hrs,
SUM(ISNULL(alloc_hrs,0)) alloc_hrs,
SUM(ISNULL(actual_hrs,0)) actual_hrs,
CASE
WHEN SUM(ISNULL(baseline_hrs/8,0)) = SUM(ISNULL(baseline1_hrs/8,0)) THEN SUM(ISNULL(baseline1_hrs/8,0))
ELSE SUM(ISNULL(baseline_hrs/8,0))
END AS baseline_hrs,
slice_date
FROM (
-- if there is changed allocation in the project (meaning there are values in PRJ_BLB_SLICES_M_BASE) then use this
SELECT
r1.ID res_id,
r1.full_name,
tm1.ROLE_ID role_id,
r1.full_name role_name,
0 avail_hrs,
0 alloc_hrs,
0 actual_hrs,
SUM(ISNULL(s1.slice,0)) baseline_hrs,
0 baseline1_hrs,
s1.slice_date
FROM PRJ_BASELINES pb
INNER JOIN PRJ_BASELINE_DETAILS pd1 ON pd1.BASELINE_ID = pb.ID AND pd1.OBJECT_TYPE = 'ASSIGNMENT'
INNER JOIN PRASSIGNMENT tm1 ON pd1.OBJECT_ID = tm1.PRID
INNER JOIN niku.SRM_RESOURCES r1 ON tm1.ROLE_ID = r1.ID
LEFT JOIN PRJ_BLB_SLICES_M_BASE s1 ON pd1.ID = s1.PRJ_OBJECT_ID
WHERE
pb.IS_CURRENT = 1
AND pb.PROJECT_ID = ***
GROUP BY r1.ID, r1.full_name, tm1.ROLE_ID, s1.slice_date, s1.SLICE
UNION
-- if there is no changed allocation compared to baseline in the project use allocation values PRJ_BLB_SLICES_M_ALC
SELECT
r.ID res_id,
r.full_name,
tm.PRROLEID role_id,
r.full_name role_name,
0 avail_hrs,
0 alloc_hrs,
0 actual_hrs,
0 baseline_hrs,
SUM(ISNULL(s.slice,0)) baseline1_hrs,
s.slice_date
FROM PRJ_BASELINES pb
INNER JOIN PRJ_BASELINE_DETAILS pd ON pd.BASELINE_ID = pb.ID AND pd.OBJECT_TYPE = 'TEAM'
INNER JOIN niku.PRTEAM tm ON pd.OBJECT_ID = tm.PRID
INNER JOIN niku.SRM_RESOURCES r ON tm.PRROLEID = r.ID
LEFT JOIN PRJ_BLB_SLICES_M_ALC s ON tm.PRID = s.PRJ_OBJECT_ID
WHERE
pb.IS_CURRENT = 1
AND pb.PROJECT_ID = ***
GROUP BY r.ID, r.full_name, tm.PRROLEID, s.slice_date, s.SLICE
) A
WHERE
slice_date BETWEEN DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) AND GETDATE()
GROUP BY full_name, res_id, role_id, role_name, slice_date
order by SLICE_DATE