AnsweredAssumed Answered

Help with Simple NSQL Statement

Question asked by venkat1 on Jun 27, 2013
Latest reply on Jun 27, 2013 by Owen_R
Hi All,

I'm newbie to Clarity. I want to field which tell's you that this a current timeperiod


I have been doing something like this

resper.prstart <= sysdate <= resper.prfinish

say for time period 6/08/13(PR Start -Saturday )- 6/15/13(PRFinish Saturday) but i want to some action on the friday (a day before finish day i.e 6/14/13 ) so i changed it to



Question 1 ) resper.prstart <= sysdate <= resper.prfinish-1 --- This looks ok but giving me an error 00905: missing keyword SQL Text


but i was thinking if there is something current period and sysdate<=resper.prfinish-1

Question 2 ) To Fix above error and also since i don't know the current period indicator i have used something like this which works but for some reason i don't like the way

WHEN resper.prstart <= sysdate and resper.prstart+6 > sysdate


Any suggestions


SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:resper.resid || resper.periodid:resperiod@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:r.full_name:Resname@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:NVL(te.ts_id,0):tsid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:(SELECT full_name
FROM srm_resources
WHERE user_id = r.manager_id):Manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:resper.prstart:Periodstart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:resper.prfinish:Periodend@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:(CASE WHEN te.prstatus = 0 THEN 'Unsubmitted'
WHEN te.prstatus = 1 THEN 'Submitted'
WHEN te.prstatus = 2 THEN 'Returned'
WHEN te.prstatus = 3 THEN 'Approved'
WHEN te.prstatus = 4 THEN 'Posted'
WHEN te.prstatus = 5 THEN 'Adjusted'
ELSE 'Not Created' END):timesheet_status@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(NVL(te.practsum/3600,0)):actual_hours:agr@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(NVL(te.practsum/28800,0)):actual_days:agr@,
@SELECT:DIM_PROP:USER_DEF:STOPLIGHT:RESOURCE:(CASE


WHEN resper.prstart > sysdate
THEN NVL(te.prstatus,5)

WHEN resper.prstart <= sysdate <= resper.prfinish

--WHEN resper.prstart <= sysdate and resper.prstart+6 > sysdate
THEN 6

ELSE
NVL(te.prstatus,9)END):stoplight@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:CASE WHEN te.prstatus IN (0, 1, 2, 3, 4, 5) THEN 1 ELSE 0 END:allow_link@
FROM (SELECT id,
user_id,
full_name,
manager_id,
person_type
FROM srm_resources R
JOIN prj_resources pr ON r.id = pr.prid
WHERE prisopen = 1
AND pr.prtrackmode = 2) r
JOIN (SELECT id, name
FROM cmn_lookups_v
WHERE lookup_type = 'SRM_RESOURCE_TYPE' and
language_code = 'en') TYPE
ON r.person_type = TYPE.id
JOIN (SELECT r.id resid,
tp.prid periodid,
tp.prstart,
tp.prfinish
FROM srm_resources r
JOIN prtimeperiod tp
ON 1=1
AND (r.date_of_hire IS NULL OR r.date_of_hire <= tp.prfinish -1)
AND (r.date_of_termination IS NULL OR r.date_of_termination >= tp.prfinish - 1)
WHERE
--tp.prfinish < SYSDATE
-- AND
r.is_active = 1
AND tp.prisopen = 1) resper
ON resper.resid = r.id
LEFT OUTER JOIN
(SELECT ts.prid ts_id,
tp.prid tp_id,
ts.prresourceid,
te.practsum,
NVL(ts.prstatus,0)
prstatus
FROM prtimesheet ts
JOIN prtimeperiod tp
ON tp.prid = ts.prtimeperiodid
LEFT JOIN prtimeentry te
ON te.prtimesheetid = ts.prid
) te
ON resper.resid = te.prresourceid
AND resper.periodid = te.tp_id
WHERE r.manager_id = @WHERE:PARAM:user_id@
AND @FILTER@
AND (te.prstatus is NULL or te.prstatus< 3)
GROUP BY resper.resid || resper.periodid,
nvl(te.ts_id,0),
(CASE
WHEN resper.prstart > sysdate
THEN NVL(te.prstatus,5)

WHEN resper.prstart <= sysdate and resper.prstart+6 > sysdate
--WHEN resper.prstart <= sysdate <= resper.prfinish

THEN 6
ELSE
NVL(te.prstatus,9)END),
r.person_type,
type.name,
r.id,r.full_name,
r.manager_id,
resper.prstart,
resper.prfinish,
(CASE WHEN te.prstatus IN (0, 1, 2, 3, 4, 5) THEN 1 ELSE 0 END),
(CASE WHEN te.prstatus = 0 THEN 'Unsubmitted'
WHEN te.prstatus = 1 THEN 'Submitted'
WHEN te.prstatus = 2 THEN 'Returned'
WHEN te.prstatus = 3 THEN 'Approved'
WHEN te.prstatus = 4 THEN 'Posted'
WHEN te.prstatus = 5 THEN 'Adjusted' ELSE 'Not Created' END)
HAVING @HAVING_FILTER@


Thanks in advance

Outcomes