PL/SQL :: Dates And Times In A Subquery With A Group By

Dec 20, 2012

I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.if I do a TO_char (visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.Is there a way to keep as a date in my sub query and then convert to a string?

it looks a little like this, with some other fields that I have to max, sum ...

visit     provider     person     visitdate
1     2     1     12/20/2012 10:30
2     2     2     12/20/2012 10:30
3     2     5     12/20/2012 11:30
4     3     3     12/21/2012 11:30
5     3     4     12/21/2012 11:30

I need to boil this down to

provider     visitdate
2     12/20/2012 10:30
3     12/21/2012 11:30
2     12/20/2012 11:30

Then I use that in a subquery where I use just the month and year TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR right now if I do a group by visitdate on the subquery it returns

provider     visitdate
2     12/20/2012
3     12/21/2012

even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM') it is still returning :

provider     visitdate
2     12/20/2012
3     12/21/2012

View 4 Replies


ADVERTISEMENT

Select Data Between Subquery Dates?

Sep 5, 2012

I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?

sql Code

AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
where max(b.effdt) between (SELECT term_begin_date AND term_end_date
from PS_TERM_TBL

[Code].....

View 2 Replies View Related

Displaying Records Between Specific Times On Specific Dates?

Apr 11, 2011

I have a database which consists of various orders and various field.

I have a variable called createddatetime . I want that whenever i should run the database it should display records from

Yesterday 06:00:00 am to Current Date 05:59:59 am

Now to implement this i tried to put this syntax

and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'

But nothing comes up

where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.

View 3 Replies View Related

Group By With Order By For Dates?

Mar 9, 2013

ii have written a query like thi:

select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags"
from p_in where order_date between '1-apr-11 and '31-mar-12'
group by to_char(order_date,'mon-yyyy')

the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE

View 1 Replies View Related

PL/SQL :: Count / Group By Between Dates

Oct 30, 2013

I am trying to count the number of IDs dropped and enrolled in each unit for each of the 4 terms between their perspective dates. There are 4 Terms and an ID can participate in a unit in any of these terms:

TERMSTART_DATEEND_DATE125-Feb-1318-Mar-13227-May-1317-Jun-13326-Aug-1316-Sep-13425-Nov-1316-Dec-13 .

I am trying to count how many IDs enrolled in a unit between those dates and how many doped before the end dateThe ENROL_DATE for each ID in a unit has to be between the given Term Dates to count. Unit KIS has 1 ENROL and one DROP in TERM 1 UNIT POL occurs in TERM 2 and 4 and both DROPUNIT LIN and 1 ENROL and 1 DROP in 2 different TERMS My problem is how do i specify count ENROL and count drop between the Term dates and then group by TERM and UNIT. 

{code}CREATE TABLE DAN_GR4(ID             NUMBER(12),STATUS           VARCHAR2(12),TERM             NUMBER(12),ENROL_DTE       DATE,TERM_START_DTE  DATE,TERM_END_DATE   DATE,UNIT            VARCHAR2 (12));  INSERT INTO  DAN_GR4 (ID,STATUS,TERM,ENROL_DTE,TERM_START_DTE,TERM_END_DATE,UNIT) VALUES ('1',    'ENROL'    ,'1',    '15-Mar-13'    ,'25-Feb-13' ,'18-Mar-13',  'KIS');INSERT INTO  DAN_GR4 (ID,STATUS,TERM,ENROL_DTE,TERM_START_DTE,TERM_END_DATE,UNIT) VALUES ('1',    'DROP'    ,'2',    '27-MAY-13'    ,'27-MAY-13' ,'17-JUN-13',    'POL');INSERT INTO  DAN_GR4 (ID,STATUS,TERM,ENROL_DTE,TERM_START_DTE,TERM_END_DATE,UNIT) VALUES ('1',    'DROP'    ,'2',    '27-JUN-13'    ,'27-
[code].....

View 2 Replies View Related

PL/SQL :: Closest Date From A Group Of Dates

Dec 19, 2012

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.

View 4 Replies View Related

Recovery Manager (RMAN) :: Archive Logs Group By Dates

Apr 30, 2013

10.2.0.4
SLES 11

This is the folder groupings of our archivelog folder:

oracle@sdb51:/u01/app/oracle/flash_recovery_area/PROD/archivelog> ls -lt
total 32
drwxr-x--- 2 oracle oinstall 4096 2013-04-30 12:42 2013_04_30
drwxr-x--- 2 oracle oinstall 4096 2013-04-29 12:35 2013_04_29
drwxr-x--- 2 oracle oinstall 4096 2013-04-28 23:42 2013_04_28
drwxr-x--- 2 oracle oinstall 4096 2013-04-24 12:20 2013_04_24
drwxr-x--- 2 oracle oinstall 4096 2013-04-23 22:00 2013_04_23
drwxr-x--- 2 oracle oinstall 4096 2013-04-21 02:01 2013_04_21
drwxr-x--- 2 oracle oinstall 4096 2013-04-19 22:00 2013_04_19
drwxr-x--- 2 oracle oinstall 4096 2013-04-18 18:46 2013_04_18

Are there supposedly complete folder by dates each day? So I am missing archivelogs of 27th, 26th,25th, etc?

View 7 Replies View Related

Finding Individual Dates Having Dates Plus 2 Days

Jan 26, 2011

I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10
1220.01.10
1321.01.10

1423.01.10

1526.01.10
1627.01.10

1729.01.10
1831.01.10

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

View 2 Replies View Related

SQL & PL/SQL :: Split A Date Into New Dates According To Black Out Dates?

Mar 10, 2011

Split a date into new dates according to black out dates!

Here is my tables:

CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;

[code]....

I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.

Note: Travel Date can be between 0000-01-01 - 9999 12 31

Sample:

Travel Date:

Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11

Black Out Dates:

BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29

Excepted Result:

New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11

Visually:

Travel Date : -----[--------------------------]--

A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------

Result :

X1 : -----------[--]--------------------
X2 : -----------------------[--------]--

Sample 2:

Travel Date : -[--------------------------------]--

BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------

Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--

How can I do it using PL SQL ?

View 5 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

PL/SQL :: How To Sum Times Value

Mar 18, 2013

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?

I know difference of two date column is number. So, first i need to know that data type i should use ?

Secondly, how i get the sum of the all stored time ? for example data are

1.30 -- One hour and thirty min.
2.45
3.50

View 20 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

SQL & PL/SQL :: How Many Times Do The Inner Execute

Oct 2, 2010

I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.

The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM

UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )

The situation here is that:

1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.

My question is: Do both the inner select statements execute 15000 times?

The statement executes within a second and updates 15000 rows. How is this made possible?

View 10 Replies View Related

SQL & PL/SQL :: How To Get Minutes Between Two Times

Oct 19, 2012

i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00

so in this it should return 360 minutes.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Rows N Times In A Result Set

Mar 9, 2010

How to get rows N times in a result set?

For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.

But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".

View 2 Replies View Related

SQL & PL/SQL :: Dealing With Times Recorded In UTC

Oct 8, 2011

I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.

New Zealand operates Daylight Savings, so twice a year it changes its clocks.

When New Zealand is on standard time it is UTC+12.

When New Zealand is on daylight savings time it is UTC+13.

Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.

I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.

I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.

Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?

View 6 Replies View Related

Select All Dates Between Two Dates

Oct 22, 2003

How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:

12/2/2003
12/3/2003
12/4/2003
12/5/2003

Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.

View 14 Replies View Related

How To Make A Command Loop Three Times

Jun 28, 2013

I have code that creates a sequence and a select statement that generates the next number in a sequence. It starts the sequence at 5 and every subsequent number is the previous number minus 3. The minimum value it can go to is 0. All I want is for my select statement to execute three times in a row. Is there any way I can do that?

create sequence MY_FIRST_SEQ
increment by -3
maxvalue 5
start with 5
minvalue 0
nocycle;

select MY_FIRST_SEQ.NEXTVAL from dual;

View 5 Replies View Related

Generate Last Seven Days Batch Run Times

Jan 8, 2013

I would the query to do the following:

1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Determine Number Of Times A Value Appears

Nov 11, 2012

I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.

for e.g.

Quote:trip table
-------
trip_id start_date end_date duration
445 01-jan-12 03-jan-12 3

Quote:pickup table
--------
pickup_id trip_id company
1 445 randomname
2 445 randomname
3 445 google.inc
4 878 randomname

with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.

View 2 Replies View Related

SQL & PL/SQL :: Populate Column Two Times With Different Criteria?

Mar 22, 2011

I am trying to query O/P from two tables.Hence, populate same column(Level_Name) two time on report based on its Level

Table =Level_Mst

Codeno Level_Name Parent_level
1 IT Dir 0
2 HR Dir 0
3 Assets Section 1
4 Payroll Section 2

Table=Users

User_id Name Top_level Bottom_Level
1 John 1 3
2 Smith 2 4

Desired O/P

Name Top_lvl Bottom_lvl Top_lvl_Name Bottom_Lvl_Name
John 1 3 IT Dir Assets_Section

Right now im getting name from level_mst either for top_level or for bottom_level .I want to show both names in one row.

View 6 Replies View Related

SQL & PL/SQL :: How To Fetch Two Times Same Explicit Cursor

Nov 25, 2012

I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.

I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.

FOR i IN 300..300 --18000
LOOP
y:=ymax-ysize*(i+0.5);

[Code]......

View 6 Replies View Related

SQL & PL/SQL :: Run Query Multiple Times With Time Gap

May 25, 2013

I need to run the below query and display the result every one hour.

Below code giving result only once.

I think my method is wrong.

SQL> declare
2 counter number :=0;
3 item varchar(300);
4 BEGIN
5 FOR item IN (

[Code]...

PL/SQL procedure successfully completed.

View 12 Replies View Related

Replication :: Job Failing Several Times In TOAD

Jan 25, 2011

In TOAD,I see one of my jobs is failing several times. However, I could refresh it manually.I found the following error. What is the cause of error and the way to rectify this.

ORA-23404: refresh group "ORA-23404: refresh group "schema_name"."job_name" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
"."" does not exist.

View 4 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

How To Copy Set Of Rows N Times And Append Them With Different Value For A Column

May 27, 2011

Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.

View 8 Replies View Related

Performance Tuning :: Number Of Times Sql Had Been Executed / Run?

Oct 25, 2011

how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?

View 3 Replies View Related

PL/SQL :: Sample Function Returns Many Times Of Expected Value

Nov 30, 2012

The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.

For example, we have thee tables with the following results:

Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000)
Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000)
Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)

The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"

I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?

View 2 Replies View Related

Forms :: FRM-40209 - Field Must Be Of Form XX-XX-XXX Coming Several Times

Oct 3, 2012

I have a form which has a Number field. I set it's Format Mask property to 9,99,99,99,99,999 so that I can format it comma separated and to restrict the user to enter decimal values. If the user enter any format other than this for example any decimal values, it is showing the message FRM-40209:Field must be of the form 9,99,99,99,99,999 which is fine. But when the user enters a decimal value and try to save the form it pop up this message several times.

I tried to catch this error in ON-ERROR trigger and display a message. But this is also pop up several times. I tried to raise form_trigger_failure also. This is also not working. I want this error message to come only once.

View 2 Replies View Related

Performance Tuning :: ASH Report - SQL Has Been Executed For 1567 Times?

Sep 12, 2012

In ASH report

there is a section that goes like this

SQL ID Planhashed Sampled # of Executions % ActivityEvent% Event Top Row Source
fdy93qpr1227 1567 7.58direct path read 3.65TABLE ACCESS - FULL

does it suggest that this SQL has been executed for 1567 times is this correct .

View 7 Replies View Related







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