Re: Display of Dates

From: magpies <-_->
Date: Wed, 13 Nov 2002 23:02:39 +0800
Message-ID: <aqtpi2$b7u1@imsp212.netvigator.com>

select to_char(insert_date, 'MON-YYYY') insert_month,

to_date(substr(max(to_char(insert_date,'yyyymmdd')||to_char(start_date,'yyyy mmdd')||to_char(end_date,'yyyymmdd')||to_char(qtr)), 1, 10), 'yyyymmdd') insert_date

to_date(substr(max(to_char(insert_date,'yyyymmdd')||to_char(start_date,'yyyy mmdd')||to_char(end_date,'yyyymmdd')||to_char(qtr)), 11, 10), 'yyyymmdd') start_date

to_date(substr(max(to_char(insert_date,'yyyymmdd')||to_char(start_date,'yyyy mmdd')||to_char(end_date,'yyyymmdd')||to_char(qtr)), 21, 10), 'yyyymmdd') end_date

to_number(substr(max(to_char(insert_date,'yyyymmdd')||to_char(start_date,'yy yymmdd')||to_char(end_date,'yyyymmdd')||to_char(qtr)), 31)) from <table>
where ???
group by to_char(insert_date, 'MON-YYYY');

The result for the following data will be: 8/4/02, 9/1/02, 10/3/02, 22
9/4/02, 10/1/02, 10/3/02, 7

---

Inserted Date, Start_Date , End_Date, Qty

8/1/02,    9/01/02,    10/31/02, 30
8/2/02,    9/10/02,    10/20/02, 10
8/3/02,    9/21/02,     10/1/02 , 5
8/4/02,    9/1/02,     10/3/02, 22

9/1/02,    10/01/02,    10/31/02, 30
9/2/02,    10/10/02,    10/20/02, 10
9/3/02,    10/1/02,     10/1/02 , 5
9/4/02,    10/1/02,     10/3/02, 7

"radhika" <rashika_18_at_hotmail.com> wrote in message news:5b8f3c02.0211121342.413ef8a4_at_posting.google.com...
> Hi All,
> Please help me with this query .
>
> In table A , i have the following columns and records.
>
> Inserted Date, Start_Date , End_Date, Qty
> 9/1/02, 10/01/02, 10/31/02, 30
> 9/2/02, 10/10/02, 10/20/02, 10
> 9/3/02, 10/1/02, 10/1/02 , 5
> 9/4/02, 10/1/02, 10/3/02, 7
>
> The user can only insert a startdate, end date and a qty
> he cannot update/delete records but can insert records if he changes
> his mind on the qty
> the latest records overrides any previous records.
> So he came in on 9/1 and entered for the whole month
> then he kept changing his mind.
> So
> Now in the web app, i have to display the above dates in the following
> manner.
>
> 10/01/02 - 10/03/02 - 7
> 10/04/02 - 10/09/02 - 30
> 10/10/02 - 10/20/02 - 10
> 10/21/02 - 10/31/02 - 30
>
> Peformancewise, please suggest a way to query the above table and
> display the data in the above manner
>
> thanks
Received on Wed Nov 13 2002 - 09:02:39 CST