How to query in SQL timesheet not populated

[Karim DIOUF]

​Hello all,
I have a request to build a process that get all opened timesheets and submit them if the user is unvailable the whole week.
Ive started this query to get the opened timsheets but it gives only the timesheets which the users have populated.

select

res.unique_name [Resource_Id],

res.FULL_NAME [Resource_Name],

slice_date [Week_Start_Date],

CASE WHEN (SUM(slice)/8)=0 THEN 0 ELSE 1 END [Availability],

replace(convert(varchar, PD.PRSTART, 111), '/', '-') AS TP_START,

replace(convert(varchar, PD.PRFINISH, 111), '/', '-') AS TP_FINISH,

pt.prid as ts_id,

pt.prstatus,

pt.prversion,

pd.prid as tp_id

from prj_blb_slices wk

inner join prj_blb_slicerequests req on wk.slice_request_id=req.id and req.request_name = 'WEEKLYRESOURCEAVAILCURVE'

inner join srm_resources res on res.id=wk.prj_object_id

left outer join prtimesheet pt on res.id=pt.PRRESOURCEID

Left outer join PRTIMEPERIOD pd on pt.PRTIMEPERIODID=pd.prid

where res.LAST_NAME='DIOUF'

and wk.SLICE_DATE>=pd.prstart

and pd.prfinish > wk.SLICE_DATE

and (pt.PRSTATUS=0 or pt.PRSTATUS=2)

GROUP BY

res.unique_name,

res.FULL_NAME,

slice_date,

replace(convert(varchar, PD.PRSTART, 111), '/', '-') ,

replace(convert(varchar, PD.PRFINISH, 111), '/', '-'),

pt.prid ,

pt.prstatus,

pt.prversion,

pd.prid

order by

slice_date

Any help to get this or a suggestion of how to query thoses timesheet not populated will be grateful.

thank you,
Karim

[David Morton]

You are be able to find timesheets that have not been created by the user - just write some SQL that includes a NOT EXISTS on the timesheet that you expect to be there.

The problem that you will have is that such timesheets do not exist in the system at all - so you would not be able to submit them ; you could easily build some exception reporting (report or portlet) but you'd probably need some complex XOG to  actually create those missing timesheets (and then submit them)

EDIT : should say that the NOT EXISTS should be  on the PRTIMESHEET record for situations where the user has never even clicked on teh timesheet link and on the PRTIMEENTRY record for where they have clicked but not populated (i.e. empty timesheet) - you would need to think about all scenarios I would assume.

[Deactivated User]

Agree with Dave.it
If you look at the timesheet list it includes entries timesheets that dot not exist.
That is it contains the resources you are allowed to view and which are open for the timeperiods displayed.
When a resources clicks the first time the timesheet icon and the timesheet opens a record is created in prtimesheets table.
At that point it may be that it is not populated and there are no prtimeentry records for that timesheet.
That means you cannot submit it and process further.
As Dave says it is much easier to create an alert from that than to create with XOG the records needed to make it such that it can be submitted. Though it is possible.
Years ago I opted for 500 users manually process up to posting weekly those timesheets that existed, but were not submited and approved.

[Liz Williamson]

If you check some of the Missing Time queries (reporting/nsql) I think you have to do a union query and get all open periods and then get where they do not exist in the prtimesheet table.

I did this years ago a little differently by outer join to all open time periods and then get the users where prtimesheet.prtimeperiodid is null.  Or something like that.