I have an excel spreadsheet that queries an oracle data for some information. I am trying to get the information shown to only be from the current month. I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out. Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
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).
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.
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
I want a monthly report where the month wise sum of qty should be displayed in a row for last 12 months. I need to specify the month start date and end date in the query to pick the sum for the particular month. How can i do it in a SQL query?
SUM(decode(to_char(t1.trx_date,'mm/rrrr'), to_char(add_months(SYSDATE, -1),'mm/rrrr'), nvl(t2.quantity_invoiced, 0))) AS qty_01 from t1,t2 where t1.col1=t2.col1
instead of sysdate i have to use start and end of month. Also i am using group by clause on some columns.
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST FROM EMRASTHAMAHISTORYDETAILS WHERE ------ GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:
CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/) WHERE DIRECTORY_NAME in ( select DIRECTORY_NAME from DBA_DIRECTORIES WHERE DIRECTORY_PATH like '/xyz/%' )
i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.
We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.
We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below
Number of Working days in March is 31 - 3 + 1 = 29
Allowance A1 = (300 * 29 )/31
Number of Working days in April is 5 ( this also needs to find logical I am guess ) Allowance A2 = (300 * 5 )/30
Then A1 + A2.
The A(n) would be the total allowance where provided the number of month across.
MISSING_DATES EMPNO ---------------------- ---------- 09-SEP-12 TO 11-SEP-12 7499 23-SEP-12 TO 26-SEP-12 7499 01-sep-12 TO 30-SEP-12 7521 01-sep-12 TO 30-SEP-12 7788
?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 am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.
SELECT curr_balance FROM transaction_details WHERE acct_num = '10'
[Code]...
This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?
I have a form with multiple tabs, each tab showing one data block. Many of the blocks are standard in enter-query mode. I want to allow the users to navigate through the tabs while the blocks are in enter-query mode.how can i do this?
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 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);
A) extract subscriber numbers from TCME153.SUB then B) bounce those subscriber numbers off of a disconnect date i.e. DISCO_DTE_SBB then C) bounce A) and B) off of an item table i.e. ITV_ITEM_VALUE
My hope is that the first query works as intended. Second and third queries throw error 'ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH'.
1) SELECT DISTINCT SUB_ACCT_NO_ITV FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB WHERE EXISTS (SELECT SBB_BASE.DISCO_DTE_SBB FROM (SELECT SUB FROM TCME153.SUB));
2) SELECT DISTINCT SUB_ACCT_NO_ITV FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
I am trying to query on the dates in the table that are the last day of each month. The last day of the month may not be in the table. Example 07/30/2010 is the last day in the table for July 2010, but 07/31/2010 is the last day of the month. My query should give me 07/30/2010 data since it is the last day of the month in the table.
I just need a where clause that will only give me the data for the last day of each in the table.
I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.
For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.
e.g. a range could be from today:Sept,20 to Apr,18.
so what i was trying is to select doing the following.
select TABLE_X_ID, TABLE_X_BIRTH_DATE from TABLE_X where to_date(TABLE_X_BIRTH_DATE, 'DD/MM') between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM') and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')
first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.
I need to calculate first business day of a given month . Below is complete explanation
Business day=sould not include weekends and holidays.
In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.
So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.
I'll be passing the month begin date as parameter..so the function should return the first business day for that month.
