hi thanks for the reply ..
i am using the script below to pull the data....and it is showing me the amount of work that i have done in that month...
but the thing is that for a single project it is showing me like 10-20 records and with different Wres_id which i get from web_work table and when i look into those values , there i can find my one record for the month that i worked..
i was wondering why it is showing me too many wres_id for single project for given time period..
plus this query is giving the data by month ...how can i get the data by per day ???
can you look into the query ...any help will be highly appericiated
thanks
create table #timesheet ( Resource int, Project int, Task int, Period int, Hours dec(7,2))
----------------------------------------------------
-- insert into temp table
----------------------------------------------------
insert into #timesheet ( Resource, Project, Task, Period, Hours)
----------------------------------------------------
-- load timesheet data - account for start month
----------------------------------------------------
select A.WRES_ID, C.PROJ_ID,
--B.TASK_ID,
B.TASK_UID,
(year(A.WWORK_START) * 100) + month(A.WWORK_START),
case when ((year(A.WWORK_START) * 12) + month(A.WWORK_START)) = ((year(A.WWORK_FINISH) * 12) +month(A.WWORK_FINISH))
then (((datediff(dd,A.WWORK_START,A.WWORK_FINISH) + 1) * A.WWORK_VALUE) / 60000)
else ((((datediff(dd,A.WWORK_START,A.WWORK_FINISH) + 1) - day(A.WWORK_FINISH)) *A.WWORK_VALUE) / 60000) end
from LSREPOSITORY.ProjectServer.dbo.MSP_WEB_WORK A
join LSREPOSITORY.ProjectServer.dbo.MSP_WEB_ASSIGNMENTS B
on A.WRES_ID = B.WRES_ID AND B.WASSN_ID = A.WASSN_ID
join LSREPOSITORY.ProjectServer.dbo.MSP_WEB_PROJECTS C
on C.WPROJ_ID = B.WPROJ_ID
where A.WWORK_TYPE = 1 and
((year(A.WWORK_START) * 12) + month(A.WWORK_START)) <= ((year(A.WWORK_FINISH) * 12) +month(A.WWORK_FINISH))
union all
----------------------------------------------------
-- start and end in different months - account for to month
----------------------------------------------------
select A.WRES_ID, C.PROJ_ID,
--B.TASK_ID,
B.TASK_UID,
(year(A.WWORK_FINISH) * 100) + month(A.WWORK_FINISH) as Period,
((day(A.WWORK_FINISH) * A.WWORK_VALUE) / 60000) as Hours
from LSREPOSITORY.ProjectServer.dbo.MSP_WEB_WORK A
join LSREPOSITORY.ProjectServer.dbo.MSP_WEB_ASSIGNMENTS B
on A.WRES_ID = B.WRES_ID AND B.WASSN_ID = A.WASSN_ID
join LSREPOSITORY.ProjectServer.dbo.MSP_WEB_PROJECTS C
on C.WPROJ_ID = B.WPROJ_ID
where A.WWORK_TYPE = 1 and
((year(A.WWORK_START) * 12) + month(A.WWORK_START)) < ((year(A.WWORK_FINISH) * 12) +month(A.WWORK_FINISH))
---------------------------------------------------------
select distinct Resource,Project, Task, Period, sum(Hours)
from #timesheet
group by Resource, Project, Task, Period