SQL & PL/SQL :: Taking Latest Current Balance Using Analytical Query
Jan 22, 2013
I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.
SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'
[Code]...
This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?
View 9 Replies
ADVERTISEMENT
Aug 10, 2013
I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....
View 17 Replies
View Related
Jul 2, 2013
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
My table looks like this.
Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14
Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query :
----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))
[code].....
Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);
[code].....
View 1 Replies
View Related
Nov 18, 2011
We've got a query which returns one row, but uses an IN statement. The IN statement links to more than one row in the subquery. When we use a combination of DISTINCT and an ANALYTICAL sum, the sum total is multiplied by the number of rows in the sub query. Remove the DISTINCT and we get a single value.
A simplified example of the problem is below.
I can't see how a query which returns a single row then returns multiple values with the addition of a DISTINCT. Removing the analytical sum also provides a single row, but we need this in the actual query we're running. So it seems to be some combination of DISTINCT, ANALYTICAL SUM and IN query is causing multiple values to be returned.
CREATE TABLE go_test_distinct1
(gtd_value NUMBER);
-- Three identical values
-- To replicate the three identical values returned by
[code].....
View 12 Replies
View Related
Nov 30, 2010
I have a table which has the attached data.
Sample data is here
LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........
I am trying to do a report like this.
Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM
Output should be
11/28/2010 25 11 49 27
11/29/2010...
I am using analytical function to do this, It throws an error range cannot be used for dates.
View 5 Replies
View Related
Apr 3, 2012
I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.
drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........
View 10 Replies
View Related
Aug 9, 2012
I want to get rows by latest date for each group pf rows.
say my data is :
TYPE1 TYPE2 quantity DATE-ORDERED
sweets chocolate 10 05-FEB-2012
sweets chocolate 10 04-DEC-2012
sweets chocolate 10 08-FEB-2012
pastries chocolate 20 08-AUG-2012
[Code] ..........
I want to get results by latest date,
sweets chocolate 10 04-DEC-2012
pastries chocolate 20 08-AUG-2012
sweets vanilla 10 05-DEC-2012
pastries vanilla 20 05-NOV-2012
I have tried Queries with Max(DATE-ORDERED) and grouping it..its not showing me results because I don't have indexes in my data.
View 2 Replies
View Related
Mar 13, 2012
I need a query to get data for the latest date for some purchases.
My expected output would be
Date - Item - Price - Qty
10/6/2011 10129 .5 1
6/8/2011 10130 13.33 9
2/6/2011 10131 21.74 24
Below are the scripts for this.I have also attached it.
CREATE TABLE XX_PO_LINES_ALL
( CREATION_DATE DATE,
ITEM_ID NUMBER,
UNIT_PRICE NUMBER,
QUANTITY NUMBER )
[code]......
View 4 Replies
View Related
Jan 18, 2013
I am working with a new client and am still waiting on access to systems so I'm a bit hampered in looking at details of the database and environment. The database is OLTP and typical response time for returning result sets is under 2 seconds, often less than 1 second.
A simple SELECT on a few columns of v$session_longops (no subqueries, group by, having, etc) ran for more than 5 minutes.
View 3 Replies
View Related
Mar 2, 2013
How to find time log for query or any procedure like start time and end time and total time.
So that I can tune that queries properly.
Also how can we find estimated query running time.?
View -1 Replies
View Related
Jul 4, 2011
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies
View Related
Feb 19, 2013
I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.
View 6 Replies
View Related
Jan 24, 2013
My oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.
but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.
View 1 Replies
View Related
Apr 23, 2012
Attached query taking 80000 reads to execute...
View 8 Replies
View Related
Sep 1, 2010
For an query, cost was 16Lakhs and was taking 30min, I brought down the cost to 1.5lakhs, but still it is taking 30min.
There were many outer joins and same table has been Used(FROM clause) 5 times in the query. I have introduced WITH clause, and brought down the cost.
View 7 Replies
View Related
Sep 25, 2013
select
serialnumber from product where productid in
(select /*+ full parallel(producttask 16) */productid from producttask where
startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and producttasktypeid in
[code]....
Explain plan output:
Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |
[code]....
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)
[code]....
Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement
View 2 Replies
View Related
Aug 17, 2011
The below query is taking more than 5minutes to return the data for any criteria.The big tables are
SECURITY_POSITION_SUMMARY -- 60Million
WEB_TEAM_X_ACCOUNT_BM -- 26Million
and the rest of those are small tables..All the indexes are in place and I have tried with few hints but this query is slow.
WITH REPS
AS (SELECT DISTINCT REP_SET.FILTER_TOKEN
FROM (SELECT /*+ INDEX (wdsd WEBDATASETDTL_PK_TEAM) */
DISTINCT
WDSD.DATA_SETTING_ID, WDSD.FILTER_TOKEN
FROM WEB_DATA_SETTING_DETAIL WDSD,
[code]....
View 1 Replies
View Related
Jul 12, 2013
I have an excel spreadsheet that queries an oracle data for some information. I am trying to get the information shown to only be from the current month. I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out. Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".
View 6 Replies
View Related
Dec 24, 2012
The below query is taking high CPU almost 98% and longer time to execute.
SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....
View 15 Replies
View Related
Jul 18, 2012
I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:
CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)
i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.
View 26 Replies
View Related
Mar 29, 2012
hint of which DBA views we need to query to find current running queries which are not using indexex?
I suppose v$session is one , what else we need to look to?
View 1 Replies
View Related
Oct 27, 2010
I need query to retrive the default tablespace of the current user, when the user is not a dba, i.e. user doesn't have access to dba_users table.
View 2 Replies
View Related
Jun 19, 2012
I have a table for exampl,
emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012
i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.
my output like this.....
emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)
View 2 Replies
View Related
May 11, 2010
I have a form with multiple tabs, each tab showing one data block. Many of the blocks are standard in enter-query mode. I want to allow the users to navigate through the tabs while the blocks are in enter-query mode.how can i do this?
View 3 Replies
View Related
May 30, 2012
What is the difference between the following . In my schema all are giving the same results with some different format
SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
View 1 Replies
View Related
Sep 22, 2010
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
View 5 Replies
View Related
Nov 29, 2012
i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type,
v_area_suid in i_purchase_h.pih_area_suid%type,
v_invoice_dt in i_purchase_h.pih_invoice_date%type,
v_pih_suid in i_purchase_h.pih_suid%type,
v_type in i_purchase_h.pih_type%type) is
[code]......
my output is
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
View 3 Replies
View Related
Mar 19, 2011
I have one table where the item stock balance can be taken.What i want is like a ageing report based on the ending balance.If the ending balance is 10,000 for item a.It should bring all the Receipts which have qty lesser than 10,000.
I managed to write one function like below to get the receipts numbers but the format is 3778578-3778612-3790428 and i want to convert them into rows like
3778578
3778612
3790428
The function used is as below.
CREATE OR REPLACE FUNCTION l_get_rcpt (
l_item IN VARCHAR2,
l_date DATE,
l_bal IN NUMBER
)
[code]...
View 1 Replies
View Related
Mar 24, 2013
Is there a way to find running balance for accounts, suppose i have two tables.one for transaction and one summary of balance.
i want to have the closing balance based on transactions subtracted or added based on credit and debit for example.it will add to the running total if its debit and subtract if its credit and final output is the balance.
create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);
insert into acnt_trans values ('1001','02-FEB-2013',200,0);
insert into acnt_trans values ('1001','03-FEB-2013',0,100);
insert into acnt_trans values ('1001','04-FEB-2013',50,0);
[code]....
--I want the output as below.if i run the report as of '04-feb-2013'
trans_date , acnt_code , debit_amt,credit_amt , balance
02-feb-2013 1001 200 0 3200
03-feb-2013 1001 0 100 3100
04-feb-2013 1001 50 0 3150 -- this is the closing balance.
View 4 Replies
View Related
Sep 3, 2010
My problem is that I am working with a balance (Infor/WorkBrain - Time & Attendance) I have a balance called Bank Holiday(bal_id 10044). Every holiday that an employee works, he is allowed to take another day off in lieu of the day he worked. The employee has up to twelve months to take that holiday.
My job is to figure out when he worked that holiday (not a problem) and send an email/note at the 11 month mark and notify the employee and supervisors that said employee has 1 month to use his banked holiday.
Here is what the employee_balance log looks like.
EBLOG_IDEMP_IDBAL_IDEBLOG_DELTAWRKS_WORK_DATEWRKS_IDEBLOG_MESSAGE
51200128537100441 10/15/2010 5281229TCODE_NAME:FR_HWS
51150828537100441 7/1/2010 5136881TCODE_NAME:FR_HWS
51150928537100441 7/27/2010 5136942TCODE_NAME:FR_HWS
51150528537100441 8/1/2010 5137137TCODE_NAME:FR_HWS
5294372853710044-1 8/31/2010 5137173TCODE_NAME:FR_HWO
52947828537100441 6/3/2010 5001014TCODE_NAME:FR_HWS
My problem is the data/constraints I am working with. I can tell what day the holiday was based on a time code (EBLOG_MESSAGE) and EBLOG_DELTA shows a positive 1. I can also tell when the employee has used a bank holiday (EBLOG_DELTA is -1) and EBLOG_MESSAGE = FR_HWO.
But how to I perform the deduction so that I get four most current dates. Meaning since my first date that the employee gets for a holiday wokred is 6/3/2010 but the employee used a bank holiday on 8/31/2010. So I want to display the dates 7/1/2010, 7/27/2010, 8/1/2010 and 10/15/2010.
My preference would be not to add anything new to this table as it is a core table for Infor/WorkBrain.
View 2 Replies
View Related
