SQL Query to get a time index [message #420740] |
Tue, 01 September 2009 23:40  |
mr.rajeshyadav
Messages: 48 Registered: November 2007
|
Member |
|
|
hi ,
I have a table with some columns one of them is a date column.I want to get a time index compared with a static date value.
For example CLMDTE is a date column and it varies from 01-AUG-07 to 30-NOV-07 and the static date value is '01-AUG-07' now if the CLMDTE value is between '01-AUG-07' and '14-AUG-07' i want to get Index as 0 like wise if CLMDTE value is between '05-AUG-07' and '31-AUG-07' then index as 1 and CLMDTE value is between '01-SEP-07' and '14-SEP-07' then index as 2 and CLMDTE value is between '15-SEP-07' and '30-SEP-07' then index as 3
like wise i want to get an incremental time index till 30-NOV-07
can any one share their suggestions.
Thanks In advance,
Raj
|
|
|
|
|
Re: SQL Query to get a time index [message #420743 is a reply to message #420740] |
Tue, 01 September 2009 23:58   |
mr.rajeshyadav
Messages: 48 Registered: November 2007
|
Member |
|
|
DDL is
create table test_tab(code varchar2(10),clmdate date default sysdate)
DML queries for inserting dummy data
insert into test_tab(code,clmdate) values('A','01-AUG-07');
insert into test_tab(code,clmdate) values('A','12-AUG-07');
insert into test_tab(code,clmdate) values('B','14-AUG-07');
insert into test_tab(code,clmdate) values('C','01-SEP-07');
insert into test_tab(code,clmdate) values('A','12-OCT-07');
insert into test_tab(code,clmdate) values('B','14-SEP-07');
insert into test_tab(code,clmdate) values('A','01-NOV-07');
insert into test_tab(code,clmdate) values('A','16-NOV-07');
insert into test_tab(code,clmdate) values('A','31-NOV-07');
|
|
|
Re: SQL Query to get a time index [message #420747 is a reply to message #420743] |
Wed, 02 September 2009 00:33   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
First thing to note is that '01-AUG-07' is not a date, it is a string, and it it very poor practice to use a string to insert into a date column, (or indeed use dates in pretty much any other way too)
Always use the to_date function when working with strings that should be dates:
TO_DATE('01-AUG-07', 'DD-Mon-YYYY')
In addition, never use 2 digit years. Always use 4 digit years. (Is '01-AUG-07' in 1907, 2007 or 2107 or some other year?)
Now, as to your question, you need to explain in words the rules that govern the correlation between date and index.
[Updated on: Wed, 02 September 2009 00:34] Report message to a moderator
|
|
|
Re: SQL Query to get a time index [message #420750 is a reply to message #420747] |
Wed, 02 September 2009 00:43   |
mr.rajeshyadav
Messages: 48 Registered: November 2007
|
Member |
|
|
what ever i have posted here are just dummy tables i have live data separately i have created this table for testing purpose only.
Any ways thanks for your suggesion.
The rules to index is for an example from the test data when the clmdate value is in between 1-AUG-07 and 14-AUG-07 the index should be 0 and
if clmdate value is in between 15-AUG-07 and 31-AUG-07 the index should be 1
if clmdate value is in between 1-SEP-07 and 14-SEP-07 the index should be 2
like wise till 30-NOV-07 i want incremental indexes
|
|
|
|
|
Re: SQL Query to get a time index [message #420769 is a reply to message #420750] |
Wed, 02 September 2009 02:43   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
mr.rajeshyadav wrote on Wed, 02 September 2009 06:43 | what ever i have posted here are just dummy tables i have live data separately i have created this table for testing purpose only.
|
That much is obvious. My point as to the treatment of dates remains.
Quote: | The rules to index is for an example from the test data when the clmdate value is in between 1-AUG-07 and 14-AUG-07 the index should be 0 and
if clmdate value is in between 15-AUG-07 and 31-AUG-07 the index should be 1
| That is not a rule, that is a set of examples. Personally, I'm not going to make guesses as to what it is you need. I'm sure others will, but I would suggest that you have a think about exactly what it is that you are looking to achieve and transl;ating that into a set of rules avoiding examples (unless they are there to supplement the rules.
Michel's reply looks like it would fit your ruleset though.
|
|
|
|
|
|
Re: SQL Query to get a time index [message #420801 is a reply to message #420786] |
Wed, 02 September 2009 05:54   |
mr.rajeshyadav
Messages: 48 Registered: November 2007
|
Member |
|
|
the query you posted is for August alone it is not generic to any month
Quote: |
No.of days 28 29 30 31
Time Index 1-14 1-14 1-14 1-14
Time Index 15-28 15-29 15-30 15-31
|
but what i was asking is as per the above table if the number of days in a month are 28 days then then days from 1 to 14 should get an index other wise index+1.
when we run a query for the results bet ween 01-Aug-2007 to 31-NOV-2007 then from 01-AUG-2007 to 15-AUG-2007 i have to get an index as 0
and from 16-AUG-2007 to 31-AUG-2007 i have to get an index as 1
and from 1-SEP-2007 to 15-SEP-2007 i have to get an index as 2
and from 16-SEP-2007 to 30-SEP-2007 i have to get an index as 3
like wise i have to get an incremental time index.
Thanks,
Rajesh.
|
|
|
Re: SQL Query to get a time index [message #420802 is a reply to message #420801] |
Wed, 02 September 2009 05:58   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Do you think that
05-SEP-2007 2
10-SEP-2007 2
15-SEP-2007 3
20-SEP-2007 3
25-SEP-2007 3
30-SEP-2007 3
05-OCT-2007 4
10-OCT-2007 4
15-OCT-2007 5
20-OCT-2007 5
25-OCT-2007 5
30-OCT-2007 5
04-NOV-2007 6
09-NOV-2007 6
14-NOV-2007 6
19-NOV-2007 7
24-NOV-2007 7
are august dates?
And you still didn't answer to my questions.
Do you want help or not?
And why 14-Aug/15-Aug become now 15-Aug/16-Aug? Have you clear specifications?
Regards
Michel
[Updated on: Wed, 02 September 2009 06:02] Report message to a moderator
|
|
|
|