Hi, I need a query to split the salaried hours as 8 hrs/day from the state of earnings.

SARAORC

Ashish-Oracle

@SARAORC This forum is dedicated to Fusion Data Intelligence. Please confirm the application you are using here? Also provide more details (if possible) screenshots showing the issue.

NOTE: For Fusion related queries we have a separate sister community.

https://community.oracle.com/customerconnect/

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/24136/hi-i-need-a-query-to-split-the-salaried-hours-as-8-hrs-day-from-the-state-of-earnings

Can you please elaborate your query a bit more? What are you trying to build is it an OTBI or a BIP or in FDI?

Thanks.

SARAORC

@MandeepGupta Thanks for the response.

It was a BIP report, I am trying pull the data of entry hours for the salaried employee hours from the state of earnings page.

So, I need to split the hours eg: if the value is 80 I need to split into 8 hrs/day as 10 separate rows.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62114#Comment_62114

You can write something like below:

select papf.person_number ,'80' num_of_hours, to_number('80')/to_number('10') per_day_hours
from per_all_people_f papf,
(select level lvl
from dual
connect by level <= to_number('80')/to_number('10')
)
where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and lvl <= to_number('80')/to_number('10')
and papf.person_number = '269628'

Thanks.

SARAORC

Hi @MandeepGupta

Thanks for the response

Not all the employees have 80 hours some of them have 72 and 64 hours.

Based on the hours I need to split the values.

For Example:

1. if the value is 80 hours, I need to populate 8 hrs/day as 10 separate rows.
2. If the value is 72 hours it should populate 8 hrs/day as 9 separate rows

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62120#Comment_62120

That was just an example. Instead of below:

'80' num_of_hours, to_number('80')/to_number('10') per_day_hours

Use your actual table.column names.

If you are not able to replicate, share the table name and column name, I can try to write it for you.

Thanks.

SARAORC

I try to replicate the table and column with this query, but it is not working.

I am sharing the table and column below.

Table - Pay_Run_Balances

Column - Balance_Value

Could you please write it for me using this table.

Thanks.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62124#Comment_62124

In your main select clause:

add below:

Pay_Run_Balances.Balance_Value num_of_hours

,(Pay_Run_Balances.Balance_Value/10) per_day_hours

FROM …..

add a new line in from clause:

,

(select level lvl

from dual

connect by level <= (Pay_Run_Balances.Balance_Value/10)

)

in where clause:

and lvl <= (Pay_Run_Balances.Balance_Value/10)

Thanks.

SARAORC

Thanks for sharing.