I can give you the logic first i'll sort the start_date(already sorted in given example), then i'll compare the 2'nd id start date with 1'st id end date if it is less than the 1'st id end date, which means overlapping is there, then i'll group those 2 id's in to same group if not group them into 2 different groups.
I am new to oracle, I have request to build a query,
we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.
Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...
I'm looking to see if there's a solution to my problem that I can use within the context of my business application interface into an Oracle RDMS. I have access to write custom SQL statements and functions, but I am NOT able to create stored procedures using the interface I have.
The challenge I am having is comparing date ranges. I have a table containing two columns labelled START TS TIME and END TS TIME, both of type 'Date'. I have figured out how to query each row against a given Next Session Start and Next Session End and determine if each row overlaps that row.
I need a procedure that will be recursive: that is, set Next Session Start and Next Session End to START TS TIME and END TS TIME of the first row, compare all rows against it, then set Next Session Start and Next Session End to the next row, compare all rows, ... for all rows in the table. I want to know what the maximum number of matches is (i.e. the most time periods that overlap).
If I could use a stored procedure I could complete this query easily. Is there other techniques (i.e. functions) available to leverage in order compare each row of date ranges against ALL rows in the same table?
The data in the table was imported from a csv file and there is a relationship between the rows. Each combination of col1, col2 and col3 describes a full route of a journey. The row with an entry in col6 describes the full route and the other rows describes each leg in the route.
For example, for R1, the route is AA to BB via CC. Another example for R4 the route is FF to SS via XX, PP, and OO.
What i would like to do is missing a route. For example the route for R3 is DD to EE via FF. There is an entry for DD to FF but is missing an entry for FF to EE.
The results should return the following rows which are incomplete
Here is what i have come up with but it doesnt quite returned the correct result.
select * from tableA a Where not exists( select 1 from tableA b where instr(col6,col4,1)>0 and instr(col6,col1,1)>0) And a.col1=b.col1 And a.col2=b.col2 And a.col3=b.col3 )
I have a sets of data in table ow_ship_det , from which i want to group all the records which are having same sl_desc but with the condition that sl_qty is not more than 1000 and sl_wt not more than 50000, i managed to do it but the problem is i want the wieght(sl_wt) and qty(sl_qty) to be evenly distributed among groups or boxes for example take the first four records which have common sl_desc 'H170' Where the qauntities are 15000,15000,10000,10000 as per the condition and loop written in program it will bring the 2 boxes or serial numbers with first 3 weights into 1 box as 40000 and other box as 10000, which i dont want instead i want to have them as 25000 each.
CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER); insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300);
[code]...
--if you see above the weight is not balanced properly in batches 0001 for H170 Desc it should get divided equally as below
ob_batch OB_PM_CODE OB_DESC OB_QTY OB_WT 0001 A H170 300 15000 0001 C H170 300 10000 0002 B H170 300 15000 0002 D H170 300 10000
since the orgid 1 has changed the dept from org1 to org2 I do not want this to be appeared in the final count. Results should only include the orgid 2 since it didn't changed any dept.
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Name Hours date a810/11/2011 a 510/12/2011 a610/13/2011 a710/14/2011 a710/15/2011 a810/16/2011 a710/17/2011 a810/18/2011 a810/19/2011 a710/20/2011 a710/21/2011
If i want the sum of hours for 3 days range ,how should i do it.
E.g. say
name hrs startdate enddate
a 19 10/11/2011 10/13/2011 a 22 10/14/201110/16/2011 a 23 10/17/2011 10/19/2011
How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id. In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.
If, there is manager_id 117, it should create grp_id 3.
To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num
I am looking for an update statement for this issue.
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.
pas_codepas_profilesutp_idsutp_pricex 2664good stuff310069< because pbk_price is like 67 from that period 2664good stuff310071< because pbk_price is like 50 from other period
I thought I would make use of the following query, but I am not getting the proper results when applying it to a real table with more than 20 mln records:
SELECT trunc(R_DUR/6)*5+1 as range_start_rdur, trunc(R_DUR/6)*5+5 range_end_rdur, sum(noofan) as no_of_an, sum(sumofrdur) as sum_of_rdur, sum(sumofchdur) as sum_of_chdur, [Code] ...........
i have a table where there are codes of length 6 or length 12 some times, i need to add the summary of amount based on two different types of codes, one is adding the distinct codes which are of 6 char long and other sum will be based on from substr(7) till last.
create table strings ( strings_var varchar2(12),strings_amt number); insert into strings (strings_var,strings_amt) values ('02.01',10 ); insert into strings (strings_var,strings_amt) values ('02.01_A11111',15); insert into strings (strings_var,strings_amt) values ('02.02_A11111',15); insert into strings (strings_var,strings_amt) values ('03.01_B11111',15); insert into strings (strings_var,strings_amt) values ('03.02_B11111',15);
In my schema the employees table has a number of 55 rows in department_id 30.
How can I spit the employees table into views group by department_id as
- one view with no more than 55 rows (this view will contain only a department) - another view with more departments but whose number of rows is not > 55 but can contains 2 department_id (e.g.: 9, 10 and the sum of these rows is 43 but if I would like to bring another department the rows count will be > 55)
Allow me to preface this with the notice that I am not familiar with XML outside of its hierarchical structure, and am not familiar with what you can do with it using formatting.
As an example, let us say you have the following table:
Object_Type | Object_Name | Descriptor | ------------------------------------------------------------ Fruit | Apple | Crunchy | Fruit | Orange | Sour | Utensil | Pencil | Wooden |
Now let's say you want to query this table to return an XML format, which will be used in a web site to display the information, and you want to group the display by Object_Type, so that you want an XML format like this:
Is it possible to group it in a way so that Apple and Orange end up in the the same <Object Group>? Or is that meaningless and such grouping can be done on the web site itself by formatting the XML?
When a GROUP BY clause contains multiple columns, which grouping is the most major grouping?What puzzled me was, I never knew there was such a thing as a "most major grouping" in a GROUP BY clause. Anyway, the answer:
the first column listed in the GROUP BY clause . what this means in practice? It must mean something different to your bog standard "select sum(order value) from sales group by city,country,region" because in that case, I can't see how city has any more or less relevance to the query than region.
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
one is "ora" it is a 8i version 2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables. Q)Is it BEST practice? If not let me know the best practice to compare data in tables? Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
How to select the transactions out of the database that occurred within 70 seconds of each other. The toll_date field is a TIMESTAMP field.
Problem is, I seem to only get transactions that occurred within 70 minutes of each other. On the timestamp field I break the math down into the seconds in a day and I add 70. I then subtract that value and add that value to the timestamp and I should get anything between those values right?
Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.
in the below V_ERR is error count V_UPS is un processed count V_PROCESSED_COUNT is total to be processed.
I am expecting PASS result but it is giving FAIL.
DECLARE V_ERR NUMBER:=0; V_UPS NUMBER:=0; V_PROCESSED_COUNT NUMBER:=0; NIN NUMBER; BEGIN V_PROCESSED_COUNT:=1; [Code] .......
I want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
As part of our project, we need to perform table comparisons in two different databases. I am currently looking for various options to accomplish this.
One of them is doing minus operation between these two tables. Also, i have looked at the data compare option in toad utility.
