473,320 Members | 1,841 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 4271

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Erich Trowbridge | last post by:
Has anybody seen this tool? It is awesome. check out http://vw.vermeer.org/ . It's a php front end for large-scale syslog deployments. It makes managing syslog in large networks a snap. The idea...
1
by: Ralph Freshour | last post by:
I'm not sure the follow multiple table query is the right way to do what I need to do although it seems to be working: $php_SQL = "SELECT * ". "FROM basics, personal, photos ". "WHERE...
4
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
28
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical...
6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
5
by: Alicia | last post by:
Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;
7
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from...
6
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the...
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.