Hai Michale ,
Thanks
Will try to do this .
Regards
binu
On 11/10/08, Michael Moore <[EMAIL PROTECTED]> wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
