Query 1: SELECT DISTINCT YR FROM( SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'YYYY'), 1*LEVEL -1) , 'YYYY') YR FROM Dual CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1 ORDER BY YR )
Gives the Output as YR **** 2007 2008 2009 2010 2011
Query 2 ******* SELECT DISTINCT MONTH FROM( SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'MM'), 1*LEVEL -1) , 'MM') MONTH FROM Dual CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1 ORDER BY MONTH ) Gives the Output as
insert into sample values('1','01-JAN-2005'); insert into sample values('1','1-JAN-2006'); insert into sample values('2','2-JAN-2007'); insert into sample values('3','4-JAN-2005'); insert into sample values('4','11-JAN-2004'); insert into sample values('3','2-JAN-2005'); insert into sample values('2','12-JAN-2006'); insert into sample values('1','21-JAN-2005'); insert into sample values('2','11-JAN-2005'); insert into sample values('7','01-JAN-2005'); [code]....
select b.penjara_id, p.penj_lokasi, a.no_daftar, b.episod, b.nama1,to_char(a.trkh_mula_prl,'dd/mm/yyyy') as trkh_mula_prl, to_char(bulan_proses,'mm/yyyy') as bulan_proses, b.epd, b.lpd from prl_daftar_proses a, senarai_pesalah b, penjara p
where a.no_daftar=b.no_daftar and a.episod=b.episod and b.penjara_id = p.penjara_id and a.setuju_jplp is null and a.bulan_proses between to_date(:FROM,'dd/mm/yyyy') and to_date(:TO,'dd/mm/yyyy') order by b.penjara_id, a.bulan_proses,a.no_daftarHi,
how i can insert only month and year from the value that have full date in the database?
for example,the date is 09/18/2012, but i just want to insert 09/2012 as parameter. If i want to insert only one parameter, i can do that..But i have problem when I want to insert two parameters..
I am populating a time dimension table. One job is to assign business day with sequence number on monthly basis and by year. Business day does not include weekends and all federal holidaies.
I created a PL/SQL block to handle this job. It works. However, I haven't figure out how to pass the month number and year number into PL/SQL block automatically. Now I have to manually enter year and month number (on Toad) to pass to block to make it work. If I create it as stored procedure and write another block to call it, I can pass year and month number into parameter there like this:
exec my_sp_name ('1', '2013');
But all of these are not good enough. I want to use code to automatically pass yesr and month number into PL/SQL block. So that I can make it better. . here is the PL/SQL block I created.
declare i number := 1; begin for r in (select * from time_dim where calendar_month_number = &month and calendar_year= &year and business_day_flag = 'B' order by date_sk_id) loop
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace PROCEDURE ADD_REWARD_sp( p_proj_id IN NUMBER, p_pledgeAmt IN NUMBER, p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
?I have the following requirement, the output should be:
Ticket count (sr_number) % of tickets inside DL Number of tickets inside DL Average cycle time (cycle time = closed date - created date) Total cycle time (cycle time = closed date - created date) Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate OrderId Subtotal CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL") AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
AREA (area_code, area_name) MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)
I want to display the output of top 3 sales columnwise as below.
AREA_NAME Top 1st (ISBN) Top 1st (BOOK_NAME) Top 1st (QTY) Top 2nd (ISBN) Top 2nd (BOOK_NAME) Top 2nd (QTY) Top 3rd (ISBN) Top 3rd (BOOK_NAME) Top 3rd (QTY)
Thing is how can I join those tables to get all the sales ID's that have at least two products one of which has got any number of messages and the other one hasn't got ANY any messages. So another words one sale entity that has both products with messages and at lease one with no message. Something like this
Sales 1 ID 123 Product1 ID 1234 for sale ID 123 Product2 ID 4321 for sale ID 123 Sales_message ID 098 for product ID 1234 'blabla' Sales_message ID 876 for product ID 1234 'albalb' AND NO MESSAGES FOR PRODUCT 4321
Required to generate report to total sale dollar amount by combining information from T1 and T2 (matching with item_no). Rules are a)Formula is Sales_Number*Sale_price b)Choose the earliest sale record to latest by Sales_Date with the sum of Sales_Number is less or equal the Total_Sales_Number from T1. c)If the sum of Sales_Number is less than the Total_Sales_Number from T1, then the use a fixed price for all items, say $10 d)If a item is not in T2 then use $10 for the price
For example, item_no 1: Total sale dollar amount = 20*$15+30*$15+(60-20-30)*$10
item_no 2: Total sale dollar amount = 40*$16+(50-40)*$18 item_no 3: Total sale dollar amount = 5*$20 item_no 4: Total sale dollar amount = 20*$10
The current logic is done by using cursor. Can this be done without cursor using such as analytical statements? The Oracle DB is 9i.
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 24 2013 March loc1 55007 78 57 28 2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 2013 March loc1 55007 78 57 2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL FROM ( SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
i am sure it may be wrong because it doesn't mention year however i have been told to get a list of all students who enrolled in MAY. and i have tried doing
select * from table where enrolDate > to_date('01-05','DD-MM') and enrolDate < to_date('31-05','DD-MM') and it doesnt work (says no rows selected when in fact there are some rows!
i can do it fine when using YEAR ie '01-05-2000','DD-MM-YYYY' another problem.
I am trying to do this:
Select to_char(to_date(01012008,DD-MM- YYYY),DAY) from dual;
and it is giving me the error however if i do this:
Select to_char(to_date(16012008,DD-MM-YYYY),DAY) from dual;
I got a table with a date-typed column called: "Birth_date", and I wanna write a function that retrieves all the records that "got a birthday" this week.
in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),
In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).
example ---------- jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5 feb ------------do--------------------------------- like this for all 12 months at a single query.
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
