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.
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.
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
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.
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
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?
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,
,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?
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
[/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 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.
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
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.
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?
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
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)
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
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')
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
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'
