SQL & PL/SQL :: Number Of Months The Contract Was Open

Aug 21, 2012

I have a contract that has several categories with payments in each one. If there is a payment=0, then a category is considered closed. The contract is considered closed when all categories are closed.

Date Category Payments
01/01/07 1 5
01/01/07 2 7.5
01/01/07 3 6
05/01/07 2 0
09/01/07 1 0
11/01/07 3 0

(date here is in mm/dd/yy)

I need a SQL that will return the number of months within the year that contract was open. In the example above, the last category closed on 11/01, so the result should be 10. If the last line was not there, the contract would still be open in 2007 and the result should be 12.

View 33 Replies


ADVERTISEMENT

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

PL/SQL :: Convert A Number To Years And Months

Jul 30, 2012

I would like to convert a number to years & months.

e.g. 119 = 9 years and 11 months

This would be displayed as 9.11

Is there an oracle function or sql that can calculate this value,

View 8 Replies View Related

SQL & PL/SQL :: Procedure To Compute Ratios Across Variable Number Of Months

Nov 19, 2011

the problem below:

I have a table AlertData below:

DeptIDMONTHCount
192010-041392
192010-051134
192010-061094
192010-071333
292010-042217

[Code]...

Within each DeptID group I need to calculate absolute change of 'Count' column between previous and current months and compare change value with threshold.

If ratio >= threshold N number of times I need to make a note of that event. Threshold = 0.1 N = 2 - alert needs to exceed threshold two consequtive times

Here is data processing algorithm:

1. Calculate change between month 2010-04 and 2010-05: abs((1134/1392 - 1))= 0.18;
2. check change value against threshold: 0.18 > 0.1
3. Threshold was exceeded, set alert_fired_cnt counter to = 1
4. Once alert fired it creates a baseline for comparison - I need to use Count from month 2010-04: We're now in month 2010-06: abs(1094 / 1392 - 1)=0.21
5. check change value against threshold: 0.21 > 0.1
6. Threshold was exceeded, increment alert_fired_cnt counter by 1 = 2
7. At this point alert exceede threshold two times, I need to set a alert_triggered flag = 1 and reset alert_fired_cnt = 0 for further calculations
8. We're in montn 2010-07: abs(1333/1294-1)=0.03
8. check change value against threshold: 0.03 < 0.1
9. Since threshold was not exceeded, keep alert_fired_cnt counter to = 0

Above algorithm needs to be run for all DeptID groups.

I load above data into an associative array and loop through elements. I am having trouble keeping computations within each DeptID group.

View 18 Replies View Related

Reports & Discoverer :: Select Weeks Number Per Months And Year As 1 To 52 On Matrix Report 6i?

Aug 27, 2010

wanna to make matrix report retrieve months year and the number of weeks from dual table it supposes to the weeks number is 52 or 53 week months and weeks on the rows please find attach pic it show what i need to do for exmple

month name: January February March April May June July August September Nov October December
no of week : 1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-20-21-22....... TO 52 week

View 5 Replies View Related

SQL & PL/SQL :: Calculate Date When Contract Can Be Terminated

Jul 27, 2012

I've got the following table:

ID.................Date...........................number...period.....termination
434473......2012-04-18 00:00:00.000...............6.......month(s)...end of month
443080......2012-02-26 00:00:00.000...............36......month(s).....end of quarter

Now it's getting complicated. Date is the start date of a contract.

I need to calculate a date when the contract can be terminated and must compare it with the current date. Lets take the first row. In column "termination" is the termination date. A contract can be terminated at "end of month" (I need to check the last day of a month. In order to get the real termination date we need to subtract the column "number".

example 30.01.2012 - 7 month = 30.06. 2011

The thing I can do it, but my logic works only if the date is in the same year. I've been thinking a lot...sql is not important first....Have you got a theoretical solution?

View 11 Replies View Related

JVM :: JDBC Driver DatabaseMetaData.getCrossReference Contract Not Respected?

Jul 29, 2013

Since JDK 6, DatabaseMetaData.getCrossReference contract is :Retrieves a description of the foreign key columns in the given foreign key table that reference the primary key or the columns representing a unique constraint of the parent table (could be the same or a different table). But the Oracle JDBC Driver executed query is :

SELECT
  NULL AS pktable_cat,
  p.owner as pktable_schem,
  p.table_name as pktable_name,
  pc.column_name as pkcolumn_name,
 
[code]...

View 0 Replies View Related

Session Restriction - Limit Number Of Open Application Actions By Some Os User?

Sep 17, 2013

,in 11g,is there a way I could limit the number of open application actions by some os user ?We have an application where users are executing the same thing while the last is not yet finished,so we have several same things runninng at the same time executed by the same user.

Can we restrict that somehow through the database or that needs to be  done through application?

View 4 Replies View Related

Real Application Clusters :: Unable To Open Wallet / ORA-28353 / Failed To Open Wallet

Jul 11, 2012

I want to create a wallet on RAC setup.I have two node setup.I have created the wallet directory under shared folder /u01/oradata/$ORACLE_SID/wallet

I am Unable to open wallet.I tried this using the below command

SQL> alter system set encryption key identified by "aryabhat";
alter system set encryption key identified by "aryabhat"
*
ERROR at line 1:
ORA-28353: failed to open wallet

Following is the content of sqlnet.ora file

(path : /u01/app/11.2.0/grid/network/admin/sqlnet.ora)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/oradata/$ORACLE_SID/wallet/)))

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

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 :: 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

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

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

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 :: Split Months In Sql Statement

Oct 29, 2012

I need to display the month as below, in a sql statements.

MONTH
JAN-1-15
JAN-16-31
FEB-1-15
FEB-15-28
MAR-1-15
MAR-15-31

i tired with this, but it dint come

select trunc(sysdate, 'year') one, trunc(sysdate, 'year') + floor((last_day(trunc(sysdate, 'year')) + 1 - trunc(sysdate, 'year'))/2) - 1 two,
trunc(sysdate, 'year') + floor((last_day(trunc(sysdate, 'year')) + 1 - trunc(sysdate, 'year'))/2) three, last_day(trunc(sysdate, 'year')) four from dual

View 2 Replies View Related

SQL & PL/SQL :: Pivot Table Comparison Between Months

Jun 28, 2010

SQL> select * from query;

CUSTO SWITCH DATE_X METRIC COUNT_X SEQUENCE_NO FILE
------- ------- --------- ------- ---------- ----------- ----
JCI S1 28-JUN-10 PORT1 10 4 TNB
JCI S1 28-JUN-10 PORT2 4 4 TNB
JCI S1 28-JUN-10 PORT3 8 4 TNB
JCI S2 28-JUN-10 PORT1 1 2 TNB
JCI S2 28-JUN-10 PORT2 5 2 TNB
JCI S3 28-JUN-10 PORT1 6 8 TNB
JCI S5 28-JUN-10 PORT2 2 4 TNB

------->s5 only in the current month-it was not in the previous month data collection
-----------------------------------------------------------------------------
JCI S1 28-MAY-10 PORT1 10 3 TNB
JCI S1 28-MAY-10 PORT3 5 3 TNB
JCI S2 28-MAY-10 PORT1 4 1 TNB
JCI S2 28-MAY-10 PORT2 2 1 TNB
JCI S2 28-MAY-10 PORT3 8 1 TNB
JCI S3 28-MAY-10 PORT2 7 7 TNB
JCI S3 28-MAY-10 PORT3 5 7 TNB
JCI S4 28-MAY-10 PORT1 2 10 TNB
------->s4 in the previous month data

15 rows selected.

1 SELECT A.CUSTO,A.SWITCH,
2 A.PORT1-B.PORT1,
3 A.PORT2-B.PORT2,
4 A.PORT3-B.PORT3
5 FROM (SELECT
6 A.CUSTO

[Code] ......

CUSTO SWITCH A.PORT1-B.PORT1 A.PORT2-B.PORT2 A.PORT3-B.PORT3
------- ------- --------------- --------------- ---------------
JCI S1 0 4 3
JCI S2 -3 3 -8
JCI S3 6 -7 -5

How to include the Switch S4 and S5 in the output. how implemented FULL OUTER JOIN

View 13 Replies View Related

PL/SQL :: Query To Total Attendance By Months

Jul 21, 2012

I need the following totals for each student based on the schools that they are enrolled for each month July through June.

Total Days Suspended from each school

Attendance Table Columns - Absent Date, Absent Code, Pupil_Number, School (Absent Codes for suspended = 3)

Total Days Present at each school - Days Enrolled at each school minus Days Suspended minus Absences

Totals Takes the first day that a student is enrolled (counts the days that the school is in session and the student is not marked absent) Student must be enrolled during the time frame for that month.

Total Days Absent - Total days absent minus suspensions

Examples
Student enrolls in school 11111 on 7/15/2011
Student withdraws from School 11111 on 11/15/2011
Student enrolls in school 22222 on 11/20/2011

Total Days Present should be totaled for each student for each month that they are in a school.

I need 1 row of data for the above student for each month that contains total days suspended, total days present and total days absent.
Pupil Number School Month Totals Days Suspended Total Days Absent Total Days Present

1234 11111 07 0 0 0
1234 11111 08 0 0 4 (school days 4)
1234 11111 09 3 1 16 (schools days 20 - 3-1)
1234 11111 10 0 1 18 (school days 19 - 1)
1234 11111 11 1 1 7 (student enrolled at school A 9 days -1-1)
1234 22222 11 0 1 10 (student enrolled at school B 11 days -1)
1234 22222 12 1 2 17 (school B 20-1-2)
1234 22222 01 0 0 20

View 2 Replies View Related

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

Write Simple Sql Which Would Delete Data From Last N Months?

Apr 20, 2009

am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate

e.g

Jan 1 - 30 deletes 2 - 30 keeps data for 1st
Feb 1 - 28 deletes 2 - 28 keeps data for 1st
Mar
Apr Current sysdate
May

View 3 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 :: List Persons Having Event In Consecutive Months?

Apr 7, 2011

how to query a list to see if a person had events in consectutive months within the past year. We call a person a LongTermResident if they had a review in any two consectutive months within a reporting period. I wrote a function isResidentLongTerm, passing in FacilityID, ResidentID, ReportPeriodStart, and ReportPeriodStop and returning a 'Y' or 'N'. It works, but the performance is slow.

So if I have a list of reviewers, facilities, reviewees I want to select only those SNF/NF residents who have had routine reviews in any two consectutive months at the same facility.

This is my query:

select ConsultantID, ResidentID
from (
select distinct ConsultantID, FacilityID, ResidentID
from Reviews
where BedType = 17820 -- SNF/NF bed
and ReviewType = 17474 -- routine review
)
where isResidentLongTerm( FacilityID, ResidentID, :startDate, :stopDate ) = 'Y'

View 1 Replies View Related

PL/SQL :: Yearly Quantity Distributed Evenly Across 12 Months

Aug 15, 2012

getting the yearly qty distributed evenly across 12 months. I can do this in PL/SQL but want to know is it possible in SQL.

Database version 10g

create table test_tbl( qty number(22),
yr number,
key_val varchar(10)
);

insert into test_tbl values( 500000, 2013, '1');
insert into test_tbl values (56789, 2014, '2');

commit;

This is the output I need to get from a SQL select query

Key     Month     Year     qty
--------------------------------------------------------------
1     1     2013     41667
1     2     2013     41667
1     3     2013     41667
1     4     2013     41667
1     5     2013     41667
1     6     2013     41667
1     7     2013     41667
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Create Select Query That Will Get Data From 18 Months Ago Based On Sysdate?

Sep 9, 2013

I have a table with columns job_id, jan, feb, mar ... , and year

I need to create a select query that will get the data from 18 months ago based on sysdate.

So something like:

Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');

However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.

View 23 Replies View Related

Find Queries Bases On Elapsed Time From Window Of 3 Months

Nov 19, 2012

how to find out queries bases on elapsed time from the window of 3 Months

DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT

View 6 Replies View Related







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