471,108 Members | 1,254 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Help needed in SQL Query

I have a table that has fields GRP_CODE & END_DATE. For a given
GRP_CODE and a given month of a year, there may be 1, 2 or 3 END_DATE
values.
For Example,
GRP_CODE END_DATE
AA 07/14/2003
AA 07/28/2003
BB 07/14/2003
BB 07/28/2003
AA 08/01/2003
AA 08/15/2003
AA 08/29/2003
BB 08/01/2003
BB 08/15/2003
BB 08/29/2003
....

I have to develop a query that has one input parameter, a date. The
query should return either 0, 1, 2, or 3 based on the match with the
END_DATE field.

In the above example, here is what the query should return the
following results for various input dates

Input Date Query Output
08/01/2003 1
08/15/2003 2
08/29/2003 3
08/30/2003 0
07/14/2003 1
07/28/2003 2

Can anybody help me with how the query can be written?

Thanks in advance.
Jul 19 '05 #1
4 4181

"Surendra" <su************@yahoo.com> wrote in message
news:74*************************@posting.google.co m...
I have a table that has fields GRP_CODE & END_DATE. For a given
GRP_CODE and a given month of a year, there may be 1, 2 or 3 END_DATE
values.
For Example,
GRP_CODE END_DATE
AA 07/14/2003
AA 07/28/2003
BB 07/14/2003
BB 07/28/2003
AA 08/01/2003
AA 08/15/2003
AA 08/29/2003
BB 08/01/2003
BB 08/15/2003
BB 08/29/2003
...

I have to develop a query that has one input parameter, a date. The
query should return either 0, 1, 2, or 3 based on the match with the
END_DATE field.

In the above example, here is what the query should return the
following results for various input dates

Input Date Query Output
08/01/2003 1
08/15/2003 2
08/29/2003 3
08/30/2003 0
07/14/2003 1
07/28/2003 2

Can anybody help me with how the query can be written?

Thanks in advance.


Look up COUNT and GROUP BY in the manuals
Jul 19 '05 #2
I think I have not been clear in my requirements

If there are 3 dates in a month, the indexes returned should be 1, 2,
3 based on what date is passed in. So if the input date is 07/14/2003,
since it is the first date in the month of July 2003, I should get the
index value of 1. If I input the date as 08/01/2003, since it is the
1st of the 3 date entries for August 2003, the query should return a
value of 1. If I use 08/29/2003 as the input, the query should return
an index of 3 as it is the third date in the month of August.


"Alan Mills" <Al********@xservices.pants.fujitsu.com> wrote in message news:<bh**********@news.icl.se>...
"Surendra" <su************@yahoo.com> wrote in message
news:74*************************@posting.google.co m...
I have a table that has fields GRP_CODE & END_DATE. For a given
GRP_CODE and a given month of a year, there may be 1, 2 or 3 END_DATE
values.
For Example,
GRP_CODE END_DATE
AA 07/14/2003
AA 07/28/2003
BB 07/14/2003
BB 07/28/2003
AA 08/01/2003
AA 08/15/2003
AA 08/29/2003
BB 08/01/2003
BB 08/15/2003
BB 08/29/2003
...

I have to develop a query that has one input parameter, a date. The
query should return either 0, 1, 2, or 3 based on the match with the
END_DATE field.

In the above example, here is what the query should return the
following results for various input dates

Input Date Query Output
08/01/2003 1
08/15/2003 2
08/29/2003 3
08/30/2003 0
07/14/2003 1
07/28/2003 2

Can anybody help me with how the query can be written?

Thanks in advance.


Look up COUNT and GROUP BY in the manuals

Jul 19 '05 #3

Try this (for Oracle 8i+):

Select Grp_Code, End_Date,
Rank() Over(Partition By Grp_Code Order By End_Date) As Sq
From The_Table Group By Grp_Code, End_Date;

--
Posted via http://dbforums.com
Jul 19 '05 #4
su************@yahoo.com (Surendra) wrote in message news:<74*************************@posting.google.c om>...
LKBrwn_DBA <me*********@dbforums.com> wrote in message news:<32****************@dbforums.com>...
Try this (for Oracle 8i+):

Select Grp_Code, End_Date,
Rank() Over(Partition By Grp_Code Order By End_Date) As Sq
From The_Table Group By Grp_Code, End_Date;


Thank you very much. This definitely gives me the result that I was looking for.

This query works well to give the output as desired.

Here is the query that I used

Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank()
Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by
End_Date)
As Sq From The_Table
Group by substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date
where substr(to_date(End_Date,"DD-MON-YYYY"),4)= "Jun-2003"

This gives me (exactly as I wanted)
Jun-2003 02-06-2003 1
Jun-2003 16-06-2003 2
Jun-2003 30-06-2003 3

My next question is how do I extract the Rank() from this result. I
cannot add a WHERE or HAVING clause specifying the input End_Date as
this will impact the sequencing. So for example, if the input Date is
16-06-2003, I should get 2 as the result. Based on 2, I have to do
further processing.

Thanks in advance for the input.
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Erich Trowbridge | last post: by
1 post views Thread by Ralph Freshour | last post: by
9 posts views Thread by netpurpose | last post: by
28 posts views Thread by stu_gots | last post: by
6 posts views Thread by paii | last post: by
6 posts views Thread by Takeadoe | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.