Home » SQL & PL/SQL » SQL & PL/SQL » Leave Calculation (merged)
icon8.gif  Leave Calculation (merged) [message #327215] Sun, 15 June 2008 05:45 Go to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Hi all
I'm new in SQL coding and I wish some one can help me in that please,

I have a leave table called emp_lv
having the following structure :

emp_id , lv_start_date , lv_end_date , No_of_Days
-------------------------------------------------------------
1001 , 15-MAR-2008 , 10-APR-2008 , 26
1005 , 01-JAN-2008 , 18-MAR-2008 , 77
1015 , 02-MAY-2008 , 25-MAY-2008 , 23

now in order to give the HR head a report to show the number of leave each month
the final output should be like this:


Emp_ID Month Year No_of_Days
---------------------------------------------
1001 MAR 2008 16
1001 APR 2008 10
1005 JAN 2008 31
1005 FEB 2008 29
1005 MAR 2008 17
1015 MAY 2008 23

Can any one give me the code to produce soo

Please help me, I need your Assistance urgently

[Updated on: Fri, 20 June 2008 08:12] by Moderator

Report message to a moderator

Re: Leave Calculation [message #327219 is a reply to message #327215] Sun, 15 June 2008 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Then guide will also gives you how to post and what we think about words like "urgent".

In the end, you have to give what you already tried.
I think you should search for "calendar", this will help you.

Also if you want to faster and appropriate answer you should post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
DATES BREAK DOWN [message #327227 is a reply to message #327215] Sun, 15 June 2008 08:03 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Hi all
I'm new in SQL coding and I wish some one can help me in that please,

I have a leave table called emp_lv
having the following columns


SQL> desc emp_lv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                             VARCHAR2(5)
 LV_START_DATE                                      DATE
 LV_END_DATE                                        DATE
 NO_OF_DAYS                                         NUMBER(3)



SQL> select * from emp_lv;

EMP_I LV_START_ LV_END_DA NO_OF_DAYS
----- --------- --------- ----------
1001  15-MAR-08 10-APR-08         26
1005  01-JAN-08 18-MAR-08         77
1015  02-MAY-08 25-MAY-08         23


now, I need to create a view that can break or distribute the number of leave days to each month as the following:

Emp_ID     Month     Year     No_of_Days
-----------------------------------------
1001       MAR       2008     16
1001       APR       2008     10
1005       JAN       2008     31
1005       FEB       2008     29
1005       MAR       2008     17
1015       MAY       2008     23


I know how to use date function which may needed here
like to_char(LV_START_DATE,'MON') and to_char(LV_START_DATE,'YYYY')


but how can I breake down the days on the monthes, also all this functions will give me an output in single line coz it's a single line conversion function and will will end up without getting the exact format I just demonstrated ubove

can any one assist
Thanks
Re: DATES BREAK DOWN [message #327230 is a reply to message #327227] Sun, 15 June 2008 08:55 Go to previous messageGo to next message
Ora_works
Messages: 6
Registered: April 2008
Junior Member
Try this

SELECT LAST_DAY(TO_DATE('15-MAR-08', 'DD-MON-YY') ) 
        - TO_DATE('15-MAR-08', 'DD-MON-YY') 
FROM DUAL

[Updated on: Sun, 15 June 2008 09:25] by Moderator

Report message to a moderator

Re: DATES BREAK DOWN [message #327232 is a reply to message #327227] Sun, 15 June 2008 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't start several topics on the subject above all in the same day.

And you didn't put a test case: create table and insert statements.

Regards
Michel

[Updated on: Sun, 15 June 2008 09:40]

Report message to a moderator

Re: DATES BREAK DOWN [message #327233 is a reply to message #327230] Sun, 15 June 2008 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Ora_works

How does this answer to OP's question?

Regards
Michel
Re: DATES BREAK DOWN [message #327237 is a reply to message #327233] Sun, 15 June 2008 10:01 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

thanks Ora_works but again this will yeild a single row result where as my iquiry is to provide a break down on month and year

actual result was 16 -- ok I will consider that employee spent a 16 days in Mar on vacation but how I can show the remaining balance (10 days ) and it's distripution on months

do I have to reapt the code with a UNION ALL clause, but again I caannot determined the period in the middle between Start date and End date.

the main concern here is how to generate a multible row for each employee and carrying up his leave days distributed on months and years as specified earlier

Thanks and please advise

Re: DATES BREAK DOWN [message #327240 is a reply to message #327237] Sun, 15 June 2008 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table emp_lv (emp_id integer, lv_start date, lv_end date);
SQL> alter session set nls_date_format='DD-MON-YYYY' nls_date_language=AMERICAN;
SQL> insert into emp_lv values (1001 , '15-MAR-2008' , '10-APR-2008');
SQL> insert into emp_lv values (1005 , '01-JAN-2008' , '18-MAR-2008');
SQL> insert into emp_lv values (1015 , '02-MAY-2008' , '25-MAY-2008');
SQL> commit;
SQL> select * from emp_lv order by 1, 2;
    EMP_ID LV_START    LV_END
---------- ----------- -----------
      1001 15-MAR-2008 10-APR-2008
      1005 01-JAN-2008 18-MAR-2008
      1015 02-MAY-2008 25-MAY-2008

3 rows selected.

SQL> with 
...
 16  order by e.emp_id, c.mon
 17  /
    EMP_ID      MONTH       YEAR     NBDAYS
---------- ---------- ---------- ----------
      1001          3       2008         17
      1001          4       2008         10
      1005          1       2008         31
      1005          2       2008         29
      1005          3       2008         18
      1015          5       2008         24

6 rows selected.

Regards
Michel
Re: DATES BREAK DOWN [message #327257 is a reply to message #327240] Sun, 15 June 2008 14:30 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

okk

can you submit the code

I think using with function is the only way to implement thatbut

Thanks
Re: DATES BREAK DOWN [message #327299 is a reply to message #327257] Mon, 16 June 2008 00:10 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

hi michel

I notice that you didn't take in consideration the last column (No_of_Days) which used to store the output calculation from the application to determine the number of leave days

I think this can be satisfied with looping technique, hence I have to deal with each row as individual

I wish any one can guide me how to acheive the desigred result set

Thanks

Re: DATES BREAK DOWN [message #327372 is a reply to message #327299] Mon, 16 June 2008 04:35 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

the solution is :
SQL> select emp_id,  2         case when to_char(lv_start, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  3                   then to_char(lv_start, 'Month - YYYY')  4              when to_char(lv_end, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  5                   then to_char(lv_end, 'Month - YYYY')  6              else to_char(add_months(lv_start, rno-1), 'Month - YYYY')  7         end  month_name,  8         case when to_char(lv_start, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  9                   then last_day(lv_start) - lv_start 10              when to_char(lv_end, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY') 11                   then lv_end + 1 - to_date('01'||to_char(lv_end, 'MMYYYY'), 'DDMMYYYY') 12              else to_number(to_char(last_day(add_months(lv_start, rno-1)), 'DD')) 13         end  monthly_absence, 14         no_of_days total_days_absent 15    from emp_lv a, 16         (select level rno from dual connect by level <= 100) b 17   where ceil(months_between( last_day(lv_end), to_date('01'||to_char(lv_start, 'MMYYYY'), 'DDMMYYYY'))) 18         >= b.rno 19  order by emp_id, rno;
Re: DATES BREAK DOWN [message #327379 is a reply to message #327372] Mon, 16 June 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you don't know how to go to the next line.
You also don't know how to use the Preview button.

Regards
Michel
Re: DATES BREAK DOWN [message #328223 is a reply to message #327372] Thu, 19 June 2008 07:05 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Your query is great.
but, did you notice that the no_of_days column contains 1 day less than the actual leave days?

It should have been
no_of_days = lv_end_date - lv_start_date + 1

So, the query should be
select emp_id, 
	case when to_char(lv_start, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')
		 	  then to_char(lv_start, 'Month - YYYY')  
		when to_char(lv_end, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  
			 then to_char(lv_end, 'Month - YYYY')  
		else to_char(add_months(lv_start, rno-1), 'Month - YYYY')  
	end  month_name,  
	case when to_char(lv_start, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  
		 	  then last_day(lv_start) - lv_start [B]+ 1[/B] 
		when to_char(lv_end, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY') 
			 then lv_end + 1 - to_date('01'||to_char(lv_end, 'MMYYYY'), 'DDMMYYYY') 
		else to_number(to_char(last_day(add_months(lv_start, rno-1)), 'DD')) 
	end  monthly_absence, 
	lv_end-lv_start+1 total_days_absent 
from emp_lv a, (select level rno 
	 		   from dual 
			   connect by level <= 10) b
where ceil(months_between( last_day(lv_end), to_date('01'||to_char(lv_start, 'MMYYYY'), 'DDMMYYYY'))) >= b.rno
order by emp_id, rno

[Updated on: Thu, 19 June 2008 07:12]

Report message to a moderator

Re: DATES BREAK DOWN [message #328254 is a reply to message #328223] Thu, 19 June 2008 09:27 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
this shows 20 days of monthly_absence for date between '11-JUN-2008' and '19-JUN-2008' ?

with emp_lv as 
(
  select 1001 emp_id, to_date('15-MAR-2008') lv_start, to_date('10-APR-2008') lv_end from dual
UNION ALL
  select 1005       , to_date('01-JAN-2008')         , to_date('18-MAR-2008')        from dual
UNION ALL
  select 1015       , to_date('11-JUN-2008')         , to_date('19-JUN-2008')        from dual
)
select emp_id, 
	case when to_char(lv_start, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')
		 	  then to_char(lv_start, 'Month - YYYY')  
		when to_char(lv_end, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  
			 then to_char(lv_end, 'Month - YYYY')  
		else to_char(add_months(lv_start, rno-1), 'Month - YYYY')  
	end  month_name,  
	case when to_char(lv_start, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY')  
		 	  then last_day(lv_start) - lv_start + 1 
		when to_char(lv_end, 'MMYYYY') = to_char(add_months(lv_start, rno-1), 'MMYYYY') 
			 then lv_end + 1 - to_date('01'||to_char(lv_end, 'MMYYYY'), 'DDMMYYYY') 
		else to_number(to_char(last_day(add_months(lv_start, rno-1)), 'DD')) 
	end  monthly_absence, 
	lv_end-lv_start+1 total_days_absent 
from emp_lv a, (select level rno 
	 		   from dual 
			   connect by level <= 10) b
where ceil(months_between( last_day(lv_end), to_date('01'||to_char(lv_start, 'MMYYYY'), 'DDMMYYYY'))) >= b.rno
order by emp_id, rno


   EMP_ID MONTH_NAME       MONTHLY_ABSENCE TOTAL_DAYS_ABSENT
---------- ---------------- --------------- -----------------
      1001 March     - 2008              17                27
      1001 April     - 2008              10                27
      1005 January   - 2008              31                78
      1005 February  - 2008              29                78
      1005 March     - 2008              18                78
      1015 June      - 2008              20                 9

Re: DATES BREAK DOWN [message #328263 is a reply to message #328254] Thu, 19 June 2008 09:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it all with analytics instead:
with emp_lv as ( select 1001 emp_id, to_date('15-MAR-2008') lv_start, to_date('10-APR-2008') lv_end from dual UNION ALL
                 select 1001 emp_id, to_date('15-FEB-2008') lv_start, to_date('16-FEB-2008') lv_end from dual UNION ALL
                 select 1005       , to_date('01-JAN-2008')         , to_date('18-MAR-2008')        from dual UNION ALL
                 select 1015       , to_date('11-JUN-2008')         , to_date('19-JUN-2008')        from dual )
    ,days   as ( select level d from dual connect by level <=100)                 
select emp_id
      ,lv_month
      ,lv_days
      ,tot_lv_days
from (select emp_id
            ,to_char(lv_dte,'Mon - yyyy') lv_month
            ,count(*) over (partition by emp_id,to_char(lv_dte,'Mon - yyyy')) lv_days
            ,count(*) over (partition by emp_id) tot_lv_days
            ,row_number() over (partition by emp_id,to_char(lv_dte,'Mon - yyyy') order by lv_dte) rnum
      from  (SELECT emp_id,lv_start+d lv_dte
             from   emp_lv,days
             where  lv_start+d <= lv_end))
where rnum = 1
order by emp_id,to_date(lv_month,'Mon - yyyy');

EMP_ID  LV_MONTH      LV_DAYS   TOT_LV_DAYS
1001    Feb - 2008    1         27
1001    Mar - 2008    16        27
1001    Apr - 2008    10        27
1005    Jan - 2008    30        77
1005    Feb - 2008    29        77
1005    Mar - 2008    18        77
1015    Jun - 2008    8         8
Re: DATES BREAK DOWN [message #328267 is a reply to message #328263] Thu, 19 June 2008 10:11 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
No Message Body

[Updated on: Thu, 19 June 2008 10:13]

Report message to a moderator

Re: DATES BREAK DOWN [message #328421 is a reply to message #328267] Fri, 20 June 2008 02:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or we can do it in a single pass like this:
SQL> with emp_lv as ( select 1001 emp_id, to_date('15-MAR-2008') lv_start, to_date('10-APR-2008') lv_end from dual UNION ALL
  2                   select 1001 emp_id, to_date('15-FEB-2008') lv_start, to_date('16-FEB-2008') lv_end from dual UNION ALL
  3                   select 1005       , to_date('01-JAN-2008')         , to_date('18-MAR-2008')        from dual UNION ALL
  4                   select 1015       , to_date('11-JUN-2008')         , to_date('19-JUN-2008')        from dual )
  5      ,days   as ( select level-1 d from dual connect by level <=100)                 
  6  select emp_id
  7        ,trunc(greatest(lv_start, trunc(add_months(lv_start,d),'MON')),'MON') month_start
  8        ,least(lv_end,trunc(add_months(lv_start,d+1),'MON')-1) - greatest(lv_start, trunc(add_months(lv_start,d),'MON'))+1  days
  9        ,sum(least(lv_end,trunc(add_months(lv_start,d+1),'MON')-1) - greatest(lv_start, trunc(add_months(lv_start,d),'MON'))+1) over (partition by emp_id) total_lv
 10  from   emp_lv
 11        ,days
 12  where  trunc(add_months(lv_start,d),'MON')<= trunc(lv_end)
 13  order by emp_id,lv_start;

    EMP_ID MONTH_STA       DAYS   TOTAL_LV
---------- --------- ---------- ----------
      1001 01-FEB-08          2         29
      1001 01-MAR-08         17         29
      1001 01-APR-08         10         29
      1005 01-JAN-08         31         78
      1005 01-MAR-08         18         78
      1005 01-FEB-08         29         78
      1015 01-JUN-08          9          9

7 rows selected.

SQL> 
Previous Topic: Execution time is faster on 2nd and subsequent runs ?
Next Topic: ORA-01652 when running procedure
Goto Forum:
  


Current Time: Wed Dec 07 16:48:49 CST 2016

Total time taken to generate the page: 0.13981 seconds