SQL & PL/SQL :: First Business Day Of A Given Month?
Dec 6, 2010
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.
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.
We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc
format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate vcActivityName = Process Request usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity) Working Days: Monday through Saturday Hours of Operation: 9AM 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes. format of date time: 2010-09-17 14:50:51.150 vcActivityName = Process Request Don't worry about process request..
I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00 (and this is my problem, how to add this condition in this function)
I have been asked to see why a sister company cannot access multiple business areas through a single responsibility. At present, if they log in through a GL responsibility they can see all GL folders, items and the returned data. If they then go to the AP business area, they can see the folders and items, but when they run a report they get an error message 'Query returned no data' - but if they run the same query using an AP responsibility they get the correct results. I have looked at their user set up, and it seems fine i.e. has access to all business areas and required priveledges.
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).
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
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've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:
SELECT DISTINCT CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date) WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date) WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date) WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date) [code]....
The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause
ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));
But I got an ORA-01866: the datetime class is invalid. I'm using Oracle 10g xe (outdated, I know). get the months to show up in order?
How I can see the order by employees,year wise and monthwise (month should start from january and so on..)
SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') YEAR,TO_CHAR(HIREDATE,'Month') MONTH ,count(*) total FROM EMP GROUP BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');
SELECT Sum("F0902"."GBAPYC"/100) AS "initialBalance", Sum("F0902"."GBAN01"/100) AS "January", Sum("F0902"."GBAN02"/100) AS "February", Sum("F0902"."GBAN03"/100) AS "March", [code]....
I have the following table. In this table i'm trying to get the count of records by month when its value (SIGNAGE_ONE_LANE_ROAD_AHEAD) is 'Yes' and its giving me the wrong count.
I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).
I've already achieve getting data from the previous month by using this formula:
My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?
