Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query to get a time index
SQL Query to get a time index [message #420740] Tue, 01 September 2009 23:40 Go to next message
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 #420741 is a reply to message #420740] Tue, 01 September 2009 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results
Post detailed explanation how & why test data gets transformed into expected/desired results.
Re: SQL Query to get a time index [message #420742 is a reply to message #420740] Tue, 01 September 2009 23:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
TRUNC(<date difference>/15) will solve your problem.

regards,
Delna

[Updated on: Tue, 01 September 2009 23:57]

Report message to a moderator

Re: SQL Query to get a time index [message #420743 is a reply to message #420740] Tue, 01 September 2009 23:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #420751 is a reply to message #420750] Wed, 02 September 2009 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the result if static date is 2-Aug-2007 or 14-Aug-2007 or 15-Aug-2007 or 16-Aug-2007 or 30-Aug-2007 or 31-Aug-2007?
In short, what is the GENERAL rule to calculate the result from the static date?

Regards
Michel
Re: SQL Query to get a time index [message #420759 is a reply to message #420740] Wed, 02 September 2009 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select to_date('01/08/2007','DD/MM/YYYY')+5*(level-1) dt
  4      from dual
  5      connect by level <= 24
  6    )
  7  select dt,
  8         2*trunc(months_between(dt,to_date('01/08/2007','DD/MM/YYYY')))
  9         + greatest(0,sign(extract(day from dt)-14)) res
 10  from data
 11  order by 1
 12  /
DT                 RES
----------- ----------
01-AUG-2007          0
06-AUG-2007          0
11-AUG-2007          0
16-AUG-2007          1
21-AUG-2007          1
26-AUG-2007          1
31-AUG-2007          1
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

24 rows selected.

Regards
Michel
Re: SQL Query to get a time index [message #420769 is a reply to message #420750] Wed, 02 September 2009 02:43 Go to previous messageGo to next message
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 #420776 is a reply to message #420769] Wed, 02 September 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Michel's reply looks like it would fit your ruleset though.

No, just the example.

Regards
Michel
Re: SQL Query to get a time index [message #420782 is a reply to message #420769] Wed, 02 September 2009 03:47 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
This table explains you clearly

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



Thanks,
Rajesh.
Re: SQL Query to get a time index [message #420786 is a reply to message #420782] Wed, 02 September 2009 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
This table explains you clearly

Not at all.
Answer the following:
Quote:
What is the result if static date is 2-Aug-2007 or 14-Aug-2007 or 15-Aug-2007 or 16-Aug-2007 or 30-Aug-2007 or 31-Aug-2007?
In short, what is the GENERAL rule to calculate the result from the static date?

Any comment about the query I posted?

Regards
Michel
Re: SQL Query to get a time index [message #420801 is a reply to message #420786] Wed, 02 September 2009 05:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: SQL Query to get a time index [message #420952 is a reply to message #420801] Thu, 03 September 2009 08:14 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Waiting for your feedback.

Regards
Michel
Previous Topic: SQL Query for Oracle Datatbase
Next Topic: Help in Procedure
Goto Forum:
  


Current Time: Tue Feb 11 20:18:58 CST 2025