I'm trying to put into my select a date parameter. I'm not sure how really to do this. Right now I have fixed date value in it and now I want to have a parameter like: StartDate and EndDate. I'll be really grateful for any help. My select:
select laborcode,
(select sum(workhours) from workperiod where calnum='...'and workdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) -
(select count(calnum) from workperiod where calnum='...'and workdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY') and shiftnum='HOLIDAY') * 8 as alias,
(select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) as alias,
(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) as alias
from labor l
The macro prompt syntax is #prompt('StartDate','date')# Which would change your SQL to:
select laborcode,
(select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY')) -
(select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure,
(select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY')) as delovne_ure,
(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY')) as assignment_ure
from labor l