Clarity

Hello,
does anyone have query that returns all OPEN timesheets for a certain timeperiod?

the query I have gives these results but...
only if a resource has added a task to the timesheet will it show in my query. If the resource has never touched teh timesheet it will not show in my query. Is there anyway to get around this? Or is that that the timesheet tables only get populated once teh timesheet is OPENED and an item added?

Here is the query I am using.
SELECT
distinct NIKU.SRM_RESOURCES.FULL_NAME,
NIKU.PRTIMEENTRY.PRACTSUM/3600,
--(NIKU.PRTIMEENTRY.PRACTSUM/3600) as [hours],
NIKU.PRTIMEPERIOD.PRSTART,
niku.prtimesheet.prstatus

FROM
NIKU.SRM_RESOURCES,
NIKU.PRTIMEENTRY,
NIKU.PRTIMEPERIOD,
NIKU.PRTIMESHEET

WHERE
NIKU.PRTIMESHEET.PRRESOURCEID = NIKU.SRM_RESOURCES.ID
AND NIKU.PRTIMESHEET.PRID = NIKU.PRTIMEENTRY.PRTIMESHEETID
AND NIKU.PRTIMESHEET.PRTIMEPERIODID = NIKU.PRTIMEPERIOD.PRID
and NIKU.PRTIMEPERIOD.PRSTART = '10/28/2012'
--AND NIKU.PRTIMESHEET.PRSTATUS = 0
--and NIKU.PRTIMESHEET.PRSTATUS = 2

ORDER BY NIKU.SRM_RESOURCES.FULL_NAME asc

Try this ...

select
@SELECT:DIM:USER_DEF:IMPLIED:TIMESHEET:ts.pruid:ts_pruid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.unique_name:res_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.is_active:res_is_active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.person_type:res_person_type@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.date_of_hire:res_date_of_hire@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.date_of_termination:res_date_of_termination@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.email:res_email@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.is_external:is_external@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.created_date:res_created_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.include_flag:res_include_flag@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prstatus:status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prversion:version@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prsubmittedby:sub_by@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prapprovedby:app_by@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prisadjustment:is_adj@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.prmodtime:modified_time@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:ts.posted_time:posted_time@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:tp.prstart:prstart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:tp.prfinish:prfinish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:tp.prisopen:propen@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:sr.full_name:resource_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEET:mr.full_name:manager_full_name@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,0,NVL(te.practsum,0)/(CASE WHEN sr.resource_type <> 0 THEN 1 ELSE 3600 END))):pending_actuals@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,NVL(te.practsum,0)/(CASE WHEN sr.resource_type <> 0 THEN 1 ELSE 3600 END),0)):posted_actuals@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,0,NVL(te.practsum,0)/(CASE WHEN sr.resource_type <> 0 THEN 1 ELSE 3600 END)))
+ SUM(DECODE(ts.prstatus,4,NVL(te.practsum,0)/(CASE WHEN sr.resource_type <> 0 THEN 1 ELSE 3600 END),0)):total_actuals@

from
PRTIMEPERIOD tp,
PRTIMESHEET ts,
PRTIMEENTRY te,
srm_resources sr,
srm_resources mr

where
ts.prid = ts.prid
and te.prtimesheetid = tp.prid
and ts.prresourceid = sr.id
and ts.prtimeperiodid = tp.prid
and sr.MANAGER_ID = mr.USER_ID
and @filter@
GROUP BY sr.unique_name, sr.is_active, sr.person_type,
sr.date_of_hire, sr.date_of_termination, sr.email, sr.is_external,
sr.created_date, sr.include_flag,
sr.full_name, mr.full_name, ts.pruid, ts.prstatus, ts.prversion,
ts.prsubmittedby, ts.prapprovedby, ts.prisadjustment,
ts.prmodtime, ts.posted_time,tp.prstart, tp.prfinish, tp.prisopen



email me if necessary >>>> Stephen.Forney@GoHomeward.com

Here is the NSQL that you should try first >>>>

SELECT UNIQUE
@SELECT:DIM:USER_DEF:IMPLIED:TIMEAPPROVED:rownum:row_num@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:tp.prstart:tp_prstart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:tp.prfinish:tp_prfinish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:ts.prstatus:ts_prstatus@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:ts.posted_time:ts_posted_time@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:ts.prmodtime:ts_prmodtime@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:r.unique_name:r_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:r.full_name:r_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:r.is_active:r_is_active@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:r.person_type:r_person_type@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:sb.unique_name:sb_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:sb.full_name:sb_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:ab.unique_name:ab_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:ab.full_name:ab_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:mr.unique_name:mr_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMEAPPROVED:mr.full_name:mr_full_name@

from
prtimeperiod tp,
prtimesheet ts,
srm_resources r,
SRM_RESOURCES mr,
SRM_RESOURCES sb,
SRM_RESOURCES ab

where ts.prresourceid = r.id
and ts.prtimeperiodid = tp.prid
and ts.prsubmittedby = sb.user_id
and ts.prapprovedby = ab.user_id
and r.manager_id = mr.user_id

AND @FILTER@

GROUP BY rownum, tp.prstart, tp.prfinish, ts.prstatus,
ts.posted_time, ts.prmodtime, r.unique_name, r.full_name,
r.is_active, r.person_type,
sb.unique_name, sb.full_name, ab.unique_name, ab.full_name,
mr.unique_name, mr.full_name

end

The timesheet is first created when a user clicks to open it.
That is when there no number attached to the resource and timeperiod in the timesheet list there is no timesheet.
When a timesheet is opened it is created and there is no way to cancel that. If no hours are entered then the timesheet list will display 0. That will remain the same when tasks are added to the timesheet until hours are entered.

If you want the "non created" timesheets in the query results you have to join to resources. Something like (for MS SQL)

SELECT t.username username,
t.Resource name,
t.Period_start Period_start,
t.Timesheet_status Timesheet_status,
t.Hours Hours

FROM
(
select
srm_resources.full_name Resource,
srm_resources.unique_name username,
ISNULL(timesheets.Period_Start,'') Period_start,

ISNULL(timesheets.Timesheet_status,'') Timesheet_status,
ISNULL(convert(VARCHAR,timesheets.Hours),'') Hours


from
niku.prj_resources,
niku. srm_resources
left join
(Select
prresourceid,
convert(varchar,prtimeperiod.prstart,105)Period_Start,
prtimesheet.prstatus,
CASE
WHEN prtimesheet.prstatus = 0
THEN 'Open saved'
WHEN prtimesheet.prstatus = 1
THEN 'Submitted'
WHEN prtimesheet.prstatus = 2
THEN 'Submitted'
WHEN prtimesheet.prstatus = 3
THEN 'Approved'
WHEN prtimesheet.prstatus = 4
THEN 'Posted'
WHEN prtimesheet.prstatus = 5
THEN 'Adjusted'
ELSE 'Does not exist'
END
Timesheet_status,

ISNULL(convert(VARCHAR,sheetsum.sheettotal),'No entry') Hours
from niku.prtimeperiod, niku.prtimesheet
left join

(select prtimeentry.prtimesheetid, sum(prtimeentry.practsum)/3600 sheettotal from niku.prtimeentry group by prtimeentry.prtimesheetid
) sheetsum
on prtimesheet.prid = sheetsum.prtimesheetid
where
prtimesheet.prstatus <5
and prtimesheet.prtimeperiodid=prtimeperiod.prid
) timesheets on timesheets.prresourceid=srm_resources.id
where prj_resources.prid=srm_resources.id
and srm_resources.is_active= 1
and prj_resources.prisopen=1
and prj_resources.prisrole=0
and srm_resources.person_type>0 )t

Martti K.

Thanks Martin that worked.