How to select date range from two tables

[tyb]

Dear frends
plz help
i'v two tables each having the date field, now i want to select the mximum and min date from both tables to show in the parameter form.

also plz advise me on how to use the date between function such that if the Ist & 2nd date are the same. it would select for the same date.

i.e. where date between date1 & date2 --if date1 & date2 are same -- then it would select for the same date.

 

[Karen99]

Maybe there is a easier way, but I would probably do the following :

Create or replace view vwDate
as
select date1 as ADate
from table1
union
select date2 as ADate
fom table2

then I would :

select max(adate), min(adate)
from vwDate

Regards
Karen

 

[tyb]

how simple, i didnt even have the idea

plz reply my 2nd question too i.e. if i use the
date between min(date) and max(date) in this case if there is the same value for max & min, it wont return any row. how to resolve this problem.

many cardial thanks for ur reply.

 

[Karen99]

Not sure if this will work in your case, but try the NVL function.

What nvl does is :

select nvl(column1,0)
from table1

if the value is null then it will return 0.

You can also have

select nvl(column1,1)
from table1

then if the value is null then it will return 1.

So you can return whatever you want if the value is null

 

[hoinz]

hi,

date between min(date) and max(date)

Could it be that you are unaware of the fact that Oracle date format includes time (i.e. hour, minute, second) as well?
(Don't be confused by the default date format, which only shows day/month/year. The time is usually there as well, though hidden.)

If min(date) and max(date) really are the same, then this time intervall will not cover a whole day as you perhaps may believe, but one single second.
And if none of your data is in this second, well, you won't get any columns selected.

hth

 

[tyb]

thanks both of u Karen & Hoins

but my question is

1. i want to show the dates onto my parameter from (starting from the min date to max date)

2. If the user selects a single date in both the DATE FROM & DATE TO , the report should be run for that specific date.

any suggessions plz

once again many thanks for ur precious time.

Best Regards.

 

[SantaMufasa]

Tyb,

If you use the following code fragment...

...WHERE trunc(tab_date) between trunc(date1) and trunc (date2)...

...and if tab_date, date1, and data2 are all nominally on the same day,
...then the TRUNC() function causes the "tab_date", "date1", and "date2" to have the same values, therefore the WHERE results in "TRUE" since the "BETWEEN" operator includes both the beginning and ending points as "inclusive" BETWEEN values.

Here is a simple proof-of-concept:

select 'hello' from dual
where trunc(sysdate) between trunc(sysdate) and trunc(sysdate);

'HELL
-----
hello

1 row selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:30 (20Oct04) UTC (aka "GMT" and "Zulu"), 22:30 (19Oct04) Mountain Time)

 

[tyb]

can u plz tell me how would i get all the date values in between my min and max dates regardless of thier existance into the table.

for instance if i get min date as 15-Oct-04 and max 30-Oct-04 then i want to get all the dates in between thse dates i.e. 15,16,17,18,19,20,21,22 .... 30-Oct-04


best regards

and many thanks to all of u

 

[SantaMufasa]

Tyb,

Here is a sample:

select :min_date, :max_date from dual;

:MIN_DATE :MAX_DATE
--------- ---------
15-OCT-04 30-OCT-04

select to_date(:min_date)+(rownum-1)
from all_objects
where to_date(:min_date)+(rownum-1) <= to_date(:max_date);

TO_DATE(:
---------
15-OCT-04
16-OCT-04
17-OCT-04
18-OCT-04
19-OCT-04
20-OCT-04
21-OCT-04
22-OCT-04
23-OCT-04
24-OCT-04
25-OCT-04
26-OCT-04
27-OCT-04
28-OCT-04
29-OCT-04
30-OCT-04

16 rows selected.

Let us know if this satisfies your requirement.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 06:03 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 23:03 (20Oct04) Mountain Time

 

[tyb]

hi santa

i'm trying it as

select trunc(min(desp_date))+(rownum-1)
from despatch
where trunc(min(desp_date))+(rownum-1) <= trunc(max(desp_date))

but get the error " GROUP FUNCTION NOT ALLOWED HERE "

if i change the "where" with "having"

then i get the error " NOT A GROUP EXPRESSION "


best regards

 

[SantaMufasa]

Tyb,

Here is what you want instead:

select min_date+(rownum-1)
from all_objects
    ,(select trunc(min(desp_date)) min_date
            ,trunc(max(desp_date)) max_date
        from despatch
     )
where min_date+(rownum-1) <= max_date
/

Please confirm that this worked for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 05:44 (23Oct04) UTC (aka "GMT" and "Zulu"),
@ 22:44 (22Oct04) Mountain Time

 

[tyb]

I've min date as 18-jun-03 and max date as 23-aug-03

and with the above query i'm returned with 993 rows i.e. starting from 18-jun-03 to 06-mar-06.

 

[SantaMufasa]

Tyb,

You'll need to show me your data values because when I use the same MIN(desp_date) and MAX(desp_date) as you, I receive the proper 67 values:

select min(desp_date), max(desp_date) from despatch;

MIN(DESP_ MAX(DESP_
--------- ---------
18-JUN-03 23-AUG-03

1 row selected.

select * from despatch;

DESP_DATE
---------
01-JUL-03
18-JUN-03 <-- Min Date
01-AUG-03
23-AUG-03 <-- Max Date
20-JUN-03

5 rows selected.

select min_date+(rownum-1)
from all_objects
    ,(select trunc(min(desp_date)) min_date
            ,trunc(max(desp_date)) max_date
        from despatch
     )
where min_date+(rownum-1) <= max_date;

MIN_DATE+
---------
18-JUN-03
19-JUN-03
20-JUN-03
21-JUN-03
22-JUN-03
23-JUN-03
24-JUN-03
25-JUN-03
26-JUN-03
27-JUN-03
28-JUN-03
29-JUN-03
30-JUN-03
01-JUL-03
02-JUL-03
03-JUL-03
04-JUL-03
05-JUL-03
06-JUL-03
07-JUL-03
08-JUL-03
09-JUL-03
10-JUL-03
11-JUL-03
12-JUL-03
13-JUL-03
14-JUL-03
15-JUL-03
16-JUL-03
17-JUL-03
18-JUL-03
19-JUL-03
20-JUL-03
21-JUL-03
22-JUL-03
23-JUL-03
24-JUL-03
25-JUL-03
26-JUL-03
27-JUL-03
28-JUL-03
29-JUL-03
30-JUL-03
31-JUL-03
01-AUG-03
02-AUG-03
03-AUG-03
04-AUG-03
05-AUG-03
06-AUG-03
07-AUG-03
08-AUG-03
09-AUG-03
10-AUG-03
11-AUG-03
12-AUG-03
13-AUG-03
14-AUG-03
15-AUG-03
16-AUG-03
17-AUG-03
18-AUG-03
19-AUG-03
20-AUG-03
21-AUG-03
22-AUG-03
23-AUG-03

67 rows selected.

As far as my tests go, the code is correct. Please post with an actual copy-and-paste: your min/max query results, my code that you are running, the start and end rows of the query showing "xxx rows selected."

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:31 (23Oct04) UTC (aka "GMT" and "Zulu"),
@ 09:31 (23Oct04) Mountain Time

 

[tyb]

Hi santa

can u plz give me ur mail, so that i can attatch the data out put ,u've asked for, as a word file.

its 19 pages in length.

Best Regards

 

[SantaMufasa]

Tyb,

I don't really need to look at 19 pages of output to solve the problem. Please do this for me...Just run the following query and please post the code you are running. Here is the query:

col a heading "Min Date" format a21
col b heading "Max Date" format a21
select to_char(min(desp_date),'yyyy-mm-dd hh24:mi:ss')a
     , to_char(max(desp_date),'yyyy-mm-dd hh24:mi:ss')b
from despatch;

...and post the code here that generates the 993 rows of output.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 07:48 (25Oct04) UTC (aka "GMT" and "Zulu"),
@ 00:48 (25Oct04) Mountain Time

 

[tyb]

i'm using the same code u did post to me i.e.

SQLWKS> select min(desp_date),max(desp_date) from despatch
2>
MIN(DESP_DATE) MAX(DESP_DATE)
-------------------- --------------------
18-JUN-03 23-AUG-03
1 row selected.
SQLWKS> select min_date+(rownum-1)
2> from all_objects
3> ,(select trunc(min(desp_date)) min_date
4> ,trunc(max(desp_date)) max_date
5> from despatch
6> )
7> where min_date+(rownum-1) <= max_date;

dont know y its giving problem

and the above query generated the min & max date values.


best regards

 

[SantaMufasa]

Tyb, my buddy, you did not run the code I posted !!! If you had run the code I posted, you would see the century as well as the years. So please copy-and-paste the code below at your SQL*Plus prompt:

col a heading "Min Date" format a21
col b heading "Max Date" format a21
select to_char(min(desp_date),'yyyy-mm-dd hh24:mi:ss')a
     , to_char(max(desp_date),'yyyy-mm-dd hh24:mi:ss')b
from despatch;

...then please post the results of the query here.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 07:19 (30Oct04) UTC (aka "GMT" and "Zulu"),
@ 00:19 (30Oct04) Mountain Time

 

[tyb]

Hi Santa

how could i overlook what u instructed. but i'm sorry for not posting the results i.e. the same as with the simple query. here are the results.

SQLWKS> select to_char(min(desp_date),'yyyy-mm-dd hh24:mi:ss')a
2> , to_char(max(desp_date),'yyyy-mm-dd hh24:mi:ss')b
3> from despatch;
A B
------------------- -------------------
2003-06-18 00:00:00 2003-08-23 00:00:00
1 row selected.

as for the formatting lines. it returns error (Invalid sql statement) so i've erased it.

Best Regards

 

[SantaMufasa]

Tyb,

The min/max dates look ready for success. If you then use the same min/max dates in the query I provided, I cannot see how it can generate anything besides the 67 dates I displayed in my post of 09:31 (23Oct04) Mountain Time.

Please post a copy-and-paste of my code running against the above min/max dates along with the first and last rows of output so we can troubleshoot your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 06:41 (01Nov04) UTC (aka "GMT" and "Zulu"),
@ 23:41 (31Oct04) Mountain Time

 

[tyb]

Hi dear

Heres what u've asked for

SQLWKS> select min(desp_date), max(desp_date) from despatch;
MIN(DESP_DATE) MAX(DESP_DATE)
-------------------- --------------------
18-JUN-03 23-AUG-03
1 row selected.
SQLWKS> select min_date+(rownum-1)
2> from all_objects
3> ,(select trunc(min(desp_date)) min_date
4> ,trunc(max(desp_date)) max_date
5> from despatch
6> )
7> where min_date+(rownum-1) <= max_date;
MIN_DATE+(ROWNUM-1)
--------------------
18-JUN-03
.
.
.
05-MAR-06
06-MAR-06
993 rows selected.

Best Regards