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. 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
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
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 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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 , ;
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
| |