Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

number of distinct years query - need assistance 1

Status
Not open for further replies.
Maven4Champ

Maven4Champ

Technical User
Jun 16, 2004
154
Hi everyone,

I have a query that has been bothering me the past 24 hours and I can't seem to get the result I need. Running Oracle 10g database and attempting to use row_number but it returns undesired results.

My data looks as follows:
CONTRACT YEAR PERIOD YEAR_NUM
CONTRACT-81352 2008 Sep-2008 1
CONTRACT-81352 2008 Oct-2008 2
CONTRACT-81352 2008 Nov-2008 3
CONTRACT-81352 2008 Dec-2008 4
CONTRACT-81352 2009 Jan-2009 5
CONTRACT-81352 2009 Feb-2009 6
CONTRACT-81352 2009 Mar-2009 7
CONTRACT-81352 2009 Apr-2009 8
CONTRACT-81352 2009 May-2009 9
CONTRACT-81352 2009 Jun-2009 10
CONTRACT-81352 2009 Jul-2009 11
CONTRACT-81352 2009 Aug-2009 12
CONTRACT-81352 2009 Sep-2009 13
CONTRACT-81352 2009 Oct-2009 14
CONTRACT-81352 2009 Nov-2009 15
CONTRACT-81352 2009 Dec-2009 16
CONTRACT-81352 2010 Jan-2010 17
CONTRACT-81352 2010 Feb-2010 18
CONTRACT-81352 2010 Mar-2010 19
CONTRACT-81352 2010 Apr-2010 20
CONTRACT-81352 2010 May-2010 21
CONTRACT-81352 2010 Jun-2010 22
CONTRACT-81352 2010 Jul-2010 23
CONTRACT-81352 2010 Aug-2010 24
CONTRACT-81352 2010 Sep-2010 25
CONTRACT-81352 2010 Oct-2010 26
CONTRACT-81352 2010 Nov-2010 27
CONTRACT-81352 2010 Dec-2010 28
CONTRACT-81352 2011 Jan-2011 29
CONTRACT-81352 2011 Feb-2011 30
CONTRACT-81352 2011 Mar-2011 31
CONTRACT-81352 2011 Apr-2011 32
CONTRACT-81352 2011 May-2011 33
CONTRACT-81352 2011 Jun-2011 34
CONTRACT-81352 2011 Jul-2011 35
CONTRACT-81352 2011 Aug-2011 36
CONTRACT-81352 2011 Sep-2011 37

My desired result is to have a 1 in the YEAR_NUM column for 2008, 2 in the YEAR_NUM column for 2009, 3 in the YEAR_NUM column for 2010 and 4 in the YEAR_NUM column for 2011.

Obviously, it is doing a row_number() based off the period as well but when removing the period and keeping my groupins within SQL, I get the desired results (kind of). Essentialy, removing the period fixes the problem. I then have 4 lines and 1, 2, 3, and 4 sequentially in the YEAR_NUM column. However, I must display the period as well, but I don't want that to affect/impact by row_number partition by statement. So ultimately for the first four rows, it should all say 1 in the YEAR_NUM.

Any SQL advice is greatly appreciated.
Thanks!
 
Sort by date Sort by votes
Maven,

How about you post the code you have so far, and we'll attempt to build on that.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Here is what I have so far:

Code: 
SELECT DISTINCT contractnumber,
  to_number(to_char(result_date,   'YYYY')) stream_year,
  to_char(result_date,   'Mon-YYYY') stream_period,
row_number()OVER(PARTITION By contractnumber ORDER BY to_number(to_char(result_date,   'YYYY')),  to_date(to_char(result_date,   'Mon-YYYY'),'Mon-YYYY') ASC) year_number
FROM contract_stream_results
WHERE contractnumber = 'CONTRACT-81352'
 AND status_code = 'CURR'
 AND purpose_code = 'Non-Reporting'
 group by contractnumber, to_number(to_char(result_date,   'YYYY')), to_char(result_date,   'Mon-YYYY')
 order by   to_number(to_char(result_date,   'YYYY')), to_date(to_char(result_date,   'Mon-YYYY'),'Mon-YYYY') ASC
 
Upvote 0 Downvote
Maven,

Thanks for posting your code. With your code, I can propose the following enhancement that gives you want you want. The concept is to join your original query with an in-line view that simply sequences the distinct years (I left in your original rownums to compare to the new year ids; you can probably remove the original [now unneeded] rownum sequences.):
Code: 
select contractnumber,stream_year,stream_period,year_number,year_num
  from (SELECT DISTINCT contractnumber
              ,to_number(to_char(result_date,   'YYYY')) stream_year
              ,to_char(result_date,   'Mon-YYYY') stream_period
              ,row_number()OVER(PARTITION By contractnumber
                                    ORDER BY to_number(to_char(result_date,   'YYYY')),
                                             to_date(to_char(result_date,   'Mon-YYYY'),'Mon-YYYY') ASC) year_number
          FROM contract_stream_results
         WHERE contractnumber = 'CONTRACT-81352'
           AND status_code = 'CURR'
           AND purpose_code = 'Non-Reporting'
         group by contractnumber, to_number(to_char(result_date,   'YYYY')), to_char(result_date,   'Mon-YYYY'))
      ,(select rownum year_num
              ,year
          from (select distinct to_char(result_date,'yyyy') year
                  from contract_stream_results))
 where stream_year = year
 order by stream_year,year_number
/

CONTRACTNUMBER  STREAM_YEAR STREAM_P YEAR_NUMBER   YEAR_NUM
--------------- ----------- -------- ----------- ----------
CONTRACT-81352         2008 Sep-2008           1          1
CONTRACT-81352         2008 Oct-2008           2          1
CONTRACT-81352         2008 Nov-2008           3          1
CONTRACT-81352         2008 Dec-2008           4          1
CONTRACT-81352         2009 Jan-2009           5          2
CONTRACT-81352         2009 Feb-2009           6          2
CONTRACT-81352         2009 Mar-2009           7          2
CONTRACT-81352         2009 Apr-2009           8          2
CONTRACT-81352         2009 May-2009           9          2
CONTRACT-81352         2009 Jun-2009          10          2
CONTRACT-81352         2009 Jul-2009          11          2
CONTRACT-81352         2009 Aug-2009          12          2
CONTRACT-81352         2009 Sep-2009          13          2
CONTRACT-81352         2009 Oct-2009          14          2
CONTRACT-81352         2009 Nov-2009          15          2
CONTRACT-81352         2009 Dec-2009          16          2
CONTRACT-81352         2010 Jan-2010          17          3
CONTRACT-81352         2010 Feb-2010          18          3
CONTRACT-81352         2010 Mar-2010          19          3
CONTRACT-81352         2010 Apr-2010          20          3
CONTRACT-81352         2010 May-2010          21          3
CONTRACT-81352         2010 Jun-2010          22          3
CONTRACT-81352         2010 Jul-2010          23          3
CONTRACT-81352         2010 Aug-2010          24          3
CONTRACT-81352         2010 Sep-2010          25          3
CONTRACT-81352         2010 Oct-2010          26          3
CONTRACT-81352         2010 Nov-2010          27          3
CONTRACT-81352         2010 Dec-2010          28          3
CONTRACT-81352         2011 Jan-2011          29          4
CONTRACT-81352         2011 Feb-2011          30          4
CONTRACT-81352         2011 Mar-2011          31          4
CONTRACT-81352         2011 Apr-2011          32          4
CONTRACT-81352         2011 May-2011          33          4
CONTRACT-81352         2011 Jun-2011          34          4
CONTRACT-81352         2011 Jul-2011          35          4
CONTRACT-81352         2011 Aug-2011          36          4
CONTRACT-81352         2011 Sep-2011          37          4

37 rows selected.
Let us know if this is what you wanted.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Could you post your desired results for the data?

Here is your code formatted in a little more user friendly format:
Code: 
SELECT  DISTINCT 
        contractnumber,
        TO_NUMBER(TO_CHAR(result_date,'YYYY')) stream_year,
        TO_CHAR(result_date,'Mon-YYYY') stream_period,
        ROW_NUMBER () OVER 
            (PARTITION BY contractnumber 
             ORDER BY     TO_NUMBER(TO_CHAR(result_date,'YYYY')),
                          TO_DATE(TO_CHAR(result_date,'Mon-YYYY'),'Mon-YYYY')
             ASC
            ) year_number
FROM    contract_stream_results
WHERE   contractnumber = 'CONTRACT-81352'
AND     status_code    = 'CURR'
AND     purpose_code   = 'Non-Reporting'
GROUP BY
        contractnumber,
        TO_NUMBER(TO_CHAR (result_date,'YYYY')),
        TO_CHAR(result_date,'Mon-YYYY')
ORDER BY
        TO_NUMBER (TO_CHAR(result_date,'YYYY')),
        TO_DATE (TO_CHAR(result_date,'Mon-YYYY'), 'Mon-YYYY') ASC

Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com
 
Upvote 0 Downvote


Maybe DENSE_RANK() would work better:
Code: 
Select Contractnumber
     , To_Char(Result_Date, 'Yyyy') Stream_Year
     , To_Char(Result_Date, 'Mon-Yyyy') Stream_Period
     , DENSE_Rank() Over (Partition By Contract
       Order By To_Char(Result_Date, 'Yyyy')) Year_Number
  From Contract_Stream_Results
 Where Contractnumber = 'CONTRACT-81352'
   And Status_Code = 'CURR'
   And Purpose_Code = 'NON-REPORTING'
 Group By Contractnumber, To_Char(Result_Date,'Yyyy'), To_Char(Result_Date,'Mon-Yyyy')
 Order By Contractnumber, To_Char(Result_Date,'Yyyy'), To_Date(To_Char(Result_Date,'Mon-Yyyy'),'Mon-Yyyy')
/

CONTRACTNUMBER       STRE STREAM_P YEAR_NUMBER
-------------------- ---- -------- -----------
CONTRACT-81352       2008 Sep-2008           1
CONTRACT-81352       2008 Oct-2008           1
CONTRACT-81352       2008 Nov-2008           1
CONTRACT-81352       2008 Dec-2008           1
CONTRACT-81352       2009 Jan-2009           2
CONTRACT-81352       2009 Feb-2009           2
CONTRACT-81352       2009 Mar-2009           2
CONTRACT-81352       2009 Apr-2009           2
CONTRACT-81352       2009 May-2009           2
CONTRACT-81352       2009 Jun-2009           2
CONTRACT-81352       2009 Jul-2009           2
CONTRACT-81352       2009 Aug-2009           2
CONTRACT-81352       2009 Sep-2009           2
CONTRACT-81352       2009 Oct-2009           2
CONTRACT-81352       2009 Nov-2009           2
CONTRACT-81352       2009 Dec-2009           2
CONTRACT-81352       2010 Jan-2010           3
CONTRACT-81352       2010 Feb-2010           3
CONTRACT-81352       2010 Mar-2010           3
CONTRACT-81352       2010 Apr-2010           3
CONTRACT-81352       2010 May-2010           3
CONTRACT-81352       2010 Jun-2010           3
CONTRACT-81352       2010 Jul-2010           3
CONTRACT-81352       2010 Aug-2010           3
CONTRACT-81352       2010 Sep-2010           3
CONTRACT-81352       2010 Oct-2010           3
CONTRACT-81352       2010 Nov-2010           3
CONTRACT-81352       2010 Dec-2010           3
CONTRACT-81352       2011 Jan-2011           4
CONTRACT-81352       2011 Feb-2011           4
CONTRACT-81352       2011 Mar-2011           4
CONTRACT-81352       2011 Apr-2011           4
CONTRACT-81352       2011 May-2011           4
CONTRACT-81352       2011 Jun-2011           4
CONTRACT-81352       2011 Jul-2011           4
CONTRACT-81352       2011 Aug-2011           4
CONTRACT-81352       2011 Sep-2011           4

37 rows selected.
[3eyes]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
LK said:
Maybe DENSE_RANK() would work better
Click to expand...
Why, yes, it would. WDITOT (Why Didn't I Think Of That?) Hava a
star.gif
for your cleverness, LK!

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Thanks to all of you. The ideas coming out of this place are always EXCEPTIONAL.

The Dense Rank did work. I coudldn't use an inline view because im trying to use our reporting tool for the development. I can do custom SQL however and did think about your idea Mufasa. I was trying to stray away from custom sql for support purposes.

The DENSE_RANK does work like a charm. I saw it out there in the available list of functions but wasn't sure what it did that was so different.

Thanks to all and rep to each of you!
 
Upvote 0 Downvote
Maven,

Be sure to thank LK with a
star.gif
since that is the currency that we use here instead of $, €, £, et cetera. <grin>

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Livia8
  • Locked
  • Question
Question re: SELECT DISTINCT 1
Replies
6
Views
877
SkipVought
S
Livia8
  • Locked
  • Question
SQL script to find number of characters allowed in &quot;Description&quot; column of &quot;AP_INVOIC 2
Replies
5
Views
320
Livia8
Livia8
Andrzejek
  • Locked
  • Question
Format a number - TO_CHAR() 2
Replies
5
Views
750
SkipVought
S
Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
826
Andrzejek
Andrzejek
EM1107
  • Locked
  • Question
How to convert CLOB data into number and compare it to other table.
Replies
4
Views
632
carp
carp

Part and Inventory Search

Sponsor

Back
Top