select to_char(order_date,'mon-yyyy') "months", sum(nob) "number of bags"
from p_in where order_date between '1-apr-11 and '31-mar-12'
group by to_char(order_date,'mon-yyyy')
the above query returns me a resultset which gives me number of bags sold in every month, but it does not sort the month in their chronological order. i want to sort my resultset in ascending order of month s.,means jan-feb-march-apr-may ams so on...QUOTE
I am trying to count the number of IDs dropped and enrolled in each unit for each of the 4 terms between their perspective dates. There are 4 Terms and an ID can participate in a unit in any of these terms:
I am trying to count how many IDs enrolled in a unit between those dates and how many doped before the end dateThe ENROL_DATE for each ID in a unit has to be between the given Term Dates to count. Unit KIS has 1 ENROL and one DROP in TERM 1 UNIT POL occurs in TERM 2 and 4 and both DROPUNIT LIN and 1 ENROL and 1 DROP in 2 different TERMS My problem is how do i specify count ENROL and count drop between the Term dates and then group by TERM and UNIT.
I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.
I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.
Here is an example of dates:
Visit Date Actual Date ========================== 01-APR-09 19-MAR-10 16-NOV-09 19-MAR-10 17-MAR-10 19-MAR-10 21-MAR-10 19-MAR-10 04-APR-11 19-MAR-10 15-JUN-11 19-MAR-10 19-SEP-11 19-MAR-10 24-FEB-12 19-MAR-10
The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.
I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.if I do a TO_char (visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.Is there a way to keep as a date in my sub query and then convert to a string?
it looks a little like this, with some other fields that I have to max, sum ...
Then I use that in a subquery where I use just the month and year TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR right now if I do a group by visitdate on the subquery it returns
provider visitdate 2 12/20/2012 3 12/21/2012
even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM') it is still returning :
Why is it necessary to precede group by before order by and not vice versa in a sql query? as the foll code gives an error select name from student order by age group by name;
I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as
select asigno,count(*) from angdata77 group by asigno order by asigno;
Is there any other query for retrieving the data from angdata77
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel ( start_date, end_date ) AS SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE A | 2010 11 01 | 2011 02 11 B | 2011 01 20 | 2011 02 15 C | 2011 03 13 | 2011 04 10 D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]------------------------- B : ------[---]------------------------ C : --------------[---]---------------- D : ----------------[------]-----------
Travel Date : -[--------------------------------]--
BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]-----------
Result X1 : -[-]------------------------------- Result X2 : -----------[--]-------------------- Result X3 : -----------------------------[--]--
Name Null Type --------------------------- -------- ------------- RPTNO NOT NULL NUMBER RPTDATE NOT NULL DATE RPTD_BY NOT NULL VARCHAR2(25) PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type ------------- -------- ------------- RPTNO NOT NULL NUMBER LINENO NOT NULL NUMBER COMMENTS VARCHAR2(240) UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE FROM RPTBODY t0 , RPTHEAD rpthead WHERE ( t0.RPTNO = rpthead.RPTNO AND t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss') AND rpthead.PRODUCT_ID IN ('4647') )
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003 12/3/2003 12/4/2003 12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select c.Event, c.code, count(1) as calls, total.total_count, count(1) / total.total_count * 100 as pct_of_total from table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
I am newbie to oracle and using oracle 10g as database. I want to get dates between two dates .... let me give an example suppose a user enters 1-Aug-2010 - 31-Aug-2010 , so i should get all dates in between from date and to date.
something like "select date..or whatever from dual where date between 1-Aug-2010 and 31-Aug-2010 " like this type or other.
[/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 not defined the table ( I only have privileges to query data).
I am unable to copy and paste my real code here, and the actual results from the run, as my company will fire me if I do so... so here is how things approximately look like (tried to keep it as real as possible).
Let's say that the table CYCLE has client numbers (clientid), cycle number (cycleno), date of visit (visdt).
I am trying to create a query to calculate how many days there are between each two consecutive visits/cycles for a single client(let's say 1200004)
create table RM_TR_INVESTMENT ( AS_ON_DT DATE not null, EXP_ID NUMBER(10) not null, BO_REF_ID VARCHAR2(30), FO_REF_ID VARCHAR2(30), BK_PRODUCT_ID VARCHAR2(20), BK_INV_TYPE_ID VARCHAR2(20), [code]....
I want to find the sum of NP_AMT_CCY where difference between Start_Dt and Maturity_Dt >= 14 days and <=28 days.How can I build the query for the above statement ?
I'm trying to use a PIVOT on the following data set:
ID STATUS_DESC PAY_STATUS PAID_DATE TRANSACTION_TYPE TRANSACTION_DESC DEBIT TOTAL -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 Card Payment Payment 2 349 349 9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 Card Payment Payment 1 100 100
However I'm still getting two rows as per the below. Ideally all data should be on a single row.
ID STATUS_DESC PAY_STATUS PAYMENT_1_DATE PAYMENT_1_AMT PAYMENT_2_DATE PAYMENT_2_AMT TOTAL -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 100 100 9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 349 349
I have constructed my pivot using the following on the outer select:
PIVOT (MAX (insert_timestamp) AS paid_date ,SUM (debit) AS amt
FOR transaction_desc IN ('Payment 1' AS payment_1, 'Payment 2' AS payment_2)) ;
I've used MAX to pivot the date and also tried using NVL on the insert_timestamp but still no luck.
I'm trying to run a report that has a moving date and I need to find data that's within 12 months of that certain date.
So for example... customers come in everyday all year long. I wanted to find the number of unique customers in a year. But the year is moving... So 1 year from 1/15/2011 is 1/14/2012. And 1 year from 1/16/2011 is 1/15/12. So I had something like this but doesn't quite work..
SELECT ...
NVL(COUNT(DISTINCT CASE WHEN TX.DATE_OF_FIRST_VISIT BETWEEN TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY') AND ADD_MONTHS(TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY'),12) THEN (TX.CLINIC_ID||TX.PATIENT_UNIQUE_ID)END),0) AS "YEAR_1"
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE; CREATE TABLE BookingDM ( hNo NUMBER(3), gNo NUMBER(5), dFrom DATE NOT NULL, [code]......
This is the first set I'm attempting to insert hNo = 148 gNo = 11169 dFrom = 09/03/2009 dTo = 09/10/2009 rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202'); INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202') * ERROR at line 1: ORA-01843: not a valid month
What I need to do is take 2 dates from a table named 'projects' and insert the number of days between them into a table named 'time_record'.How do I go about this?
