Query to get results between month and year 1

[ClowneUser]

Hi,

I'm trying to create a query to get a list of part numbers that were added to the system in a particular period ( month )
Below is some example data from the table

part_no    stat_period_no     stat_year_no
A1234      1                  2007
A5678      3                  2007
A9999      3                  2007
A9876      7                  2007
A5432      1                  2008

Stat_period_no is the month ( 1 to 12 )

An example of what i need is say to get all results between period 3 year 2007 and period 1 year 2008, that query should give me all results except the top row.

I hope that makes sense.

Does anyone know how i can achieve this?

Thanks,

Mick.

 

[taupirho]

You don't say what data type your start and end periods are. Assuming they were numeric, something like this should would work.

select * from
(
select part_no,
to_char(to_date('01-mar-2007'),'yyyyFMmm') start_date,
to_char(to_date('01-jan-2008'),'yyyyFMmm') end_date,
to_char(stat_year_no || stat_period_no) period
from mytable
)
where period between start_date and end_date
/



In order to understand recursion, you must first understand recursion.

 

[Dagon]

Or possibly a bit simpler:

select * from parts
where stat_year_no||rpad(stat_period_no,2) 
between '200703' AND '200801'

 

[IanWaterman]

Select * from table
where
to_char(Year, '9999')||to_char(period, '99') >= '200703' and
to_char(Year, '9999')||to_char(period, '99') <= '200801'

Ian

 

[ClowneUser]

Hi,

Thanks for your replies, none of them bring back any data, I don't get any errors, the query runs, just no results, could it be because numbers 1 - 9 are single numeric values ( ie they don't have a leading 0 ) while 10 - 12 are double?, and if so how can i get round this?

Thanks in advance,

Mick.

 

[taupirho]

Only difference between me and you is I'm running Oracle 9, so my solution should have worked although I know there are "issues" with Dagon's and Ian's. Please check your data and/or query


1 create table mytable(part_no varchar(5),
stat_period_no number,
2* stat_year_no number)
SQL> /

Table created.

SQL> insert into mytable(part_no,stat_period_no,stat_year_no)
2 values('A1234',1,2007);

1 row created.

1 insert into mytable(part_no,stat_period_no,stat_year_no)
2* values('A5678',3,2007)
SQL> /

1 row created.

1 insert into mytable(part_no,stat_period_no,stat_year_no)
2* values('A9999',3,2007)
SQL> /


1 row created.

1 insert into mytable(part_no,stat_period_no,stat_year_no)
2* values('A9876',7,2007)
SQL> /

1 row created.


1 insert into mytable(part_no,stat_period_no,stat_year_no)
2* values('A5432',1,2008)
SQL> /

1 row created.


SQL> select * from mytable;

PART_ STAT_PERIOD_NO STAT_YEAR_NO
----- -------------- ------------
A1234 1 2007
A5678 3 2007
A9999 3 2007
A9876 7 2007
A5432 1 2008




1 select * from
2 (
3 select part_no,
4 to_char(to_date('01-mar-2007'),'yyyyFMmm') start_date,
5 to_char(to_date('01-jan-2008'),'yyyyFMmm') end_date,
6 to_char(stat_year_no || stat_period_no) period
7 from mytable
8 )
9* where period between start_date and end_date
SQL> /

PART_ START END_D
----- ----- -----
PERIOD
--------------------------------------------------------------------------------
A5678 20073 20081
20073

A9999 20073 20081
20073

A9876 20073 20081
20077


PART_ START END_D
----- ----- -----
PERIOD
--------------------------------------------------------------------------------
A5432 20073 20081
20081


SQL> col period format a10
SQL> /

PART_ START END_D PERIOD
----- ----- ----- ----------
A5678 20073 20081 20073
A9999 20073 20081 20073
A9876 20073 20081 20077
A5432 20073 20081 20081





In order to understand recursion, you must first understand recursion.

 

[Dagon]

Sorry, mine should have been:

select * from parts
where stat_year_no||lpad(stat_period_no,2, '0')
between '200703' AND '200801'

 

[taupirho]

Yep, Dagon's solution looks good now.
Short and sweet, unlike my War and Peace. Way to go.



In order to understand recursion, you must first understand recursion.

 

[ClowneUser]

Hi taupirho,

I tried your query again, and it did bring back some data which looked good, except I queried between period 7 2007 and period 3 2008, but it missed periods 10, 11 and 12 of 2007 and also shows period 10 from 2008.
Any idea why?

Dagon,

I tried your ammended query but still no data is returned.

Thanks for your help so far.

Mick.

 

[taupirho]

Please post some insert statement to allow us to create a more representative sample of your data. Just the important data. Leave out extraneous stuff.




In order to understand recursion, you must first understand recursion.

 

[IanWaterman]

Dagon's solution should work, if not try explicitly to_char the first part

select * from parts
where to_char(stat_year_no, '9999')||lpad(stat_period_no,2, '0')
between '200703' AND '200801'

Also did you notice he changed rpad to lpad

Ian

 

[ClowneUser]

Yes Thanks Ian, Just retried it and it works a treat.

Thanks to everyone for your help.

Mick.