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
DarrenJacksonAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Try this?

select ...
where earn.company_id = '01' and  earn.emp_no = '000644'  and  earn.data_deriv_day >= trunc(sysdate-80)
0
 
DarrenJacksonAuthor Commented:
bloody hell that was way to easy ;)

Thanks for this appreciate it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.