AnsweredAssumed Answered

Filter the baseline usage by resource

Question asked by TiagoAb on Dec 12, 2017
Latest reply on Jan 11, 2018 by TiagoAb

Hello,

 

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?

 

Thank you!

Outcomes