The revolutionary project management tool is here! Plan visually with a single glance and make sure your projects get done.

Hi,

How can i do this we can use dummy table so basic idea is divide current_year -1998 and distribute years among five segments ( seq_1 to seq_5).

Result should be:

Sequence count for (current-1998/3)

seq_1 1998-2000

seq_2 2001-2003

seq_3 2004-2006

seq_4 2007-2009

seq_5 2010-2013

How can i do this we can use dummy table so basic idea is divide current_year -1998 and distribute years among five segments ( seq_1 to seq_5).

Result should be:

Sequence count for (current-1998/3)

seq_1 1998-2000

seq_2 2001-2003

seq_3 2004-2006

seq_4 2007-2009

seq_5 2010-2013

(select case when extract(year from YOUR_DATE) in (1998,1999,2000) then 'seq_1'

when extract(year from YOUR_DATE) in (2001,2002,2003) then 'seq_2'

when extract(year from YOUR_DATE) in (2004, 2005,2006) then 'seq_3'

when extract(year from YOUR_DATE) in (2007,2008,2009) then 'seq_4'

when extract(year from YOUR_DATE) in (2010,2011,2012,2013) then 'seq_5'

end sequence

from YOUR_TABLE)

group by sequence

can we do this without hard coding the years?

Can we use any system table as there is no table for this

seq_1 1998-2000

seq_2 2001-2003

seq_3 2004-2006

seq_4 2007-2009

seq_5 2010-2014

(select case when extract(year from YOUR_DATE) in (1998,1999,2000) then 'seq_1'

when extract(year from YOUR_DATE) in (2001,2002,2003) then 'seq_2'

when extract(year from YOUR_DATE) in (2004, 2005,2006) then 'seq_3'

when extract(year from YOUR_DATE) in (2007,2008,2009) then 'seq_4'

else 'seq_5'

end sequence

from YOUR_TABLE)

group by sequence

do

floor((year - 1998)/3)+1

but then you'd have 6 sequences, not 5

1998-2009 will be spread between the first 4 sequences, everything else goes into sequence 5

I have no idea what you mean by "disturbed even or odd"

mod(year-1998,5)+1

1998 - 1

1999 - 2

2000 -3

2001 - 4

2002 - 5

2003 - 1

2004 - 2

2005 - 3

2006 - 4

2007- 5

2008 - 1

2009 - 2

2010 - 3

2011 - 4

2012 - 5

2013 - 1

2014 - 2

etc

something like below

select sequence,count(*) from

(select case when extract(year from sysdate) in (1998,1999,2000) then 'seq_1'

when extract(year from sysdate) in (2001,2002,2003) then 'seq_2'

when extract(year from sysdate) in (2004.2005,2006) then 'seq_3'

when extract(year from sysdate) in (2007,2008,2009) then 'seq_4'

else 'seq_5'

end sequence

from sysdate)

group by sequence

if you want to use something from the data dictionary that will only give you counts if your dictionary has data going back to 1998

if you just want to see some sample data then create some....

WITH your_table

AS (SELECT DATE '1998-01-01' + LEVEL * 123 your_date

FROM DUAL

CONNECT BY LEVEL < 100)

SELECT sequence, COUNT(*)

FROM (SELECT CASE

WHEN EXTRACT(YEAR FROM your_date) IN (1998, 1999, 2000) THEN 'seq_1'

WHEN EXTRACT(YEAR FROM your_date) IN (2001, 2002, 2003) THEN 'seq_2'

WHEN EXTRACT(YEAR FROM your_date) IN (2004, 2005, 2006) THEN 'seq_3'

WHEN EXTRACT(YEAR FROM your_date) IN (2007, 2008, 2009) THEN 'seq_4'

ELSE 'seq_5'

END

sequence

FROM your_table)

GROUP BY sequence

ORDER BY sequence

And no, you certainly cannot use "sysdate" to help you with this. That is a function, not a table, and it always returns the current date/time value (which always includes the current year).

All Courses

From novice to tech pro — start learning today.

Open in new window