473,325 Members | 2,870 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,325 software developers and data experts.

problem with dates

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
Mar 7 '07 #1
4 1415
Dave44
153 100+
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.

Expand|Select|Wrap|Line Numbers
  1. select *
  2. from   my_table
  3. where effective-from-date <= range_thru_date
  4. and    effective-to-date >= range_from_date
  5.  
range_thru_date is 20-02-2007
range_from_date is 10-02-2007
Mar 8 '07 #2
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
Mar 12 '07 #3
Dave44
153 100+
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?

Expand|Select|Wrap|Line Numbers
  1. [144]dave@ORADB> create table temp
  2.   2  (id    number,
  3.   3   name  varchar2(30),
  4.   4   from_date   date,
  5.   5   thru_date   date );
  6.  
  7. Table created.
  8.  
  9. Elapsed: 00:00:00.01
  10. [144]dave@ORADB> INSERT INTO temp
  11.   2       VALUES (1,
  12.   3               'X',
  13.   4               TO_DATE('10-01-2006', 'dd-mm-yyyy'),
  14.   5               TO_DATE('13-02-2007', 'dd-mm-yyyy') );
  15.  
  16. 1 row created.
  17.  
  18. Elapsed: 00:00:00.01
  19. [144]dave@ORADB> 
  20. [144]dave@ORADB> INSERT INTO temp
  21.   2       VALUES (2,
  22.   3               'Y',
  23.   4               TO_DATE('01-02-2007', 'dd-mm-yyyy'),
  24.   5               TO_DATE('28-02-2007', 'dd-mm-yyyy') );
  25.  
  26. 1 row created.
  27.  
  28. Elapsed: 00:00:00.01
  29. [144]dave@ORADB> 
  30. [144]dave@ORADB> INSERT INTO temp
  31.   2       VALUES (1,
  32.   3               'Z',
  33.   4               TO_DATE('14-02-2007', 'dd-mm-yyyy'),
  34.   5               TO_DATE('20-03-2007', 'dd-mm-yyyy') );
  35.  
  36. 1 row created.
  37.  
  38. Elapsed: 00:00:00.01
  39. [144]dave@ORADB> SELECT   TO_CHAR(search_date, 'dd-mm-yy') search_date,
  40.   2           ID,
  41.   3           NAME
  42.   4  FROM     temp a,
  43.   5           (SELECT     TO_DATE('10-02-2007', 'dd-mm-yyyy') +(LEVEL - 1) search_date
  44.   6            FROM       DUAL
  45.   7            CONNECT BY LEVEL <= 1 +(TO_DATE('15-02-2007', 'dd-mm-yyyy') - TO_DATE('10-02-2007', '
  46. dd-mm-yyyy') ) )
  47.   8  WHERE    search_date BETWEEN a.from_date AND a.thru_date
  48.   9  ORDER BY search_date, ID, NAME;
  49.  
  50. SEARCH_D         ID NAME
  51. -------- ---------- ------------------------------
  52. 10-02-07          1 X
  53. 10-02-07          2 Y
  54. 11-02-07          1 X
  55. 11-02-07          2 Y
  56. 12-02-07          1 X
  57. 12-02-07          2 Y
  58. 13-02-07          1 X
  59. 13-02-07          2 Y
  60. 14-02-07          1 Z
  61. 14-02-07          2 Y
  62. 15-02-07          1 Z
  63. 15-02-07          2 Y
  64.  
Mar 13 '07 #4
Hi Dave,
it's working fine....
thnx a lot......
Mar 13 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

6
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...
3
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...
3
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...
3
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...
11
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...
3
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...
2
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...
5
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...
7
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...
2
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...
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
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: 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...
1
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)...
1
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...
1
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....
0
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...
0
isladogs
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...

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.