We help IT Professionals succeed at work.
Get Started

date less 80 days

192 Views
Last Modified: 2015-03-09
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
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE