SQL & PL/SQL :: Year And Month Query

Mar 20, 2011

I have two Queries.

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

MONTH
***
01
02
03
04
05
06
07
08
09
10
11
12

I want to combine these two. I need the output as

2007-01
2007-02
2007-03
....
...

View 8 Replies


ADVERTISEMENT

PL/SQL :: Query To Be Displayed Grouped By Year / Month And Then By Assignment?

May 27, 2013

I have the following table structure with values

CREATE TABLE DUMMY
  (
    SR_NUMBER          VARCHAR2(100 CHAR),
    ASSIGNMENT_GROUP   VARCHAR2(100 CHAR),
    REASSIGNMENT_COUNT VARCHAR2(100 CHAR),
    CREATED DATE,

[code]...

?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.

View 10 Replies View Related

SQL & PL/SQL :: Max And Min Values Of Month And Year

Jun 25, 2010

can we take the maximum and minimum value of month and year

View 7 Replies View Related

Interval Year To Month Parameter

Mar 20, 2008

I have a procedure that has a 'INTERVAL YEAR TO MONTH' parameter. What value do I pass to this parameter?

View 1 Replies View Related

SQL & PL/SQL :: Compare The Sales With The Same Month Last Year

Jul 6, 2012

I wanted to compare the sales with the same month last year. But the issue is only one value on each customers.

File Attached Table as JPG file..
-----------------------------------------------------------------------
SELECT WORDERS.BP_ORDER,
(SELECT SUM (WINVITEMS.ITEM_VAL)
FROM WINVITEMS
INNER JOIN WORDERS ON WORDERS.ORD_NO = WINVITEMS.ORD_NO
WHERE WORDERS.DATE_ORDER >= TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: How To Sort Date By Month (JAN) And Year (2009)

Jun 4, 2010

I've done this once before, but can't seem to find the sql.

How can I sort by month and year on a column called ex: TEST_dATE

JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010

View 15 Replies View Related

SQL & PL/SQL :: Create Procedure Have Month And Year As Argument

Aug 5, 2010

Need to create a procedure that will retrun me monthly count of records

create table sample ( S_name varchar2(20), S_Date Date);

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]....

View 5 Replies View Related

SQL & PL/SQL :: Find Out Date Difference In Day-month-year?

Jul 8, 2010

i want to find out difference between two dates in day-month-year. How can i do it?

For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'

View 4 Replies View Related

PL/SQL :: Insert Only Month And Year Instead Of Full Date

Oct 8, 2012

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..

View 16 Replies View Related

PL/SQL :: How To Pass Year And Month As Parameter Into A Block

Apr 26, 2013

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

[Code]....

View 11 Replies View Related

SQL & PL/SQL :: Calculations - Get Same Month From Previous Year Of Selected Date

Sep 20, 2011

i have a requirement to create a query which gets the same month from the previous year of the selected date .

Example : i select SEP-2011 ( as sysdate )

it will return the SEP-2010 as well .

here is the code which works fine with sysdate, dual :

SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;

in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)

am unable to apply the above code on it , i guess its the data type pf the field .

View 5 Replies View Related

SQL & PL/SQL :: Checking If Month And Year Is Greater Or Equal To Current One?

Mar 7, 2013

This is the description for the procedure:

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.

View 3 Replies View Related

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

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.

View 3 Replies View Related

SQL & PL/SQL :: How To Sort Query Based On Financial Year

Jun 12, 2012

Haw to sort the data based on financial year.

For example Indain Fiscal year 01-Apr-2010 to 31-Mar-2011

Apr2010 -1
May2010 -2
.
.
.
.
Feb2011-11
Mar2011-12

I had given a query..for Quarter idendification

SELECT distinct
'Qtr'
|| CASE
WHEN PERIOD BETWEEN TO_DATE ('01-04-2010', 'DD-MM-YYYY')
AND ADD_MONTHS (TO_DATE ('01-04-2010', 'DD-MM-YYYY')-1, 3)

[code].....

View 2 Replies View Related

SQL & PL/SQL :: Getting Query For Current Year Data Up To Sysdate?

Jun 19, 2012

I have a table for exampl,

emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012

i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.

my output like this.....

emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)

View 2 Replies View Related

SQL & PL/SQL :: Date Query - Input Year In Specified Range

Dec 26, 2012

Getting problem with below query.

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error starting at line 1 in command:

select to_date('30-DEC-00','DD-MON-YYYY') from dual

Error report:

SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*Cause: Illegal year entered
*Action: Input year in the specified range

View 1 Replies View Related

Query For Getting Data For Every Quarter For Financial Year

Nov 11, 2013

My problem is I need to get the data for every quarter for financial year and also I need data for every week for financial year.For example for financial year 2012-13, Apr2012 to Jun2012 would be Q1, Jul2012 to Sep2012 would be Q2 and so on. Total 8quarters should come upto Apr2013.In the same way  1st apr 2012 to 7th apr 2012 would be week1, 8th apr to 15th apr would be week2 and son on. How to write a query for this scenario in oracle.

View 4 Replies View Related

PL/SQL :: Query By Current Month

Jul 12, 2013

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".

View 6 Replies View Related

Specify Month Start And End Date In Query?

Oct 27, 2008

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.

View 1 Replies View Related

SQL & PL/SQL :: Query To Find Last 6 Month Records?

Aug 23, 2010

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

how to use in where condition

View 13 Replies View Related

Group By Month - Query A Transaction Table?

Oct 19, 2010

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.

View 2 Replies View Related

PL/SQL :: Query To Find Number Of Days Across Each Month

Oct 1, 2012

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.

View 10 Replies View Related

SQL & PL/SQL :: Create A Query Which Will Show Sales Of Products By Month

Jul 12, 2004

I'm trying to create a query which will show sales of products by month, something like this: -

JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015

The data is held on 3 tables: -

TB_ARTICLES
ID
CODE
DESCRIPTION

TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE

[code]....

Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.

View 12 Replies View Related

SQL & PL/SQL :: Query To Find Missing Dates Between Two Columns In Given Month?

Sep 26, 2012

I need query to find the missing dates between two columns in given month.

CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);

[code].....

Required output is

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

View 8 Replies View Related

SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table

Jun 4, 2013

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.

1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);

2. List table content:
select * from T1;

account_no area_code bill_month consumption

Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......

3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12

Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182

With the conventional query I hope this is impossible,

View 2 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

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).

View 5 Replies View Related

SQL & PL/SQL :: For Each Month Sum Values From Month Before?

Nov 25, 2010

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);

Result

ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250

View 17 Replies View Related

SQL & PL/SQL :: Same Day At Last Year

Apr 20, 2011

my propble is supose if today is Tuesday 01�32011 which is week 16 of this year i need to get same day tuesday on week 16 of last year

View 12 Replies View Related

To_date - Using Just Dd / Mm And Not Year

Jun 18, 2008

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;

View 3 Replies View Related

SQL & PL/SQL :: Date Without Year

Sep 27, 2010

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),

but I don't know how..

View 39 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved