Need help in writing a procedure in Oracle

pinkuray
pinkuray used Ask the Experts™
on
I have a excel file data attached here :
Columns are as below:

Team      Rep        Total Calls      Total Duration      Date

Where I want to output by giving an from data and to sysdate(today's date)
So the report should give me Like as below:

Team ,Rep,Total Calls ,Total Duration ,WeeklyAverageCalls,WeeklyAverageDuration,MonthlyAvgCalls,MonthlyAvgDuration,YearlyAvg Call and lastly YearlyAvgDuration .

Should have in a single row.

Is there any way where I can pass the dates as a parameter so that It will calculate accordingly.

Test-data.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Naveen KumarProduction Manager / Application Support Manager

Commented:
is this data present in some oracle table ?
Naveen KumarProduction Manager / Application Support Manager

Commented:
what is the output data you wanted ?

Can you confirm the data you had given in the excel is the input data ?

Author

Commented:
The data is not loaded into oracle database...
I also want to load it 1st then generate the report.
I am failing in loading the data for column total_duration and date.

Can you suggest me what should be the proper structure for loading sucessfully the file into oracle db
and also if I need to change the format of the file.

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Naveen KumarProduction Manager / Application Support Manager

Commented:
how are you loading the data into the table ? sqlloader or some procedure or any other means ?

Is this question to deal with the data loading issues ? or to deal with the query after the data gets loaded into the table ?

Thanks

Author

Commented:
For both I have issue :
1st loading the data into database and then writing a procedure with date param to get report as posted before.

I was loading the data into DB through SQL Developer but I see an Issue with that :
column “Total Duration” comes with default date i.e. 1/10/1900  12:00:00 AM   if the data in excel sheet is “0:00:00”

The correct data is  “0:00:00”
 But when you click in excel sheet it converts it and make it into another format where all my loading goes wrong.

Where I want the data like  “0:00:00” but if try to format it and load it shows the same value with "12:00:00"

Can you please suggest on this

Naveen KumarProduction Manager / Application Support Manager

Commented:
" I was loading the data into DB through SQL Developer but I see an Issue with that :
column “Total Duration” comes with default date i.e. 1/10/1900  12:00:00 AM   if the data in excel sheet is “0:00:00”
The correct data is  “0:00:00”
 But when you click in excel sheet it converts it and make it into another format where all my loading goes wrong.
Where I want the data like  “0:00:00” but if try to format it and load it shows the same value with "12:00:00"
Can you please suggest on this " ----> Nothing incorrect with this. This is working fine. It is just the way you are display it in sql developer. If you display in 24 hour format, then 12:00:00 AM will become like 00:00:00

Thanks

Author

Commented:
How can I set to 12 hr for this table only or to specific column
Naveen KumarProduction Manager / Application Support Manager

Commented:
what is the query you are using in sql developer to see the data ? give me that query and i can modify it.

Author

Commented:
Here you go with the sql query :

select * from P_DETAIL;
Result:
Team               Rep                                TotalCalls       TotalDuration     Call_Date
-----------------------------------------------------------------------------------------------------
Bteam      Ramos, Neicy      0      12:00:00      06-OCT-10
LTeam      O''Chuida                            0                   12:00:00      06-OCT-10

You can see two Issues in this :

One with TotalDuration and another one with Rep Name for 2nd record :
O''Chuida  Origainal name in file is O'Chuida

Can you also suggest this.


Naveen KumarProduction Manager / Application Support Manager

Commented:
what is the data type of the TotalDuration  ? I think it is set to date right.

But i believe you wanted to store only the time portion in hh24:mi:ss format in it right ?

try the below :

select team, rep, totalcalls, to_char(totalduration,'hh24:mi:ss') total_duration , call_date from P_DETAIL;
Naveen KumarProduction Manager / Application Support Manager

Commented:
try the revised query : ( this will take care of both the issues you wanted )

select team, replace(rep,'''''','''') rep, totalcalls, to_char(totalduration,'hh24:mi:ss') total_duration , call_date from P_DETAIL;

Author

Commented:
I was directly loading the file into Oracle database and made it something like this :

Team     varchar2(40),
Rep       varchar2(40),
TOTAL_CALLS   number,
TOTAL_DURATION   VARCHAR2(21),
CALL_DATE   date

Do I need to change this ?

Please let me know the correct datatype for loading this.
Naveen KumarProduction Manager / Application Support Manager

Commented:
i think the best thing is to store the total_duration in seconds in excel itself and then store it as a number data type in the table so that we can easily get the queries to give sum, avg,etc otherwise we end up in difficulty there.

for example, in excel if the total duration is 01:01:03, then you should a final value as 423

i,e 1 hour * 60 * 60 = 3600   +
     1 min * 60 = 60 +
      3 secs = 3
=  3663 ( so in excel this value you can put for total duration and load it into table with number data type )

Thanks

Author

Commented:
So If i click on Format the column in excel then which Category I should be Using it and in what sample .

Can you please bring some light to this ?
Naveen KumarProduction Manager / Application Support Manager

Commented:
1) How did you get the data into excel first ?
2)Do you need only date or only time portion or both date/time value for this TOTAL DURATION ?


a) as i see some records have date portion along with time in it but only time portion is shown because of cell formats ( excel cells D2 to D12 )
b) some records do not have date portion in it ( cells D13 to D56 ) and only time portion is there and that is displayed.
c) again some records have some date portion in it along with time and so on..

Can you first get the data in this "Total Duration" column to the same format which ever one you want with date & time or only time. Once this is sorted, we can see how to load this into oracle.

Thanks

Author

Commented:
1) Data is loaded to excel by other source where I dont have any control.
2) I want only the time to be loaded to my column
a) you are correct but this is how I get the data everytime and that cannot be fixed. So need some theory to do it by my own where I can get only the time by formatting it.
b) True but if you format the excel to time then you will can see what is the correct data.
c) Exartly  for this reason I was gettting error out while loading the data into oracle


For all the question we just need to format the excel category to time and then you can get all the proper data.
Naveen KumarProduction Manager / Application Support Manager

Commented:
formatting the excel - just shows how the data is seen in the excel cells and everyone will think only time portion is there but if you look in the formula bar some cells do have date portion in it .

i think you can only do it manually as there are only 100+ records which have this issue. so just get the data into the proper format and then i upload here and i can verify it for you.

If everything is fine, then we can load to the table.

Thanks

Author

Commented:
But then everytime I need to do it manually as this will go as a daily report genaration
Naveen KumarProduction Manager / Application Support Manager

Commented:
in that case first get this investigated and understand how you get some value with date & time and some only with time and so on... --> "Data is loaded to excel by other source where I dont have any control."

Unless this is fixed, you cannot properly load data into table and use it for reporting.

Thanks,

Author

Commented:
@ Nav Kum V.

I have loaded the data into Oracle Db with all the required format with SQL Loader without chaning any thing on excel.

Now can you help me writing the procedure with date parameter for calculating the Month to date ,year to date and week to date like one single row as below:

Team ,Rep,Total Calls ,Total_Duration ,WeeklyAverageCalls,WeeklyAverageDuration,MonthlyAvgCalls,MonthlyAvgDuration,YearlyAvg Call and lastly YearlyAvgDuration .

The report should go something like from date and to date give given then the above row should calculate and give you the result.

Naveen KumarProduction Manager / Application Support Manager

Commented:
Can you please provide sample output data in the excel itself for the input data which you had given ?

Also can you please post the data types of all the columns in this table ? if total_duration is varchar2, it will become a complex task to calculate average etc...

Author

Commented:
Structure of the Tbale is like below:

create table pstatus
(TEAM   VARCHAR2(20)  ,
REP     VARCHAR2(25) ,
TOTAL_CALLS  NUMBER,
TOTAL_DURATION  VARCHAR2(19),
CALL_DATE  DATE)  ;

I dont have any sample output now ...I need to calculate manually and then I can provide ..
I will Upload the sample soon
Naveen KumarProduction Manager / Application Support Manager

Commented:
also provide me the fresh input data because some records are having date and some not for the TOTAL_DURATION in the excel which you had given.

You told me that you have loaded data into oracle table using sqlloader. So just query the table and give the fresh input data in an excel along with what outputs you need.

I can then come up with the query you need.

Author

Commented:
Please find the fress data from query :
Test-data.xls
Naveen KumarProduction Manager / Application Support Manager

Commented:
did you check the fresh data in the excel in the column call_duration column.. again it has the same issues as i told before.

excel d12 cell has date value but d13 cell does not have date and only time portion is there.

The disadvantage is that this is a varchar column and if you hava data like this, it is not possible to get the query you wanted.

You run the below query to see how bad your data is...

select call_duration, length(call_duration) from pstatus;

for example, if you data is all fine then the length of call_duration should all have the same value.

Thanks,

Author

Commented:
I have run this query where the result shows me as below:

select distinct (length(total_duration)) from pstatus;
Result:
------------------
7

It is having the same value as 7 nothing else...
Naveen KumarProduction Manager / Application Support Manager

Commented:
ok.. do you think it is possilble for you to add another column in the same table to have the time duration in seconds instead of varchar2 as that will help to do the calculations easily ?

I can give you the update for this table to populate the new column which you are going to add.

Author

Commented:
Sure thats fine I was wondering is it possible if we could convert the values at the time of inserting to the table into seconds then it would solve the automation process to, so I dont have to change the value from excel like let the excel sheet comes in the same manner , if we can convert it to seconds  while inserting the data to table then everything goes fine.

Can you please try to make in this way ?
Naveen KumarProduction Manager / Application Support Manager

Commented:
doing that conversion stuff is not easier in sql*loader as you are using that to load the data right ?

may be we can setup a trigger before insert into this table to see whether we can convert the values for the duration_secs column and as usual you can use the sql loader to load this file.

Thanks

Author

Commented:
Thats sounds really great to have a trigger on a table to convert the column record to seconds but we need to change the table structure .

After which we can make our procedure easily.
Please provide me the code.

Thanks.
Naveen KumarProduction Manager / Application Support Manager

Commented:
Do you know how to write triggers or no ?

I will not be able to write code for you due to time constraints. But i can help if you can come up with the basic code for the trigger.

First get the table altered to have a new column called something like duration_secs with number data type.

Thanks

Author

Commented:
I have added the column to the existing table and here is the code attached for trigger ...
Just you need to help me in doing the conversion the total_duration column to seconds :

The trigger code is just a smaple where i need to add your conversion formula :


-- Table is been alterd now 
Alter Table phone_status add ( total_duration_seconds number);

-- Trigger for converting the total_duration value to seconds
CREATE OR REPLACE TRIGGER convert_duration_sec
AFTER INSERT
    ON phone_status
    FOR EACH ROW

declare
    v_total_duration varchar2(10);

begin
    -- Need to add the conversion formula 
    SELECT convert total_duration INTO v_total_duration
    FROM phone_status;

    -- Insert record into phone_status table
    INSERT INTO phone_status
     ( total_duration_seconds )
    VALUES
     (  v_total_duration );

END;

Open in new window

Production Manager / Application Support Manager
Commented:
good.. try this :

CREATE OR REPLACE TRIGGER convert_duration_sec
BEFORE INSERT
    ON phone_status
    FOR EACH ROW

declare
    v_total_duration varchar2(10);
  v_dur_secs number;
begin
 
v_dur_secs := to_number(substr(:new.total_duration,1,1)) * 60 * 60;
v_dur_secs := v_dur_secs + ( to_number(substr(:new.total_duration,3,2)) * 60 ) ;
v_dur_secs := v_dur_secs + to_number(substr(:new.total_duration,6,2)) ;

:new.total_duration_seconds := v_dur_secs;

dbms_output.put_line('debug message trigger:dur:'|| nvl(:new.total_duration,'***') || ':2:' || nvl(v_dur_secs,-99) );

END;
/

Author

Commented:
Thanks nav kum v:  as the Oracle database is undergoing on maintenance , once that is fixed I will update you soon....  
Naveen KumarProduction Manager / Application Support Manager

Commented:
Fine with me.

Author

Commented:
Hi...
I have loaded the file into the system and it generated the records with second based on table trigger.

So now the main procedure has to be written with date param.
Naveen KumarProduction Manager / Application Support Manager

Commented:
can you please send me the data in excel for all the columns, so i can load them into my table and then give the queries you wanted.

Thanks

Author

Commented:
Please find the attached file having the recent updated data table-export.csv
Naveen KumarProduction Manager / Application Support Manager

Commented:
I appreciate if you can provide me sample output data in another excel atleast for first 1 or 2 sample input records which you have provided in the table-export.csv excel file. This would help to give me the query you wanted precisely.

I am bit not clear whether the output should have that many records in the input data or it should be aggregated ( i,e should it grouped etc ) ??

Thanks
Naveen KumarProduction Manager / Application Support Manager

Commented:
also would be nice if you can explain what is the logic should i use for these columns ?

WeeklyAverageCalls, ---> do you mean sum(calls)/number of weeks in a year or do you mean sum(calls) / number of weeks until now in the year or some other logic ?

WeeklyAverageDuration ---> similarly as above.

Please explain similarly for the below on what logic should i use them to compute it.

MonthlyAvgCalls
MonthlyAvgDuration
YearlyAvg Call
YearlyAvgDuration
Naveen KumarProduction Manager / Application Support Manager

Commented:
So now i am all ready with the data loaded into sample table in my test database. So if you just let me know the sample output and logic to be used, we can complete the query and close this question as this is going on like a project :)

Author

Commented:
Hi...

Sorry for the delay:

I have the output of the required data as an example ...

So I am attaching the file with the data I have and the target sheet for how I want them to be..

Thanks Test.xlsx
Naveen KumarProduction Manager / Application Support Manager

Commented:
try this. It uses to get the total calls for the current week, current month, current year. Test it and let me know for any changes.

with x as ( select to_char(sysdate,'mm') mm,
to_char(sysdate,'yyyy') yy ,
to_char(sysdate,'ww') ww
from dual )
select rep,
sum(case when to_char(call_date,'ww')= x.ww then total_calls else 0 end) weektodate,
sum(case when to_char(call_date,'mm')= x.mm then total_calls else 0 end) monthtodate,
sum(case when to_char(call_date,'yyyy')= x.yy then total_calls else 0 end) yeartodate
from pinkuray_tbl , x
group by rep
order by rep

Author

Commented:
this is not giving me the proper result :

Bentz, Mark         0      0      427
Brady, Ryan                           0                    0      327
Bristol, Mary        0      0      1
Brogan, Christopher      0      0      346
Brown, Stephanie      0      0      194
Byrd, Francis      0      0      381

Where result should be:


Rep      WeekToDate      MonthToDate      YearToDate
Bentz, Mark      107      427      5124
Brady, Ryan      82      327      3924
Bristol, Mary      0      1      12
Brogan, Christopher      87      346      4152
Brown, Stephanie      49      194      2328
Byrd, Francis      95      381      4572
Christine, Joe      108      433      5196
DeRosa, Roseann      116      465      5580
Fallorin, Jennilyn      150      599      7188
Gaynor, Paul      121      483      5796
Goffredo, Katie      106      424      5088
Kapetanakis, Kyle      86      343      4116



As the attached ffile have the target result for the Source data , So based on that file the data is not matching.
Naveen KumarProduction Manager / Application Support Manager

Commented:
please understand the query you are running. i already told that it will take current month/current year/current week. so it is obvious that it will not bring data for current week and current month as we are in the end of november but your data is in october right ?

with x as ( select to_char(sysdate,'mm') mm,
to_char(sysdate,'yyyy') yy ,
to_char(sysdate,'ww') ww
from dual )
select rep,
sum(case when to_char(call_date,'ww')= x.ww then total_calls else 0 end) weektodate,
sum(case when to_char(call_date,'mm')= x.mm then total_calls else 0 end) monthtodate,
sum(case when to_char(call_date,'yyyy')= x.yy then total_calls else 0 end) yeartodate
from pinkuray_tbl , x
group by rep
order by rep

You gave me the excel with the output you wanted.

But did you tell me the logic on how to get those values at least for one record ? I had asked this already in my previous updates but you did not explain. Hence i came up with the query and told you that it is going to work with respect to current year, month and week as i do not get the logic what you need.

Please explain for one or two sample output records on how to arrive at those values and we can easily modify the query to get that output.

Thanks

Author

Commented:
Hi...

I am not sure how the calculation is taking place the only thing I have is the output i.e. extracted from external source.

where they also have the same data but the output was as the target data I have provided earlier.

The report i.e. we are getting is from external source where we don't have a any access.

If this is possible to do it then ok else I have to close this ticket as I dont have the calculation for how to find week to date , month to date and year to date ....
Naveen KumarProduction Manager / Application Support Manager

Commented:
close it as i have no idea on what logic do i need to apply to get that data.

Thanks
Naveen KumarProduction Manager / Application Support Manager

Commented:
I would suggest to close this question by accepting these updates 34016597 , 33997743 , 33946965 as these were the ones which have got the text based stuff ( time ) stored into a numeric column through a trigger which eases the numerical calculations.

It is not advisible to delete this question as those 3 updates might be useful to someone on how to convert the text based time stuff into a numeric column in a table through a trigger.

Author

Commented:
Thanks Nav ,.... I am closing this as I dont have the formula for calculating the Week to day and like wise but thanks for youe help..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial