Building a very difficult report....need some help!!

From: nycman <lnlx_at_aol.com>
Date: 17 Aug 2005 08:26:52 -0700
Message-ID: <1124292412.168559.187370@z14g2000cwz.googlegroups.com>

hello,

I have been having a hard time trying to produce a report with the following
requirements: The report is a billing report. I hope you can help or point me
in the right direction.

report requirements:

---

out_date date_in Bill

2/23 3/2 3/1 to 3/2

3/1 3/3 3/1 to 3/3

3/1 4/14 3/1 to 3/31

3/1 - 3/1 to 3/31

2/23 - 3/1 to 3/31

---

  select t.*,

           greatest( in_date, to_date('mar-2005','mon-yyyy') ) fixed_in_date,

           least( nvl(out_date,to_date('3000','yyyy')), last_day( to_date(
'mar-2005', 'mon-yyyy' ) ) ) fixed_out_date

      from t
     where in_date < last_day( to_date( 'mar-2005', 'mon-yyyy' ) )+1
       and out_date >= to_date( 'mar-2005', 'mon-yyyy' );

But I would like to start the report with the first time it went out. That is to say...

this is how it looks now with the above query...

  date_in date_out
3/22/2005 8:27:03 PM 3/29/2005 5:05:04 PM 3/30/2005 3:08:39 PM 3/31/2005

how the data looks

  date_in date_out
3/22/2005 8:27:03 PM 3/29/2005 5:05:04 PM---first went out 3/30/2005 3:08:39 PM 4/16/2005 1:40:44 PM

How I would like to see it since we begin billing from the first date the truck went out.

  date_out                 date_in

3/29/2005 5:05:04 PM 3/30/2005 3:08:39 PM 3/30/2005 3:08:39 PM 3/31/2005

or when the pair falls outside of March let's say in Feb.

it should look like this..as stated above.

let's say if it were like this...

date_in date_out

2/16/2005 4:17:22 PM  2/24/2005 1:39:04 PM
2/25/2005 12:31:45 PM 2/25/2005 6:35:42 PM----fall outside of march
3/21/2005 7:40:20 PM  4/3/2005 4:34:50 PM

it should look like this..

date_out date_in
2/25/2005 6:35:42 PM 3/21/2005 7:40:20 PM Received on Wed Aug 17 2005 - 10:26:52 CDT