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 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 want to use SQLLDR (sql loader) in my forms9i form to load attendance data therefore I installed SQLLDR utility in user computer but it is not working,
Begin :message := 'Please Wait, Data LOADING...!'; Delete From atnd_load; Forms_ddl('Commit'); utlt:= 'c:oracleora90insqlldr'; usr:= 'vikorapp'; pass := 'vikorapp'; strng := 'laptop'; cntrl := 'C:VikorPayrollFormsAtndCtl.ctl'; lg := 'c: empAtndLog.log'; bad := 'c: empAtndBad.bad'; host(cmd); :message:= 'Data Loading Complete...!'; End;
I need to modify my query so that it can give me a total(duration) and total(stlmntcharge) per day in april 2013 starting from the 1st till the 30th. At the moment my query looks like below:
SELECT sum(duration),sum(stlmntcharge) FROM voipcdr WHERE (calldate >= TO_DATE('20130401','YYYYMMDD') AND calldate <= TO_DATE('20130430','YYMMDD')) AND (remtrunkid IN (SELECT UNIQUE trunkid FROM trunks WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%'))
or remip in(SELECT UNIQUE startip FROM gateways WHERE rangename LIKE 'vo-za%' OR rangename LIKE 'PC-IS-VOIS%')
I want to get any employee name of deptno 10 but total count of number of employees under dept 10.
DECLARE l_deptno NUMBER:=10; l_count NUMBER; l_ename varchar2(20); BEGIN SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno; dbms_output.put_line(l_count||' '||l_ename); end;
There is a attendance table having structure(empid number,signtime datetime)It has data of attendance of employees:
What is the right sql to show employees detail attendance according to the no of days attendance . i.e.(According to the maximum no of attendance first and so on)
suppose: 3 employees abc,bbc,cca abc has 20 days of attendance ,bbc has 21 days,cca has 19 days..The report like 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 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.
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
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 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.
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')
