SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep WHERE ID =
[code].....
here is the completed query
select EAG.AUDIT_NUMBER Audit_Nbr, ( SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep
[code].....
when I try select from this query I get ORA-00904: "DATE1": invalid identifier.
I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.
I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.
Here is an example of dates:
Visit Date Actual Date ========================== 01-APR-09 19-MAR-10 16-NOV-09 19-MAR-10 17-MAR-10 19-MAR-10 21-MAR-10 19-MAR-10 04-APR-11 19-MAR-10 15-JUN-11 19-MAR-10 19-SEP-11 19-MAR-10 24-FEB-12 19-MAR-10
The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.
the Q1 and Q2 could have values 1,2,3,4,5 thats means total are 5 questions. i need to know the total users who select value from q1 group by the values from 1..5 the toal_users who select value from q2 values( a group).i need the following result
We have a table with timestamp column and having millions of records.We want to create a materialized view or query, which can give count based
-on some group by columns from table and -group by on condition (if count > 1000) and -group by on condition (if timestamp range for that group is > 1hr)
BANNER Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production "CORE10.2.0.5.0Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
I have a staff table with the columns staff_id and completion_date. The completion_date shows the date a staff member completed questionnaire. If the staff member did not complete the questionaire, then the completion_date column will be NULL.
Table Definition:
-- Create table create table staff ( staff_id number not null, completion_date date );
See attached text file (staff.sql) for Insert Statements.
The result set needs to have the following columns: ReportDate: the Sunday of each week. Completed: The number of staff who have completed the questionnaire by the ReportDate. NotCompleted: The number of staff who did not complete the questionnaire by the ReportDate. Total: The SUM of Completed and NotCompleted columns.
As the number of Completed goes up, the number of NotCompleted goes down. Eventually Completed will equal Total and NotCompleted goes to zero.
The result set would look similar as follows and used to generate a bar graph chart:
select max(PERIOD_DUE_DATE) , form_submission_id from form_submission group by form_submission_id
but this returns all the records, I need only the max date along with its form_submission_id.In reality Its a complex query but to explain my problem I putting this simple query, how to select max(column) and column2 from table.
How do I select only last date for each contragentid? So for contragentid = 111270 it should be only '14.05.2010'.
select dd.contragentid, decode(dd.ratingvalue,'PK1',1,'PK2',2,'PK3',2,'PSR',2,'UN4',2,'VVL',2,'BK',4,3) as ratingvalue from (select 36 as contragentid, 'UN1' as ratingvalue, '25.02.2010' as ratingstartdate from dual union all select 111270 as contragentid, 'PK1' as ratingvalue, '26.11.2009' as ratingstartdate from dual union all select 111270 as contragentid, 'PK3' as ratingvalue, '14.05.2010' as ratingstartdate from dual union all select 111270 as contragentid, 'BK' as ratingvalue, '14.06.2011' as ratingstartdate from dual ) dd where dd.ratingstartdate <= to_date('31.05.2010', 'DD.MM.YYYY')
Also I need to select all rows from second test case for those contragentid which absent in first case, it should be one statement for both cases.
select * from (select 5 as contragentid, 2 as ratingvalue from dual union all select 111270 as contragentid, 1 as ratingvalue from dual ) hh
BANNER Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production "CORE10.2.0.5.0Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".
The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).
I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.
. example:
create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2)); insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00); insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00); insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00); insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);
[Code]....
and p.supid in(select s.supid from supply s where months_between
I have data such as 'hours', 'date' when and employee worked on the project. What I need is to select the total amount of hours per month of March, April, May, etc...
I know how to select data per single date but wonder how to do it per multiple dates. How does one select total amount of hours per multiple date ranges (March, April...)?
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>' and index_name not in (select c1 from( select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
how to get the output in below format. Count how many times each file is selected in a month.
Output format should be like below.. ============================================== File_Name Jan Feb Mar Apr ---------- Dec ============================================== file1 2 1 3 0 ---------- 2 file2 1 0 2 1 ---------- 3 file-n 8 2 3 0 ---------- 2
In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.
select update_date from table1 where update_date between sysdate and sysdate-90
The above query retrun no data even data is there in the table for this range.
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
