SQL & PL/SQL :: Count Of Records On Each Date?

Jan 26, 2011

PART_REF REGISTERED_BYREGISTERED_DATE
DCA544519 SVK 1/3/2011
DCA544520 SJA 1/3/2011
DCA544539 SJA 1/3/2011
DCA544572 THS 1/3/2011
DCA544608 GKA 1/3/2011
DCA544610 GKA 1/3/2011
DCA544611 THS1 1/3/2011

I just want a to get the total number of records registerd on particular date by particular user...

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Rows With Same Date - Count Date Only Once

Jan 18, 2011

I'm trying to run a script that will sum column values from a table using only a certain period of time in example below only January submissions. However, some of those rows have the same date and I want to have the latest time row of that day in the count.

Oracle 10g

sample 'submit' table

pk loan submit_time
1 105000 1/17/2011 08:05:23 AM
2 204954 1/17/2011 07:59:53 AM
3 159000 1/14/2011 05:25:35 PM
4 215000 1/05/2011 01:06:12 PM
5 340415 12/18/2010 4:34:12 PM

View 2 Replies View Related

SQL & PL/SQL :: Count Records Per Every 10 Minutes

Jun 6, 2011

How to count records per every 3 minutes ? we don't want SPs to get answer. Instead of we want single query to get this output.

The sample data has been enclosed with it.

View 13 Replies View Related

SQL & PL/SQL :: Get Count Of Records By Month

Aug 17, 2011

I have the following table. In this table i'm trying to get the count of records by month when its value (SIGNAGE_ONE_LANE_ROAD_AHEAD) is 'Yes' and its giving me the wrong count.

AUDIT_DATE SIGNAGE_ONE_LANE_ROAD_AHEAD SIGNAGE_LANE_CLOSED_AHEAD

1/5/2011 9:30 N/A N/A
2/9/2011 11:50 Yes Yes
2/21/2011 10:00 Yes Yes
2/22/2011 7:00 N/A N/A
3/7/2011 13:35 Yes N/A
3/8/2011 9:15 N/A N/A
3/9/2011 8:40 N/A N/A
3/10/2011 10:00 N/A N/A
[code]....

View 15 Replies View Related

SQL & PL/SQL :: How Many (count Of) Records Got Updated

Dec 8, 2011

I Have a table with 100records.after sometime i updated some records . But after that i want to know how many (count of) records got updated

View 3 Replies View Related

PL/SQL :: Get Records Count Of All Tables

Sep 26, 2012

I am trying to get the record count of all tables using dynamic query. I don't know how to put the value in placeholder. I tried the below code.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
CURSOR table_list
IS
select OBJECT_NAME from user_objects
where object_type in ('TABLE')
[code].......          

View 4 Replies View Related

PL/SQL :: Get Count Of Particular Table Records

Mar 11, 2013

We have table, which maintain log record of gl table. I don't know how much data exist in that table but problem is taking too much time while counting whole records.

View 11 Replies View Related

SQL & PL/SQL :: Get Number Of Records (not A Simple Count())

Mar 4, 2010

using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;

---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---

the output will be as below;

fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF

However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Item List

Apr 17, 2012

One of the tables is having

SQL> select count(1) from item_list;

COUNT(1)
----------
14356529

To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%

I tried finding the fragmentation using the below query.

select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"
from dba_tables
where table_name like ITEM_LIST%'
and owner = 'TEST_USR'
and blocks != 0
order by 4 desc

TABLE_NAME table size kb actual data in table kb
------------------------------ ------------- -----------------------
wasted space kb percent fragmented
--------------- ------------------
ITEM_LIST 5163360 4318302.27
845057.73 16.3664306

View 15 Replies View Related

SQL & PL/SQL :: How To Get Distinct Count Of Records From CSV File

Mar 18, 2010

the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id

"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"

I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4

View 15 Replies View Related

SQL & PL/SQL :: Total Records Count Calculation

Jan 14, 2013

We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .

View 7 Replies View Related

PL/SQL :: How To Count Duplicate Records In A Table

Jun 15, 2012

query to count the duplicate records and to display duplicate records in a table. Iam using oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Count Of Records In Table With Different Contents

Mar 31, 2010

I am having a table with contents like

item id, name
100,s_enter
100,p_enter
200,s_enter

i would like the output to be

Variable name cnt_s_enter=1 (this is for 200)
cnt_s_p_enter =1 (this is becoz 100 is both in s_enter and p_enter)

I am working with basic case but thats not working.

select
sum(case when name='s_enter' then 1 else 0 end),
sum(case when name in (s_enter,p_enter) then 1 else 0 end)
from table

View 3 Replies View Related

Run A Count On Portion Of Date

Aug 30, 2012

I am trying to run a query that will count the number of each occurrence of a combined month and day. Birthdays are formatted as: 1947-07-26 00:00:00

I need to query everyone in the system who share the same month & day, but not year & time. For example:

1927-07-26 00:00:00
1927-07-26 00:00:00
1987-07-26 00:00:00
1965-07-26 00:00:00
1981-03-14 00:00:00

would give me a Count of 4 for 7/26 and a Count of 1 for 3/14

View 5 Replies View Related

Getting Count Of Records / Returning Zero For Null Results?

Jul 23, 2009

I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.

This is the pre-existing query:

SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')

The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.

View 14 Replies View Related

SQL & PL/SQL :: Identify / Count Records In Ref Cursor Without Actually Fetching?

Jan 22, 2013

/* Formatted on 22/01/2013 19:32:50 */
CREATE OR REPLACE PROCEDURE test_rdm_miles (
p_ref_cursor OUT SYS_REFCURSOR
p_success NUMBER)
IS
BEGIN
OPEN p_ref_cursor FOR
SELECT 5168 mem_uid,

[code]....

I have a Procedure with out parameters as a REF CURSOR and response message as p_success.This ref cursor will be returned to the calling service. Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it. Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.

Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.

one more solution is

OPEN
FETCH
CLOSE
OPEN (AGAIN) { this will lead to redundancy)

View 7 Replies View Related

SQL & PL/SQL :: Count Data Within Date Range?

Nov 15, 2010

I need to know the counts(into buckets) for each event_date for a given event_id.

I have 4 buckets that I'm trying to calculate

1. Past (from Today)
2. Today
3. > Today but within 3 days in the future
4. More than 3 days in the future

WITH event_data AS
(SELECT '1' event_id, SYSDATE - 1 event_date
FROM dual
UNION ALL
SELECT '1' event_id, SYSDATE
FROM dual

[code]....

Sample Output:

EVENT_IDPASTTODAYWITHIN_3_DAY_WINDOWOUTSIDE_3_DAY_WINDOW
1 111 1
2 102 0

View 3 Replies View Related

Reports & Discoverer :: Count Records With Not Null Column

Jun 25, 2013

i want to count group above report records! want to count department group employees there in time is not null!

my report query

SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....

im doing it with formula column!

function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......

View 18 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

Get Count Of Records Grouped By Another Field From Transactions Table

Nov 5, 2009

I have a table that cannot be changed with a field called transaction_reference in the transactions table. This field contains any number of some values in a look-up table called codes.

The table codes contains 'AA', 'BB', 'CC'.

A typical transaction_reference field may look like 'CC BB' or 'AA' or 'AA CC' or 'AA CC BB' - any number, any order.My goal is to get a count of records grouped by another field from the transactions table.

Transactions table example:
transaction_id | transaction_reference | family
---------------------------------------------
1 | AA BB | foo
2 | BB CC | bar
3 | BB | hello
4 | AA CC BB | foo
5 | BB AA | bar

So the results should look like:

family | code | count
foo | AA | 2
foo | BB | 2
foo | CC | 1
bar | AA | 1
bar | BB | 2
bar | CC | 1
hello | AA | 0
hello | BB | 1
hello | CC | 0

If the counts of 0 (like the third to last and last line above) don't show up I'm ok with that.I put together an explode function like this one here but I'm really not sure where to go from here. I can split the transaction_reference, but I'm not sure what to compare it to or how.

I realize that a field in the transactions table for AA, BB, and CC would be ideal, but I can't do that... the powers that be won't let me change the table.

for each exploded segment from transaction_reference
look for it in the codes table
if it exists, add 1 to the count

View 7 Replies View Related

SQL & PL/SQL :: Group By Count And Date Time Range

Feb 3, 2011

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)

View 4 Replies View Related

List Count Of Rows Where DATE Field Is Not Null?

Apr 1, 2010

I need to list a count of rows where a DATE field is not null and group those counts by day.

Here's my sql so far...

SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;

Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?

View 2 Replies View Related

SQL & PL/SQL :: Count Of Date Difference Using Pending State Value In Table Field

Feb 2, 2012

Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.

Example as needed:
NoPendingDays = 23 (8+15)

I have attached Create table and Insert table values sample as SQL file.

View 10 Replies View Related

PL/SQL :: Create View Which Is Aggregate Count Of Member Records / Grouped By Business_unit / Gender / Age Per Year

Oct 13, 2012

Using Oracle 11g...We have a table in our database of data with the following information:

MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]

The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:

business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573

The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Records Based On Max Date

May 18, 2011

I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL

and if it is not null ,i shud get the records of MAX date.

How would i do this. Below is my query,by which i can get records based on either maxdate or Null date

select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........

View 10 Replies View Related

Find Records That Have Most Current Date?

Apr 13, 2004

I have two tables

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|

table_1 contain records on employee and the supervisor they are under at a certain date.

As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3

Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

How to write an SQL statement to perform this?

View 6 Replies View Related

SQL & PL/SQL :: MAX DATE - Getting Records Out Of Source Table

Sep 28, 2011

Below is my requirement,

Source Table: SRC

COL1 DATE_CREATED CREATED_BY
1 27-SEP-2011 GURU
1 28-SEP-2011 SANKAR

Target Table:TGT

COl1 DATE_CREATED CREATED_BY
1 28-SEP-2011 SANKAR

I need to take the MAX of date_created record and store it in target.

I tried,

select max(date_created), col1, created_by from src
group by col1, created_by,date_created

Which is giving me 2 records which i don't want. How to get only one record out of that source table?

View 2 Replies View Related

SQL & PL/SQL :: Counting Records Within A Date Range?

Nov 1, 2011

I am trying to write part of an SQL where it gives me a count of bookings in any 6 month period made from the first booking.Example of records

enquirynumberenquiryaddresssubjectcodebookingdate
613651 Burberry AvenueBCHR20/10/2008 07:00:00
613801 Burberry AvenueBCHR20/11/2008 07:00:00693021 Barberry AvenueBCHR07/09/2009 07:00:00

I am so far getting 3 as a count result based on SQL below. I want the count to return 2 (because its inside the 6 month range):

SELECT
ce1.enquirynumber,
ce1.enquiryaddress,
es1.subjectcode,
b1.bookingdate,
(SELECT count(b2.bookingdate)
[code]....

View 2 Replies View Related

SQL & PL/SQL :: Select Records Where Date Is Max From Tables

Oct 6, 2011

I've got 3 tables and I need to select records where date is max from these tables.

select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438

how to do that ?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved