SQL & PL/SQL :: How To Get Correct Minute Between Two Hours
Oct 22, 2012
i am using one query but not getting correct minutes.
here is my query:
v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);
here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.
here i should get value 370.
but i am getting value 350.
However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.have tried setting the interval as follows. However, they are not correct
Quote:trunc(sysdate+1) +4/24 The next interval will show 9:25:20pm.
CREATE TABLE Emp_addrs ( EMP_ID NUMBER(15) NOT NULL, ADDRESS_ID NUMBER(15) NOT NULL, SITE_USE_ID NUMBER(15) NOT NULL, SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL, SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A'); insert into Emp_addrs values ( '1207' , '1846', '2343','I'); insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'. Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
CREATE OR REPLACE VIEW V_CATALOGUER_REPORT (COUNT, EVENT_USER, TO_DATE, SHORT_NAME) AS SELECT COUNT (DISTINCT A.PART_REF),A.EVENT_USER, TO_DATE(A.EVENT_DATE, 'DD-MON-YY'),B.SHORT_NAME FROM J_SUPPLY_CHAIN_HIST A, J_SOURCE B
[code]...
i created one view,i link two tables ,i can see the correct data in this view,but the result from front end application is not correct.i cant see the dates are correct.
select job,case when deptno=10 then ename ELSE 'null' end e10 ,case when deptno=20 then ename ELSE 'null' end e20 ,case when deptno=30 then ename ELSE 'null' end e30 from emp order by 1;
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R 123 28-06-2012 21:36 Intransit Shipment 123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36 123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))
select * from test;
insert into test values (143,'frank','kadel,watson','j2098,k09876'); insert into test values (143,'steve','kadel,watson','j0987i,kuy765'); [code]....
The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.
For example:- Where id = 143, the resultset should be like this.
ID LAST_NAME FIRST_NAME EMPNO ---------- --------------- --------------- --------------- 143 frank kadel j2098 143 frank watson k09876 143 steve kadel j0987i 143 steve watson kuy765
sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.
CREATE TABLE ID_comments ( ID CHAR(10 BYTE) NOT NULL, S_COMMENTS VARCHAR2(255 BYTE), P_COMMENTS VARCHAR2(255 BYTE), C_COMMENTS VARCHAR2(255 BYTE) );
For each Id, I can have multiple records.
Below is the insert script of one of the ID:
Insert into ID_comments values ('0813654254','','JR/0813653606 single',''); Insert into ID_comments values ('0813654254','','JR/0813653606 single',''); Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE',''); Insert into ID_comments values ('0813654254','','JR',''); [code].......
Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.
I am using following query to get last 3 month ends. Here If month end falls on weekend(sat/sun) then it should have prior date
Where COB- Table name COB_DTE_ID_C- Date in numeric format (YYYYMMDD) COB_DAY_N- Days COB_MO_C- Month Number
I am not getting how to modify below query without much case when statements so that if I pass monthend date (20110531), It should give me
20110228 20110331 20110429
Its giving me
20110331 20110429 20110530
select * from ( select DISTINCT MAX (COB_DTE_ID_C) OVER (PARTITION BY COB_MO_C) from COB where COB_DTE_ID_C < 20110531 and COB_DTE_ID_C > to_char(add_months(to_date(20110531,'YYYYMMDD'),-3),'YYYYMMDD') and upper(COB_DAY_N) NOT IN ('SATURDAY','SUNDAY') ORDER BY 1 ) where rownum < 4
My colleague sent me a sql script to copy and execute in Toad for Data Analysts (see attached). Once I execute I get error messages on the date and a few other lines. My question is should I completely rewrite the script?
I would rather have this in a grid format anyway with the same data instead of how this script is pulling. I'm very green when it comes to this (if you didn't notice) and need some insight esp. on the date format considering the date column has 10 digits i.e. 1209105576.
A report we developed gives correct output when run in the report designer's previewer (paper layout). But when we deploy the report in the web server, give the same inputs and run it it gives the wrong output. I.e. it shows extra rows that should not be there.
Why do you think this is? We are unable to locate the problem source.
1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-
how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-
how do i check whether all dates in date column are in above format or not using sql query?
When I run the code below It runs very Long. It updates SUSR5 in the TEMPTABLE3 that has 112000 records. If I Change it when c>m to 2 to test. It runs very fast. The value for m is always between 10000 and 12000. That How many times it must loop to update the correct records.
I receive the output from only one of the nested blocks:
bad PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
i need to retrieve html using oracle and setting the correct user agent.I used the ut_http package and his fuction utl_http.request_pages.
I worked perfectly for me, execpt that i could be able to setup the correct user-agent.I read the documentation and i found that this instruction utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0'); should fix my problem, but when i used it, nothing changed and the webserver didn't recognize the correct user agent.
I post downhere a piece of the pl-sql procedure i made (it seems that i could find the correct "link" between utl_http.set_header instruction and utl_http.request_pieces instruction)
and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:
input.event_start_time is between EVENTS.event_date and EVENTS.event_end_date and input.event_end_time is between EVENTS.event_date and EVENTS.event_end_date
but to compare only the hours here! (HH24:MI) because the date (dd/mm/yyyy) is checked before..
I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.
Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime, MAX(stoptime) AS mx, MIN(starttime) AS mn,
SELECT audittimestamp + interval (SELECT EXTRACT(TIMEZONE_HOUR FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.
