asked on

date less 80 days

Guys, I have simple query but cannot work it out.

I want to be able to query a date in oracle and return the last 80 DAYS worth

here is the code but its not working

select * from
(SELECT
--ROW_NUMBER() OVER (ORDER BY ifsapp.hrp_pay_list_api.get_date_from(company_id,payroll_list_id) desc ) as rank,
COMPANY_ID,
PAYROLL_LIST_ID,
DATA_DERIV_DAY,
EMP_NO,
NAME,
BASIC_HOURS,
"HOURLY RATE",
OT1_HOURS,
OT2_HOURS,
OT3_HOURS,
TOTAL_HOURS,
TOTAL_EARNINGS,
BASIC_PAY,
OT1,
OT2,
OT3,
DOUBLE_DAY_SHIFT_AM,
DOUBLE_DAY_SHIFT_PM,
BONUS_WEEKLY,
BONUS_VARIED,
"Travel_Time(W)",
SPLIT_WEEK_ALLOWANCE,
TOOL_ALLOWANCE,
"Radius(W)",
SHIFT_ALLOWANCE_AMOUNT_WEEKLY,
OTHER,
"Holiday_Pay(Not_Advanced)",
HOLIDAY_PAY_ADV,
"Holiday_Stamp_Non_NIable(W)",
STATUTORY_HOLIDAY_AMOUNT,
SICK,
COMPANY_SICK,
SMP,
SPP,
LIEU_WEEKLY
FROM (SELECT DISTINCT
HO.COMPANY_ID,
HO.PAYROLL_LIST_ID,
HO.DATA_DERIV_DAY,
HO.EMP_NO,
HO.INTERNAL_DISPLAY_NAME AS NAME,
HO.BASIC AS BASIC_HOURS,
round(SUM(decode(PAY.WAGE_CODE_ID,
'A001',
PAY.VALUE,
0)) /
Decode(HO.BASIC, 0, 0.000001, HO.BASIC),
3) AS "HOURLY RATE",
HO.TIME_HALF AS OT1_HOURS,
HO.DOUBLE AS OT2_HOURS,
HO.OT3 as OT3_HOURS,
nvl(HO.BASIC, 0) + nvl(HO.TIME_HALF, 0) +
nvl(HO.DOUBLE, 0) + nvl(HO.OT3, 0) as Total_Hours,
SUM(decode(PAY.WAGE_CODE_ID, 'A001', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A057', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A058', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A059', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A070', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A071', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A100-W', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A123-W', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A250', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A120', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A116', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A251', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID,
'A114 - W',
PAY.VALUE,
0)) + nvl(HO.OTHER, 0) +
SUM(decode(PAY.WAGE_CODE_ID, 'A121', PAY.VALUE, 0)) +
nvl(HO.HOLIDAY_PAY_ADV, 0) +
SUM(decode(PAY.WAGE_CODE_ID, 'A315', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A099', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'S001', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A119', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'S002', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'S003', PAY.VALUE, 0)) +
SUM(decode(PAY.WAGE_CODE_ID, 'A111-W', PAY.VALUE, 0)) as Total_Earnings,
SUM(decode(PAY.WAGE_CODE_ID, 'A001', PAY.VALUE, 0)) as Basic_Pay,
SUM(decode(PAY.WAGE_CODE_ID, 'A057', PAY.VALUE, 0)) as OT1,
SUM(decode(PAY.WAGE_CODE_ID, 'A058', PAY.VALUE, 0)) as OT2,
SUM(decode(PAY.WAGE_CODE_ID, 'A059', PAY.VALUE, 0)) as OT3,
SUM(decode(PAY.WAGE_CODE_ID, 'A070', PAY.VALUE, 0)) as Double_day_shift_AM,
SUM(decode(PAY.WAGE_CODE_ID, 'A071', PAY.VALUE, 0)) as Double_day_shift_PM,
SUM(decode(PAY.WAGE_CODE_ID, 'A100-W', PAY.VALUE, 0)) AS Bonus_Weekly,
SUM(decode(PAY.WAGE_CODE_ID, 'A123-W', PAY.VALUE, 0)) AS Bonus_Varied,
SUM(decode(PAY.WAGE_CODE_ID, 'A250', PAY.VALUE, 0)) as "Travel_Time(W)",
SUM(decode(PAY.WAGE_CODE_ID, 'A120', PAY.VALUE, 0)) as split_week_allowance,
SUM(decode(PAY.WAGE_CODE_ID, 'A116', PAY.VALUE, 0)) as Tool_Allowance,
SUM(decode(PAY.WAGE_CODE_ID, 'A251', PAY.VALUE, 0)) as "Radius(W)",
SUM(decode(PAY.WAGE_CODE_ID,
'A114 - W',
PAY.VALUE,
0)) as Shift_allowance_amount_weekly,
HO.OTHER,
SUM(decode(PAY.WAGE_CODE_ID, 'A121', PAY.VALUE, 0)) as "Holiday_Pay(Not_Advanced)",
HO.HOLIDAY_PAY_ADV,
SUM(decode(PAY.WAGE_CODE_ID, 'A315', PAY.VALUE, 0)) as "Holiday_Stamp_Non_NIable(W)",
SUM(decode(PAY.WAGE_CODE_ID, 'A099', PAY.VALUE, 0)) as Statutory_holiday_amount,
SUM(decode(PAY.WAGE_CODE_ID, 'S001', PAY.VALUE, 0)) AS SICK,
SUM(decode(PAY.WAGE_CODE_ID, 'A119', PAY.VALUE, 0)) AS COMPANY_SICK,
SUM(decode(PAY.WAGE_CODE_ID, 'S002', PAY.VALUE, 0)) AS SMP,
SUM(decode(PAY.WAGE_CODE_ID, 'S003', PAY.VALUE, 0)) AS SPP,
SUM(decode(PAY.WAGE_CODE_ID, 'A111-W', PAY.VALUE, 0)) AS LIEU_WEEKLY
FROM (select a.company_id,
a.payroll_list_id,
a.validation_date as DATA_DERIV_DAY,
a.emp_no,
ifsapp.company_pers_api.get_name(a.company_id,
a.emp_no) as internal_display_name,
Sum(decode(param_id, 'A001', a.value)) as BASIC,
Sum(decode(param_id, 'A057-H', a.value)) as TIME_HALF,
Sum(decode(param_id, 'A058-H', a.value)) as DOUBLE,
Sum(decode(param_id, 'A059-H', a.value)) as OT3,
Sum(decode(param_id, 'A070-H', a.value)) as DOUBLEDAYSHIFT_AM,
Sum(decode(param_id, 'A071-H', a.value)) as DOUBLEDAYSHIFT_PM,
Sum(decode(param_id, 'A300', a.value)) as HOLIDAY_PAY_ADV,
Sum(decode(param_id, 'A118-W', a.value)) as OTHER
from ifsapp.hrp_pl_par_value a
group by a.company_id,
a.payroll_list_id,
a.validation_date,
a.emp_no) ho
JOIN IFSAPP.hrp_pl_wc_value PAY ON HO.COMPANY_ID = PAY.COMPANY_ID
AND HO.EMP_NO = PAY.EMP_NO
AND HO.data_deriv_day =
PAY.validation_date
and ho.payroll_list_id =
pay.payroll_list_id

GROUP BY HO.COMPANY_ID,
HO.EMP_NO,
HO.INTERNAL_DISPLAY_NAME,
HO.PAYROLL_LIST_ID,
HO.DATA_DERIV_DAY,
HO.BASIC,
HO.TIME_HALF,
HO.DOUBLE,
HO.OT3,
HO.HOLIDAY_PAY_ADV,
HO.OTHER,
PAY.PAYROLL_LIST_ID) EARN
where earn.company_id = '01' and earn.emp_no = '000644' and (to_char(earn.data_deriv_day,'yyyymmdd') - '80' )
) z

any ideas

Thanks

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DarrenJackson

ASKER

bloody hell that was way to easy ;)

Thanks for this appreciate it