Need help in writing a procedure in Oracle

is this data present in some oracle table ?

what is the output data you wanted ?

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

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.

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

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

" 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

How can I set to 12 hr for this table only or to specific column

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

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.

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;

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;

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.

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

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 ?

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

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.

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

But then everytime I need to do it manually as this will go as a daily report genaration

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,

@ 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.

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...

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

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.

Please find the fress data from query :
Test-data.xls

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,

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...

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.

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 ?

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

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.

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

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;

Select allOpen in new window

Thanks nav kum v:  as the Oracle database is undergoing on maintenance , once that is fixed I will update you soon....  

Fine with me.

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.

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

Please find the attached file having the recent updated data table-export.csv

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

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

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 :)

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

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

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.

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

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 ....

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

Thanks

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.

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..