SQL & PL/SQL :: Procedure To Calculate Summary Session?
Aug 13, 2013
I have a below requirement from client.
Create a procedure generate_daily_session_summary that will read the pcm_session_summary (pk sess_id) table and create a per day summary in the pcm_session_summary_daily table.
1. Summarize all records from pcm_session_summary for session start timestamp (START_TS) greater than the max existing SESS_DT from pcm_session_summary_daily table and less than the start of current day. Insert the records into the pcm_session_summary_daily table.
2. Populate the fields in pcm_session_summary_daily as follows :
sess_dt - truncated date from pcm_session_summary.start_ts unique_user_cnt - count of unique cws ids encountered in that day, sess_cnt number - count of total sessions in that day , max_concurrent_sess_cnt number - maximum number of concurrent sessions at any point of time in that day. Concurrent sessions are defined as those that have overlapping start_ts or end_ts - sessions that were active at the same moment in time would be concurrent with each other.
3. The procedure will be scheduled to run every day in a batch job and will summarize session data until the end of the previous day
I have the table with below details.
desc PCM_SESSION_SUMMARY
Name Null Type
---------- -------- ------------
SESS_ID NOT NULL NUMBER
USER_CUPID VARCHAR2(10)
START_TS TIMESTAMP(6)
END_TS TIMESTAMP(6)
ACT_CNT NUMBER
[code]....
I have written the below query to get the result for question no:1
select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from pcm_session_summary_daily where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;
However i tried a lot but not sure how to get maximum concurrent session count for a day.One of my friend comment below.
The Pcm_session_summary_daily is empty so you need to handle nulls when doing max. That will fix the issue of no results for the Greater Than condition.
You need to get concurrent count of sessions with respect to one another, the query that you have will not work. Do it in PL/SQL using procedural logic. The queries would return multiple rows, you need to handle that.
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
I have an urgent requirement to kill an existing session if a new session starts for the same user. I have been reading lot of blogs and posts on the above topic, but could clearly tell me how to do it.
I thought of putting a process in 101 page when login button is pressed to catch this and kill the old session.
We are using Apex 4.0/Oracle 10gR2/ Hp-Ux, We noticed that there is a process Running in Two Sessions that seems running since 3 Hours using 60% CPU,** 30% given below sql it executes in two Sessions!
declare function x return boolean is begin -- if instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY')>0 then declare l_position number := 0; begin loop l_position := instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) ; exit when instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) = 0 ;
Table: temp_user_access NAME Null? Type ------------------------------- --------- ----- USERGROUP VARCHAR2(255) USERNAME NOT NULL VARCHAR2(40) NICKNAME VARCHAR2(40) AAACC VARCHAR2(70) SYSTEM VARCHAR2(10)
I have one table which has two columns name,qty and it has data like arif,3 pcs i want to display it in 3 lines if the qty is 3 and in 2 lines if the qty is 2 using sql query
I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate OrderId Subtotal CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL") AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
EFFECTIVE_START_DATE: SCD field, Date ABACUS moves from one department to another.
EFFECTIVE_END_DATE: SCD field, Date ABACUS left the department from another. To keep history of movement.
The sample report shows possible scenario on my data.
I want to build monthly report to cater from departmental headcount on monthly basis, starting from from ABACUS start date.
see sample report below
MONTH | DEPARTMENT |NEW_HIRES | RELOCATION (OUT) | RELOCATION (IN) | NET TOTAL PREV MONTH | NET TOTAL PRESENT MONTH | WHERE MONTH: REPORT MONTH DEPARTMENT: THE DEPARTMENTS IN THE ORGANIZATION NEW_HIRES: NUMBER OF NEW STAFFS HIRED THAT MONTH BASE ON EMP_START_DATE RELOCATION_OUT: NUMBER OF STAFFS MOVEMENT TO ANOTHER DEPARTMENT (EMP_END_DATE IS NULL, EFFECTIVE_START_DATE AND
I've been experiencing strange Oracle behavior from time to time when using "explain plan". what could be the reason? (I'm going to create an Oracle ticket when I could find the cause...)
The total cost of a query was low (ex. ~500) while sub-parts of the query has high cost (ex. ~10000).I don't want to provide the query itself as the issue is observed for huge queries (like 2M characters!).The execution plan looks like this:
and we click on create summary folders we got this error Summary management not available because : this user has no privilige or quota to create materialized view in the current schema External summary registeration not available because : this user has no privilige or quota to create materialized view in the current schema
I have one group wise report and my requirement is i need to print both the Outputs in one single report first, is group with details and the summary in the last page showing machine code and summary of qty as totals ,I have given below test case .
insert into ot_cut_detail values ('L1','01','1001',2); insert into ot_cut_detail values ('L1','01','1002',2); insert into ot_cut_detail values ('L1','01','1003',2); insert into ot_cut_detail values ('L2','01','1004',2); insert into ot_cut_detail values ('L2','01','1005',2);
I'm wanting to create a query that will give me a summary of parts and labor from work orders. However there are three tables. work_order, parts, labor. Sometimes there will only be parts, sometimes just labor, or sometimes both. Well my query will only return results when they are on both. Sample query is below. Do I need to create a temp table to gather the data or is there a way to do this with a sub query.
select work_order.wono, sum(parts.cost), sum(labor.cost) from work_order, parts, labor where work_order.wono = parts.wono and work_order.wono = labor.wono group by work_order.wono;
ck_amt is checkbox.. ck_amt_tot is total of jtt_amt_1 [but total only those record whose checkbox is checked]
My task is like this When i checked checkbox whatever value in jtt_amt transfer to jtt_amt_1 field
but i can change value in jtt_amt_1 field ...i want to take addition of that changed field and show that sumation in ck_amt_tot.
I write trigger when-checkbox-changed like this ------------------------------------------------- IF :jou_tra1_tab.ck_amt = 'Y' THEN set_item_INSTANCE_property ('jou_tra1_tab.jtt_amt_1',CURRENT_RECORD, UPDATE_ALLOWED, PROPERTY_TRUE); :jou_tra1_tab.jtt_amt_1 := :jou_tra1_tab.jtt_amt; else :jou_tra1_tab.jtt_amt_1 := 0; END IF;
and when validation item trigger for jtt_amt_1 : -------------------------------------------------- IF :jou_tra1_tab.ck_amt = 'Y' then :jou_tra1_tab.ck_amt_tot := :jou_tra1_tab.ck_amt_tot + :jtt_amt_1 ; else :jou_tra1_tab.ck_amt_tot := :jou_tra1_tab.ck_amt_tot - :jtt_amt_1 ;
But when i changed value in jtt_amt_1 field i cant get write summation .
how to put exceptions while defining the summary item ? In one of my datablock there are certain lines whose "inactive_flag" gets a "Y" value on certain events. I want my summary item to exclude such amounts from summing up.
I have got another curly one with regard to Sql Loader. My original issue via [message #513696] was resolved. In summary, SQL Loader opens a comma delimited csv file and it is able to successfully populate the appropriate Oracle table. However, during the run to process each unique csv file, SQL Loader produces a both a .log file (indicating what transpired during the load) and a .bad file of the records from the csv file that where rejected by SQL Loader.
the users have requested that "is it possible to update a separate table of the summary results of what went wrong and what was right?" It just so happens that the csv file contains some summary records (rows) with processing audit infor...as the following .bad file wrote out.... ___________________________ ModuleID,ModuleSN,Lat,Lon,GMT Time,Tag Count,GMT Date,Client,Farm,Field,Variety,Machine No Module ID ,No SN ,-30.178958,149.63844,9:55:58,0,21/04/2011,101,Widgen ,9,71BRF ,5987 3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987 ,,,,,,,,,,, Total Modules,1602,,,,,,,,,, Mis-reads,1,,,,,,,,,, Mis-read %,0.06,,,,,,,,,,
1. The first record (starting in the 1st postion is "ModuleID") is a heading record in the csv file and can be disregarded. 2. The secord record (starting in the 1st postion is "No Module ID") is an error record in the csv file and can be disregarded. 3. The third record (starting in the 1st postion 3500B9880611170260179BFB, ) is a valid record, however, is was rejected as it is a duplicate of a preceeding record ...and the log indicates ...
Record 124: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS. ORA-00001: unique constraint (PTLIVE.MODULE_SERIAL_NUMBER_UK) violated
4. The fourth record (starting in the 1st postion is ,,,,,,,,,,, ) is a blank record in the csv file and can be disregarded. 5. The fifth record (starting in the 1st postion is "Total Modules" has a value of 1602 which I need to capture to write into a separate table. 6. The sixth record (starting in the 1st postion is "Mis-reads" ) has a value of 1 which I need to capture to write into a separate table. 7. The last record in the csv file and can be disregarded.
The summary Oracle table is CREATE TABLE PTLIVE.SUBMODULES_SUMMARY ( IMPORT_CSV_FILE_NAME VARCHAR2(256 ), TOTAL_SUBMODULES_LOADED NUMBER (5), TOTAL_SUBMODULES_MIS_READ NUMVER (5), DATEIMPORTED DATE default SYSDATE )
Now I would like to trap the value 1602 and load that into the Oracle column TOTAL_SUBMODULES_LOADED and the value 1 and load this into TOTAL_SUBMODULES_MIS_READ.
The name of the csv file is known and I can (as the previous [message #513696] resolved) simply use a SQL Loader control file command > IMPORT_ CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv".
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR -------- ---------------------------------------------------------------- . ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.
Emp1 : To record the employe's information and weekly rest day. Attendance :- To record daily attendance. Leave_appovd : To record the approved leaves. Holiday : To record the holidays.
W = Attendance R = Weekly Rest L = Leave H = Holiday A = Absent
My requirement is; I what create new table/view which contains all value of both table and new column called Total Amount. Total amount=SUM(Quantity*Unit_Price+Central_sales_Tax+Service_Tax+ValueAdd_Tax+Entry_Tax+Professional_Tax)
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .
