SQL Query Group ID Ranges To Chunks

SQL & PL/SQL :: Query To Split Period Into Date Ranges?

Apr 10, 2012

I have TableA which has data like below.

ID NAME CRT_DTE
1 AB 03/05/1992
2 EF 15/04/1995
3 CD 20/08/1995
4 GH 01/01/1999
5 UV 08/07/2001

[code]....

I want a query which splits the total time period (from min crt_dte to max crt_dte) into year ranges.For eg, lets say a range of 5 years then I need to get results like below.

start_dte end_dte
---------- ----------
03/05/1992 03/05/1997
03/05/1997 03/05/2002
03/05/2002 03/05/2007
03/05/2007 09/03/2012

how to write this query.

View 6 Replies View Related

SQL & PL/SQL :: Group By Query

Nov 5, 2011

I am new to PL/SQL, could you pls let me know how to solve this requirement using PL SQL

C-A | C-B
A123 | -1
B334 | 4
B567 | 2
B333 | -1
T777 | 2
Y774 | 3
T879 | 4
T654 | 3
T474 | 0
Y432 | -1

I need the output like this

C-1|C-2|C-3|C-4
3 |7 |3/10|7/10

Column-1 (Count of rows where Column-B = -1)
Column-2 (Count of rows where Column-B in(0,1,2,3,4)
Column-3 (Column-1/Total rows)
Column-4 (Column-2/Total rows)

View 5 Replies View Related

Query For Group By Date?

Apr 30, 2011

We have one oracle table, which has one column "Creation_Date" of the datatype date.

This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"

We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be

Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010

View 2 Replies View Related

SQL & PL/SQL :: GROUP BY When Running Query

Jul 29, 2010

i am facing group by issue when running the query.the error code is ORA-00979: not a GROUP BY expression.i suspect is due to the subquery in the SELECT clause.

SELECT
WHINR100.COMPANY,
WHINH210.SEQN,
FXINH039.OTBP,
WHINR100.BPID,
TCCOM100.NAMA,
WHINR100.ITEM,
WHINH210.RCNO,
FXINH051.btch,
case when (whinh210.ORNO in (select fxinh033.pdno from fxinh033))
[code]...

View 3 Replies View Related

Count And Group Query?

Apr 23, 2012

I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:

SELECT
mv.R1_State,
mv.subscriber_id,
mv.plan_code,
pm.description,
mv.line_of_business,

[code]...

But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???

Select
Count(pm.description),
mv.R1_State
FROM windsoradm.member_mv mv

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Query To Get Sequence Number In Group

Dec 7, 2010

I have Table Data in format :-

--------------------------
ABC
ABC
ABC
XYZ
XYZ

and i have a requirement where I need the output in this format.

-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2

what query to be used for this

View 2 Replies View Related

Forms :: Query In Record Group

Mar 6, 2011

I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.

p.s: That record group is built in run-time.

View 1 Replies View Related

Forms :: Get Record Group Query

Apr 3, 2010

How can I get record group query in code and chang it ?( for example change its where clus). I need to get its Query.

the record group exists and I must get its query not make it.

and I know about Populate_Group_With_Query for changing but cant I have the query to change?

FOR EXAMPLE :
my record group query is :
select a ,b from A where A.c=1;

and I use it in an LOv.

Now i want get this record group in code and change its WHERE cluse after pressing A BUTTON How can I do it?

View 2 Replies View Related

SQL & PL/SQL :: Adding Inner-query Select To Group By?

Aug 30, 2011

I have this query

select EAG.AUDIT_NUMBER Audit_Nbr,
EAG.AUDITEE_NAME Grantee_Name ,
EAG.EIN Grantee_EIN_IRS,
EAG.AUDIT_ISSUE_DATE Audit_Issue_Date,
MAX(AUDT.derive_audit_progress_status(EAG.SYS_AUDIT_ID )) Audit_Clesed_Date,
EAG.OIG_DUE_DATE Six_Month_Due_Date,

[code].....

I want o add additional column to this

I added this sql

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.

how to add the new column in group by clause.

View 6 Replies View Related

Group By Month - Query A Transaction Table?

Oct 19, 2010

sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.

View 2 Replies View Related

Query To Get Rows Of Latest Date From Each Group

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

SQL & PL/SQL :: How To Group Query Sequence Number Wise

Oct 3, 2010

I ve a data in table as follows

code status seqnce Length
B1 AVL 1 10
B2 AVL 2 10
B3 ASG 3 10
B4 AVL 4 10
B5 AVL 5 10
B6 AVL 6 10
B4 ASG 7 10
B4 ASG 8 10

I need to group the AVL status codes with the sum of lengths Ex) B1 to B2 - 20 B4 to B6 - 30 like that.

View 5 Replies View Related

PL/SQL :: Query With Group By - Fetch Data From Database

Jul 26, 2013

I am trying to write a proper query to fetch data from database. Scenario:

I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;

EMPNO     DEPTNO
---------- ----------
7654         30      7698         30     7788         20      7788         30      7876         20     7900         10      7900         30    7902     20    7934     10

scott@TESTCRM>

Ouput Expected is

EMPNO     DEPTNO
---------- ----------
7654         30      7698         30      7876         20      7902         20      7934         10

View 9 Replies View Related

SQL & PL/SQL :: Pivot Query - ORA-00937 - Not A Single Group Function?

May 8, 2012

I am having the following pivot query but I am not able to run it is giving error msg

ORA-00937: not a single-group group function

SELECT DISTINCT
C.SHORT_NAME,
MAX (
COUNT (DECODE (TO_CHAR (a.reg_date_cur, 'MON'), 'JAN', A.CAR_NO))

[code]...

View 6 Replies View Related

SQL & PL/SQL :: Split Ranges In Database?

May 5, 2010

I have been wracking my brain on this. Is there a way to write an SQL code that will combine split ranges within a table?

SAMPLE_TABLE
Common_FieldLow_ValueHigh_Value
1123123
11243000
130023005
130064000
135003501
130064500

I would like to combine any ranges that may exist. It is also possible that some row ranges may be nested in other rows.

END_RESULT
Common FieldLow ValueHigh Value
11233000
130024500

View 9 Replies View Related

SQL & PL/SQL :: To Produce Data In Ranges?

Feb 23, 2011

Activity date1 date2
R1 1/1/2011 31/1/2011
R1 2/1/2011 2/28/2011
R1 ... ...

I have a particular activity like R1 where I need to find the results for some periods as above. To be clear, if the activity is completed in between Jan 2011 to March 2011, then can I get data as like the above format?

View 20 Replies View Related

SQL & PL/SQL :: Grouping Results By Ranges?

Oct 21, 2010

I have this table,

Create table TBL_OK_HIST
(
DATE_KEY NUMBER,
A_N VARCHAR2(22 BYTE),
R_DUR VARCHAR2(8 BYTE),
CH_DUR VARCHAR2(8 BYTE),
REV VARCHAR2(20 BYTE)
)

insert into TBL_OK_HIST
values (20101010,123456768,5,20,2);
insert into TBL_OK_HIST
values (20101010,123496568,15,20,2);
insert into TBL_OK_HIST
values (20101012,122235768,25,25,3);
[Code] ......

Thus, applying the following would yeld:

Select * from TBL_OK_HIST

DATE_KEYA_N R_DUR CH_DUR REV
201010101234567685202
2010101012349656815202
2010101212223576825253
201010112345676819202
2010101234567681252527
2010101323456768136365
2010101056768123411202
2010101134681256717202
2010101068123456755559

generate the following results:

range_start_rdur range_end_rdur no_of_an sum_of_rdur sum_of_chdur sum_of_rev
1 5 1 5 20 2
6 10 1 9 20 2
11 15 2 26 40 4
16 20 1 17 20 2
21 25 1 25 25 3
26 30 0 0 0 0
31 35 0 0 0 0
36 40 1 36 36 5
41 45 0 0 0 0
46 50 0 0 0 0
51 55 2 107 107 16

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] ...........

View 1 Replies View Related

SQL & PL/SQL :: Inserting Date Ranges

Apr 30, 2010

Can we insert multiple dates within give range in a single insert statement say from 1st Feb to 31st MAY 04,something like after insertion it should resemble like below

01-FEB-04
02-FEB-04
03-FEB-04
04-FEB-04
05-FEB-04
.
.
.
31-MAY-04

View 5 Replies View Related

Select Multiple Date Ranges?

Aug 2, 2011

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...)?

View 4 Replies View Related

How To Check Condition Like Ranges Using Decode

Sep 3, 2008

we can check condition like ranges using decode?

View 3 Replies View Related

SQL & PL/SQL :: Grouping Totals By Date Ranges

Oct 7, 2010

I have to get totals from a table using different criteria, which I do like this:

<QUERY>
SELECT DISTINCT
SUM(CASE WHEN MYCONDITION1 THEN 1 ELSE 0 END) AS TOTAL1,
SUM(CASE WHEN MYCONDITION2 THEN 1 ELSE 0 END) AS TOTAL2
FROM TABLE1, TABLE2
WHERE COMMON_CONDITION1 AND COMMON_CONDITION2
AND datevalue1 >= DATE1 AND datevalue1 <= DATE2;
<QUERY>

This works fine and I get the intended result.Now, I have to repeat this for every week for the last 12 months, excluding holidays period. So, I generate a set of date ranges which will be used in the queries. So, I repeat the above sql statement for all the date ranges, which is a lengthy process.How can I do that in a single shot and get all totals for each date range.

View 4 Replies View Related

SQL & PL/SQL :: Find Out What Claims Overlap Other Via Date Ranges

Sep 3, 2010

Lets say I have a table,
Claim_id,dateA,dateB
it has 5 million rows

I need to see if any dateA,dateB of a claim_id falls within any other dateA,dateB of another claim ID

Basically
select * from table a, table b, where

(a.dateA,a.dateB) overlaps (b.dateA,b.dateB)

now I can write the query simply enough by aliasing the table 2x but no matter how I try I cant see a way to get around doing a Carteasion join

Index are ignored because it has to scan the full table anyway even if I hint the index

and the cost of the join ends up astronomical aka 5million rows X 5 million Rows....

And it ends up doing the full table scan a few times

AHHHHHHHHHHHHHHHHHH

this table is expected to grow to at least 20 million records

View 17 Replies View Related

SQL & PL/SQL :: Comparing Date Ranges Against All Rows In Same Table

Oct 30, 2011

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?

View 4 Replies View Related

SQL & PL/SQL :: Measure Time Overlap Between 3 Date Ranges

Mar 28, 2013

I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.
The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).

So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]

Here is the scenario:
---------------------

CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,

[Code].....

---------------------------------------------------------
---------------------------------------------------------
Now Active days is to be pulled for Two payees - TESTD01 and TESTD02 for the period of June month.
Desired Results :

Payee InActive_days Active_Days Period
---------------------------------------------------------------------------------------
TESTD01 13 17 June
TESTD02 14 16 June

I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.

View 11 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

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.

View 4 Replies View Related

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

Jun 23, 2011

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 ?

View 16 Replies View Related

Server Administration :: Increase Interval For Date Ranges - Maintaining Partitions?

Jun 3, 2010

I am studying Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2),

ORA-14767: Cannot specify this interval with existing high boundsby giving an example other than given in the above mentioned document.

the example given in the document is as follows:

To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:

CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)

[code]/...

View 3 Replies View Related

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

Sep 2, 2011

I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).

View 7 Replies View Related