I'm trying to create a sql query that gives me the usage of certain resources for the current baseline of the project, so far here is my query:
SELECT cast(isnull(sum(s.slice),0) as int)
FROM niku.PRTASK AS t JOIN niku.PRASSIGNMENT AS a
ON t.PRID = a.PRTASKID JOIN niku.SRM_RESOURCES AS r
ON a.PRRESOURCEID = r.ID JOIN niku.PRJ_BLB_SLICES AS s
ON a.PRID = s.PRJ_OBJECT_ID JOIN niku.PRJ_BLB_SLICEREQUESTS AS sr
ON s.SLICE_REQUEST_ID = sr.ID
WHERE t.PRPROJECTID = 5038001
and r.FULL_NAME not like '%something%'
and sr.REQUEST_NAME IN ('MONTHLYCURRENTBASEPROJUSAGE')
and r.RESOURCE_TYPE = 0
and s.slice_date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)
The value i'm getting is not correct apparently i'm doing something wrong when i try to filter by resource and not add the baseline tables to my query but even then the results are wrong. Am i forgetting something?
How do i filter the baseline usage by resource?
In order to get this information i had to create a new time slice with the following item: Current Baseline Team Usage.
This gave the information i needed.