PL/SQL :: Making Sql To Only Return Last 12 Months From Date?

Apr 21, 2013

i have written an sql to look as sales orders but i want it to only return the last 12 months worth of sales for the date its run, the date file [sdtrdj] is julian date so i used to_char to conver it to dd-mm-yyyy how can i get it to only return the last 12 months from the date its run

select
sdshan As "Location", sddcto As "Order Type", sddoco As "Order Number",sdlitm As "Product Code", sddsc1 As "Product Description",
to_char(to_date(sdtrdj + 1900000, 'yyyyddd'),'DD-MM-YY')As "Order Date", sduorg As "Order Qty", sdaexp As "Extended Amount", sdsoqs As "Quantity Shipped",
ibsrp4 As "Srp4", ibsrp6 As "Srp6", ibsrp7 As "srp7", ibsrp9 As "Srp9", ibsrp0 As "srp10", sdsrp2 As "Franchise"
From proddta.f4211, proddta.f4102
Where sdmcu = ' UKC001'
and sddcto = 'KO'
and sdlitm = iblitm and sdmcu = ibmcu
and sdsrp2 In ('504','973','322','236','232','856','233','566','590','470','343','266','279')

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Months Between Two Date

Apr 28, 2011

I want the months between two date. I want the months like this :

My parameter for the date from and to means i will enter start date and end date

Suppose Date From is 01-apr-11 and end date 31-mar-12

I want the output like this :
April
May
June
July
August
September
October
November
December
January
February
March

How to do this

View 12 Replies View Related

PL/SQL :: Fetching Date Less Than 3 Months

Sep 12, 2012

How can I fetch a date less than 3 months, if date is '31-Mar-2011' ?

View 3 Replies View Related

PL/SQL :: How To Get Min Date Of Every Month For Six Months

Mar 25, 2013

I have data like this.

Process_date SEQ_No
------------- ---------
16-MAR-13     733
09-MAR-13     732
02-MAR-13     731
24-FEB-13     730
16-FEB-13     728
09-FEB-13     727
02-FEB-13     726
26-JAN-13     725
21-JAN-13     724
12-JAN-13     723
05-JAN-13     722
29-DEC-12     721
24-DEC-12     720
15-DEC-12     719
08-DEC-12     718
03-DEC-12     717
22-NOV-12     716
17-NOV-12     715
10-NOV-12     714
03-NOV-12     713
29-OCT-12     712
23-OCT-12     711
13-OCT-12     710
05-OCT-12     709
28-SEP-12     708
22-SEP-12     707
15-SEP-12     706
08-SEP-12     705
01-SEP-12     704

every month admin will refresh actual data table and automatically this above table will update with unique seq_no and process_date.

I need to extract min date of every month(First refresh of last 6 months - excluding current month) and also seq_no related to that month so using joins(using seq_no - that is available in main table) i can combine actual data.

I need result like:

02-MAR-13     731 ( I don't need MAR as it should not take current month data)

so i need final result like below:

02-FEB-13     726
05-JAN-13     722
08-DEC-12     718
03-NOV-12     713
05-OCT-12     709
01-SEP-12     704

View 8 Replies View Related

SQL & PL/SQL :: Date - How To Get Past 12 Months From Current Month

Dec 17, 2011

I need to get the past 12 months from the current month

for example

input:

march2010

output:

apr2009
may2009
june2009
july2009
augest2009
september2009
october2009
november2009
december2009
january2010
february2010

View 23 Replies View Related

SQL & PL/SQL :: How To Return Just First Row Which Has Max Date

Sep 1, 2010

This query returns 2 rows and the output is displayed as well. how I can return just the first row where the max end_date is 4/30/2011?

select
pt.customer_number,
pt.customer_name,
lease.lease_number,
lease.lease_name,
lease.property_name_disp,
lease.location_code_disp,
MAX(pt.end_date) end_date,
pt.attribute1 Disabled
[code]...

View 19 Replies View Related

SQL & PL/SQL :: Return A Field In Date Format

Mar 23, 2011

I'm trying to return a field in a date format, but my minutes keep returning as 03. See example below:

TO_CHAR (i.editdate, 'mm/dd/yyyy HH24:MM:SS') AS "Date",

Actual data field in the table contains:
10/27/2010 1:07:42 PM

Data returned is:
10/27/2010 13:03:42

Why are the minutes incorrect?

View 10 Replies View Related

Return Results Where Employee ID That Has 2 Different Timezone IDs On Same Date

Jun 21, 2012

I have a table with the following columns

EMPLOYEEIDNUMBER(12,0)
PUNCHDTM DATE
TIMEZONEIDNUMBER(12,0)

I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example

EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456

To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.

View 3 Replies View Related

SQL & PL/SQL :: Return Most Recent Record Previous To Start Date?

Nov 18, 2011

I have a sub query (already dervived from several other tables) that has a list of children in with the date they started their course (tableofchildren). Child IDs may be duplicated in this query but each record will have unique start dates per child ID.

I have a second sub query which lists workers involved with the children in the first query (tableofworkers). A worker may be responsible for more than one child but has the unique child in the record to identify involvement with the child.

I need to join these queries together to return the child's record with the WorkerName and the AllocatedStartDate of the worker who was most recently involved with the child prior to the date (EnteredCourseDate) the child started their course (OutputWanted) - the worker associated with the child when they started their course.

A couple of points - I need to deploy this into another reporting application that doesn't support cursors etc or the 'With' operator. Also, I tend to join tables/queries in the Where clause so if it's possible that way that would be great.

OC.
create table tableofchildren
(ChildID varchar(20),
ChildName varchar (50),

[Code].....

View 13 Replies View Related

PL/SQL :: Attribute In DB - Return Difference Between Start_date And Current Date

Feb 15, 2013

I have an attribute in my DB called start_date of type date.I want a query that return the difference between the start_date and the current date.for example if start_date = 2/14/2013 *2*:35:00 PM and the current date = 2/14/2013 *1*:35:00 PM the query returns 1.

I want the result in hours.

View 13 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

SQL & PL/SQL :: Making Audit Trail

Oct 7, 2011

I want to make Audit Trail on every DML operation users does on record. I have many more tables but I mention some example tables.

My Tables are

Invoice Table

Ref_Number char(12), -- Auto Generated
Invoice_No char(12),
Date Date,
Date_Created Date,
Created_By char(50),
Modify_By char(50),
Date_Modified date,
Amount number;

Receipt Header Table
Receipt_No Char (12),--Auto Generated
Date Date,
Date_Created Date,
Created_By char(50),
Modify_By char(50)
Date_Modified date;

Receipt Detail Table
Receipt_No Char (12),
Serial_No,
Doc_Number Char(12),
Date_Created Date,
Created_By char(50),
Modify_By char(50),
Date_Modified date;

View 5 Replies View Related

SQL & PL/SQL :: Making Vertical Records To Horizontal

Aug 18, 2010

I need to build a query on making a vertical records to horizontal records. I did read somewhere saying that we can use decode function. But im still trying to understand how decode function can make a vertical records to horizontal records.

View 4 Replies View Related

Cannot Connect To Database After Switchover Without Making Changes In TNS Entry

Nov 19, 2011

I have an issue of not able to connect to the database after switchover without making changes in TNS entry after the switchover when I try to connect to then the primary database (which is standby before ) , the tns entry has entries for both the primary and secondary but the order of the primay and seconday database is to be changed manually till then it keep showing ERROR: ORA-01033: ORACLE initialization or shutdown in progress.

View 5 Replies View Related

Making A Select Statement To Join A Few Tables Together?

May 2, 2009

I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.

I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example

SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';

View 6 Replies View Related

Forms :: Making Form Of Email Sender

Mar 30, 2011

I want to make form of email sender.

View 2 Replies View Related

Backup & Recovery :: Making Backups With Oracle VSS Writer

Apr 19, 2012

I am going through the Oracle VSS writer , trying out various methods. Just keen to know if this is the right place to ask VSS related questions as I could not find any post related to VSS.

For Volume based VSS shadow copy backups for a logged database, I am unsure about the restore method using VSS writer. Also oracle doc does not mention any steps for volume based restore of a logged (archive log mode db) database.

View 2 Replies View Related

RMAN :: Connect To Auxiliary DB For Making Duplicate Database?

Jun 27, 2013

Previously it is working fine but today when i am trying to duplicate a database using rman not getting exactly error but the o/p is as below   

C:Usersdbadmin>rman target sys/tiger@na  Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 27 15:33:33 2013  Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.  connected to target database: NA (DBID=1572981579)  RMAN> connect auxiliary sys/tiger@da  connected to auxiliary database: NOIDA (DBID=1572981579, not open) 

It is correct that the rman is connected to the db with name na but not with db da Previously i am able to connect both the instance.

View 0 Replies View Related

NESTED ERROR When Making Admin Server A Windows Service?

May 6, 2013

I created a script to make my admin server a windows service. I successfully made it a service but when it starts it then shuts itself down. i ran a debug and found a error. I dont get what to do. I have tried changing the variables in the script and try it over and over again .....here is my script code and here is the error i got from debug output:

script

SETLOCAL
set DOMAIN_NAME=FRClassicDomain
set USERDOMAIN_HOME=C:/Oracle/Middleware/user_projects/domains/FRClassicDomain
set SERVER_NAME=FRweblogic

[Code]....

View 1 Replies View Related

Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?

Apr 8, 2013

splitting a table partition without making its primary key index ar any other indexes unusable.

I think it is possible to do so 10g onwards.

DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP

View 2 Replies View Related

Server Administration :: ORA-00607 / Internal Error Occurred While Making A Change To A Data Block

Mar 12, 2010

Error is occurred during the transaction...i.e.

ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [89], [83], [], [], [], [], []

View 7 Replies View Related

All Months Between Two Dates

Sep 17, 2008

I got all the month_numbers when i did this

[/b]select distinct t.f_month_number
from time_dim t
where
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)[b]

but when i add

select distinct t.f_month_number,p.start_date,round(replace(p.total_sal,',','')/12,2) as Monthly_sal
from time_dim t, employee p
where
t.f_date = p.start_date and
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)

i got only one month value.

View 6 Replies View Related

SQL & PL/SQL :: Last 6 Months Data

Jan 5, 2011

I need query to fetch last six months dyanamically based on the mon-yyyy passed by user

Input to query-JUN-2010

Result-
JAN-2010
FEB-2010
MAR-2010
APR-2010
MAY-2010
JUN-2010

View 3 Replies View Related

PL/SQL :: How To Get All Months With SQL Query

Aug 8, 2013

I want to display all months (JAN till DEC) in 1st column and their counts in 2nd column but as there are no records in JAN, FEB and march, so they are not coming up.

Get JAN, FEB etc with counts as 0?I know it can be done with NVL function but I don't know where to put it. I'm using oracle 10g on Windows XP. [code]SELECT '',    

CASE      WHEN b.mon= '01' THEN 'JAN' WHEN b.mon= '02' THEN 'FEB' WHEN b.mon= '03' THEN 'MAR'      WHEN b.mon= '04' THEN 'APR' WHEN b.mon= '05' THEN 'MAY' WHEN b.mon= '06' THEN 'JUN'      WHEN b.mon= '07' THEN 'JUL' WHEN b.mon= '08' THEN 'AUG' WHEN b.mon= '09' THEN 'SEP'      WHEN b.mon= '10' THEN 'OCT' WHEN b.mon= '11' THEN 'NOV' WHEN b.mon= '12' THEN 'DEC'    END Months, count(*) cnt  FROM    (SELECT con.fst_name first_name, con.last_name, usr.login USER_ID,        app.appl_src_cd Registration_Source,to_date(usr.created,'DD-MON-YY') Created_Date,      TO_CHAR(usr.created,'MM') mon      from siebel.s_user usr, siebel.s_contact con, siebel.S_PER_PRTNRAPPL app      where con.par_row_id = usr.row_id      and app.row_id = con.row_id      and app.appl_src_cd = 'Siebel eService'    AND TO_CHAR(usr.created,'YYYY') = :P415_YEAR    ) bgroup by b.monorder by 3 desc[/code] 

Right now getting output as....MONTHSCNTAPR4818JUL4543JUN4295MAY4190AUG541MAR20 What Jan, Feb, March etc in it too with count as 0.

View 3 Replies View Related

SQL & PL/SQL :: Printing Months Even Having No Data

Feb 26, 2012

I have table data in which we are entering Visitor's information.We need a monthly report with the count .There are some months which dont have data .So Names of month are not coming .Can i Print Month Name with 0 visitor.

Sample Data

Quote:ID Name Visit_date
1 ABC 01/02/2011
2 DEF 03/04/2011

im using query

Quote:SELECT count(ID) Nos ,TO_CHAR(visit_date,'MONTH') Month FROM DELEGATE_DETAILS WHERE visit_date BETWEEN '01/02/2011' AND '01/04/2011'
GROUP BY TO_CHAR(ARR_DATE,'MONTH')
ORDER BY TO_CHAR(ARR_DATE,'MONTH');
Output
Quote:Nos Month
1 February
1 April

Desired Output:-
Quote:Nos Month
1 February
0 March
1 April

View 2 Replies View Related

SQL & PL/SQL :: Display All The Months Between Two Dates?

Aug 17, 2010

I have this same problem and still i am getting one error message

ORA-00933: SQL command not properly ended

and the code in highlighted in 'by'

'from test_case partition by'

View 6 Replies View Related

SQL & PL/SQL :: Convert Days To Months

Nov 21, 2011

I have the following in my select statement and I am getting the days like 1130, 50, 60 etc

round(MAX (TO_DATE ('31-OCT-2011 23:59:59','dd-mon-yyyy hh24:mi:ss') - a.tx_dtime),0) DAYS

Now I want this to convert these days into months like
1.10 this denotes (one month 10 days)
1.25 this denotes (one month 25 days)
2.05 this denotes (two months 5 days)

I know I am using old Oracle but what to do my company is not spending to upgrade.

View 6 Replies View Related

SQL & PL/SQL :: Merge Number Of Months?

Sep 4, 2012

I need to calculate total number of months within a year that account existed for each SSN.

SSN CODE RECORD_DATE PAYMENT
--- ---- ----------- ----------------------
AAA 00 01-FEB-89 50
AAA 01 01-AUG-89 50
BBB 00 01-FEB-89 0
BBB 01 01-AUG-89 50
CCC 00 01-FEB-89 50
CCC 01 01-AUG-89 0

A non-zero payment indicates that the account was open on that date. A zero payment indicates that the account is closed and assumes that it was open since Jan-01.

AAA: code 00 was open in February, so it was open for 11 months (Feb-Dec)
code 01 was open in August, so it was open for 5 months (Aug-Dec)
So the result should be 11

BBB: 00 - closed in February, existed for 1 month
01 - open in August, existed for 5 months
Result should be 6.

CCC: 00 - 11 months(Feb to Dec)
01 - 7 months (Jan to Aug)
Result: 12 months

Need to get:
AAA 11
BBB 6
CCC 12

Here is the source table:

SELECT 'AAA' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'AAA' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'BBB' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 0 Payment from dual union all
SELECT 'BBB' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'CCC' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'CCC' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 0 Payment from dual union all
SELECT 'DDD' SSN, '00' CODE, '01-OCT-89' RECORD_DATE, 0 Payment from dual union all
SELECT 'DDD' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual

View 12 Replies View Related

SQL & PL/SQL :: Taking Months Off Sysdate?

Feb 25, 2010

I have a table called transaction_dw and I need to select all records that have an account balance that has been below 0 in the past 6 months. initial query I tried was:

select account_balance, timestamp
from transaction_dw
where account_balance < 0
and timestamp between sysdate and sysdate - 6;

but this is only taking 6 days off the sysdate rather than months, how I can get it to take off 6 months?

View 3 Replies View Related

PL/SQL :: To Find Months And Days Between 2?

Oct 9, 2013

 I want to find the months and days between 2 dates. For Eg. Date-1 : 25-Aug-2013Date-2 : 23-Oct-2013 If we consider every month as 30 days it should give 25-Aug-2013 to 30-Aug-2013 = 6 days 01-Sep-2013 to 30-Sep-2013 = 1 Month 23-Oct-2013 to 30-Oct-2013 =   8 days Total = 1 month and 14 days

View 33 Replies View Related







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