yes, you can use an analytic function and take the first and last value in
the partition.
The partition will be by (working_leave) order by mydate
I'll show you tomorrow if I have time or you have not worked it out by then.
I encourage you to take a try at it.
It will look something like
select first_value(mydate) partition by working_leave order by mydate
,last_value(mydate) partition by working_leave order by mydate
,working_leave
FROM ( your entire previously written select statement goes here) ;
Regards,
Mike
On Sun, Nov 9, 2008 at 8:09 AM, Binu K S <[EMAIL PROTECTED]> wrote:
> Hai Mike ,
> Thanks the help . With the help of u r query and some pl/sql statement
> I completed my work .
>
> Using this query , i am getting the following output .
>
> 01-NOV-08 Leave
> 02-NOV-08 Leave
> 03-NOV-08 Leave
> 04-NOV-08 Leave
> 05-NOV-08 working
> 06-NOV-08 working
> 07-NOV-08 working
> 08-NOV-08 working
> 09-NOV-08 Leave
> 10-NOV-08 Leave
> 11-NOV-08 Leave
> 12-NOV-08 Leave
> 13-NOV-08 Leave
> 14-NOV-08 Leave
> 15-NOV-08 working
> 16-NOV-08 working
> 17-NOV-08 working
> 18-NOV-08 working
> 19-NOV-08 working
> 20-NOV-08 working
> 21-NOV-08 working
> 22-NOV-08 working
> 23-NOV-08 working
> 24-NOV-08 working
> 25-NOV-08 working
> 26-NOV-08 working
> 27-NOV-08 Leave
> 28-NOV-08 Leave
> 29-NOV-08 Leave
> 30-NOV-08 Leave
>
>
>
> How can I format this into the following way .
>
> 01-NOV-08 04-NOV-08 Leave
> 05-NOV-08 08-NOV-08 working
> 09-NOV-08 14-NOV-08 Leave
> 15-NOV-08 26-NOV-08 working
> 27-NOV-08 30-NOV-08 Leave
>
> Any of the analytic function can be used ?
> Thanks
> Binu
>
>
> On 11/5/08, Michael Moore <[EMAIL PROTECTED]> wrote:
>>
>> This should give you enough to complete the job. If not, let me know and
>> we'll take it to the next step. Obviously, you can replace the hard coded
>> dates with PL/SQL variables.
>> -- Mike --
>>
>> CREATE TABLE date_ranges (start_date DATE,
>> end_date DATE);
>>
>> INSERT INTO date_ranges VALUES (TO_DATE('28/10/08', 'DD/MM/YY'),
>> TO_DATE('04/11/08', 'DD/MM/YY'));
>> INSERT INTO date_ranges VALUES (TO_DATE('09/11/08', 'DD/MM/YY'),
>> TO_DATE('14/11/08', 'DD/MM/YY'));
>> INSERT INTO date_ranges VALUES (TO_DATE('27/11/08', 'DD/MM/YY'),
>> TO_DATE('03/12/08', 'DD/MM/YY'));
>> commit;
>>
>> SELECT day_date, nvl2(start_date,'Leave','working') wk_or_lv
>> FROM (SELECT TO_DATE ('20081101', 'yyyymmdd') + LEVEL - 1 day_date
>> FROM DUAL
>> CONNECT BY LEVEL <=
>> TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE ('20081101',
>> 'yyyymmdd')),
>> 'DD'))) gen
>> LEFT OUTER JOIN
>> date_ranges ON (day_date BETWEEN start_date AND end_date)
>> ;
>>
>> On Tue, Nov 4, 2008 at 9:30 AM, Binu K S <[EMAIL PROTECTED]> wrote:
>>
>>> Hai ,
>>> this is not a report. This for salary calculation. For example an
>>> employee is taken leave twice in a month , eg first time it is emergency
>>> leave and another time it is sick leave . The employee taken Emergency leave
>>> from 10-nov-2008 - 15-nov-2008 and sick leave from 20-nov-2008 to
>>> 23-nov-2008 . The remaing days he is present for work . So when the salry
>>> for the month of November is preparing there will be five records .
>>>
>>> 1 from 1-nov-2008 to 9-nov-2008
>>> 2 from 10-nov-2008 t0 15-nov-2008 (Leave )
>>> 3 from 16-nov-2008 to 19-nov-2008
>>> 4 from 20-nov-2008 to 23-nov-2008 (Leave )
>>> 5 from 24-nov-2008 to 30-Nov-2008
>>>
>>> Then the salary will be calculated based on these period . The
>>> calculation of allowance during the leave period is based on lots
>>> parameter . The information available at present is the leave details . If
>>> there is no information available in the leave table , then the employee is
>>> present in the work .
>>>
>>> Regards
>>> Binu
>>>
>>>
>>> On 11/4/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>>>
>>>>
>>>>
>>>>
>>>> On Nov 3, 11:16 pm, "Binu K S" <[EMAIL PROTECTED]> wrote:
>>>> > Hai,
>>>> >
>>>> > I am working in an HR project . I have one table which store leave
>>>> > information . The main fields are
>>>> >
>>>> > Leave_code , Employee_code , Leave_type , From_date , To_date
>>>> >
>>>> > Here Leave Code is the primary key of the table .
>>>> > Employee Code is the employee Number .
>>>> > Leave Type is the type of leave . Numeric field refering to master
>>>> table
>>>> > which contain different type of leave . Eg 1 - Annual Leave , 2 -
>>>> Sick
>>>> > Leave
>>>> > 3 Emergency leave .
>>>> > From Date and To Date are the leave starting date and leave end date .
>>>> >
>>>> > For Example I have the following data in the leave table
>>>> >
>>>> > Leave_Code Employee_Code Leave_Type From_Date
>>>> To_Date
>>>> >
>>>> -----------------------------------------------------------------------------
>>>> > 1 2050 2
>>>> > 10-Nov-2008 15-Nov-2008
>>>> > 2 2050 3
>>>> > 20-Nov-2008 25-Nov-2008
>>>> >
>>>> > When I am preparing salary for the Month Of November i need to split
>>>> the
>>>> > date as follows
>>>> >
>>>> > 01-Nov-2008 to 09 -Nov-2008 Working
>>>> > 10-Nov-2008 to 15-Nov-2008 Leave ( Sick Leave )
>>>> > 16-Nov-2008 to 19-Nov-2008 Working
>>>> > 20-Nov-2008 to 25-Nov-2008 Leave(Emergency Leave)
>>>> > 26-Nov-2008 to 30-Nov-2008 Working
>>>> >
>>>> > While spliting the date we need to consider following cases also .
>>>> >
>>>> > 1 Leave may start in the previous month and end in the current month .
>>>> For
>>>> > example the leave can start from 28-OCT-2008 and Ends on 5 - Nov -
>>>> >
>>>> > 2008 . In that case the out put must be
>>>> >
>>>> > 1-nov-2008 to 5-Nov-2008 Leave
>>>> > 6-Nov-2008 to 30-Nov-2008 Office .
>>>> >
>>>> > Similarly the leave can start in the current month and ends in the
>>>> next
>>>> > month .For example the leave can start from 25-nov-2008 and Ends on 10
>>>> -
>>>> >
>>>> > dec - 2008 . In that case the out put must be
>>>> >
>>>> > 1-nov-2008 to 24-Nov-2008 Office
>>>> > 25-Nov-2008 to 30-Nov-2008 Leave
>>>> >
>>>> > U can use PL/SQL
>>>> >
>>>> > Hope I will get Help From u people
>>>> >
>>>> > Thanks
>>>> > Regards
>>>> > Binu
>>>>
>>>> This entire report is generated from this one table of data? What
>>>> have you tried to solve this?
>>>>
>>>>
>>>> David Fitzjarrell
>>>>
>>>>
>>>>
>>
>>
>>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
