Can anybody plz help me on this ?
I am having a table containing 4 fields :id,name,effective-from-date,effective-to-date
(i) for a given date i have to display the rows in the table if that date is in between the effective-from-date and effective-to-date.
(ii)the above process should be repeated for each and every date between the given date range(for example,if the given range is 10-02-2007 and 20-02-2007, then we should consider 10th,11th,...,20th feb as inputs.
Thnx in advance
4 1415
Can anybody plz help me on this ?
I am having a table containing 4 fields :id,name,effective-from-date,effective-to-date
(i) for a given date i have to display the rows in the table if that date is in between the effective-from-date and effective-to-date.
(ii)the above process should be repeated for each and every date between the given date range(for example,if the given range is 10-02-2007 and 20-02-2007, then we should consider 10th,11th,...,20th feb as inputs.
Thnx in advance
so you want every row from the table where the date in the list is betweent the from and thru date, correct?
this is just off the top of my head (meaning double check this) but i think this will work. -
select *
-
from my_table
-
where effective-from-date <= range_thru_date
-
and effective-to-date >= range_from_date
-
range_thru_date is 20-02-2007
range_from_date is 10-02-2007
Dave,
thnx for ur response.. but our problem is somewhat different. plz let me explain in detail:
our table: ID NAME EFFECTIVE_FROM_DATE EFFECTIVE_TO_DATE
1 x 10-01-06 13-02-07
2 y 1-02-07 28-02-07
1 z 14-02-07 20-03-07
and if the given date range is 10-02-07 to 15-02-07 then the output should be: date id name
10-02-07 1 x
10-02-07 2 y
11-02-07 1 x
11-02-07 2 y
12-02-07 1 x
12-02-07 2 y
13-02-07 1 x
13-02-07 2 y 14-02-07 1 z
14-02-07 2 y 15-02-07 1 z
15-02-07 2 y
i.e, for each and every date in the given date range, we should display the row in the table if that date falls in between the effective-from-date and effective-to-date
Dave,
thnx for ur response.. but our problem is somewhat different. plz let me explain in detail:
our table: ID NAME EFFECTIVE_FROM_DATE EFFECTIVE_TO_DATE
1 x 10-01-06 13-02-07
2 y 1-02-07 28-02-07
1 z 14-02-07 20-03-07
and if the given date range is 10-02-07 to 15-02-07 then the output should be: date id name
10-02-07 1 x
10-02-07 2 y
11-02-07 1 x
11-02-07 2 y
12-02-07 1 x
12-02-07 2 y
13-02-07 1 x
13-02-07 2 y 14-02-07 1 z
14-02-07 2 y 15-02-07 1 z
15-02-07 2 y
i.e, for each and every date in the given date range, we should display the row in the table if that date falls in between the effective-from-date and effective-to-date
ohh i see, so more like this? -
[144]dave@ORADB> create table temp
-
2 (id number,
-
3 name varchar2(30),
-
4 from_date date,
-
5 thru_date date );
-
-
Table created.
-
-
Elapsed: 00:00:00.01
-
[144]dave@ORADB> INSERT INTO temp
-
2 VALUES (1,
-
3 'X',
-
4 TO_DATE('10-01-2006', 'dd-mm-yyyy'),
-
5 TO_DATE('13-02-2007', 'dd-mm-yyyy') );
-
-
1 row created.
-
-
Elapsed: 00:00:00.01
-
[144]dave@ORADB>
-
[144]dave@ORADB> INSERT INTO temp
-
2 VALUES (2,
-
3 'Y',
-
4 TO_DATE('01-02-2007', 'dd-mm-yyyy'),
-
5 TO_DATE('28-02-2007', 'dd-mm-yyyy') );
-
-
1 row created.
-
-
Elapsed: 00:00:00.01
-
[144]dave@ORADB>
-
[144]dave@ORADB> INSERT INTO temp
-
2 VALUES (1,
-
3 'Z',
-
4 TO_DATE('14-02-2007', 'dd-mm-yyyy'),
-
5 TO_DATE('20-03-2007', 'dd-mm-yyyy') );
-
-
1 row created.
-
-
Elapsed: 00:00:00.01
-
[144]dave@ORADB> SELECT TO_CHAR(search_date, 'dd-mm-yy') search_date,
-
2 ID,
-
3 NAME
-
4 FROM temp a,
-
5 (SELECT TO_DATE('10-02-2007', 'dd-mm-yyyy') +(LEVEL - 1) search_date
-
6 FROM DUAL
-
7 CONNECT BY LEVEL <= 1 +(TO_DATE('15-02-2007', 'dd-mm-yyyy') - TO_DATE('10-02-2007', '
-
dd-mm-yyyy') ) )
-
8 WHERE search_date BETWEEN a.from_date AND a.thru_date
-
9 ORDER BY search_date, ID, NAME;
-
-
SEARCH_D ID NAME
-
-------- ---------- ------------------------------
-
10-02-07 1 X
-
10-02-07 2 Y
-
11-02-07 1 X
-
11-02-07 2 Y
-
12-02-07 1 X
-
12-02-07 2 Y
-
13-02-07 1 X
-
13-02-07 2 Y
-
14-02-07 1 Z
-
14-02-07 2 Y
-
15-02-07 1 Z
-
15-02-07 2 Y
-
Hi Dave,
it's working fine....
thnx a lot......
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jerome |
last post by:
Hi,
I know this is an old problem and I've already tried to look the
solution up on the web but I didn't find what I need.
So, there's the following situation:
I've got an ASP page with a...
|
by: Lyn |
last post by:
Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of birth,
date of death, be in the future, and must not...
|
by: StBond |
last post by:
Hi everyone,
I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.
I am having a little problem with the database that I am...
|
by: Andy_Khosravi |
last post by:
I have been trying to build a user friendly search engine for a small
database I have created. I'm having some particular problems with one
of my date fields.
Here's the setup:
I'm using...
|
by: Geoff Jones |
last post by:
Hi
I have a table that has a column with Date types.
I am trying to view certain rows in the table using a DataView.
Using the filter, I can view the rows with, for example, the date equal...
|
by: seegoon |
last post by:
Hi to all.
I have a small problem I hope someone can help me with.
I am running a sql query to a csv file. The query searches for the
total of a column between 2 dates.
This is a copy of one of...
|
by: hardik |
last post by:
hi friends,
i am really surprized the way access behaves in date fields i mean
it's all ok when you have us time zone or us servers but if you have
diffrent timezone like uk then access creates...
|
by: Dave |
last post by:
Hello,
I have a possible problem exporting a text field that happens to
contain dates (but is not a date field) when using TransferText in MS
Access 2000.
I am exporting a query to a text...
|
by: creative1 |
last post by:
Hello everyone.
I am experiencing a strange problem that I can't fix on my own. I think I need expert's suggestions for this.
The problem is:
I want to print account statement (or any other...
|
by: Ceebaby via AccessMonster.com |
last post by:
Hi Folks
I wondered if someone could point me in the right direction before I
completely tear my hair out.
I have a user selection form where options can be selected for a report.
Users now...
|
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: 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: 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: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |