Home » SQL & PL/SQL » SQL & PL/SQL » Generating dates (9i)
Generating dates [message #445404] Mon, 01 March 2010 09:50 Go to next message
kaiserviky
Messages: 6
Registered: March 2010
Junior Member
Can you pls help with this

I have emp table


Empno	           from_date     to_date        Scale	Bonus_pct	       Salary
101	       31/01/2010	  14/02/2010	Grade1	20,10,10,15,25,20	5000



Reqd. o/p

Emp no	from_date	Scale	Each_WeeK_Sal	
101	31/01/2010	Grade1	1000	(from 20 %X5000
101	7/2/2010	Grade1	500	(from 10 %X5000
101	14/02/2010	Grade1	500	(from 10 % X5000
101	21/02/2010	Grade1	750	(from 15 % X5000
101	28/10/2010	Grade1	1250	(from 25 % X5000
101	7/2/2010	Grade1	1000	(from 20 % X5000


we have to split the date by 7 days,should be on Sunday...and if we split by 7 days we have upto 31/01,07/02 and 14/02...but we have to generate 6 days because 20,10,10,15,25,20 (since comma seperated value is 6 )..

Thanks a lot

Re: Generating dates [message #445406 is a reply to message #445404] Mon, 01 March 2010 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

What does mean "(from 20 %X5000"?
What happens (shoudl happen) if the number of values in Bonus_pct is not equal to the number of weeks between from_date and to_date?

Regards
Michel
Re: Generating dates [message #445410 is a reply to message #445406] Mon, 01 March 2010 10:26 Go to previous messageGo to next message
kaiserviky
Messages: 6
Registered: March 2010
Junior Member
create table test123
( empno number,
  from_date date,
  to_date date,
  scale varchar2(15),
  bonus_pct varchar2(100),
  salary float
);

insert into test123 values(101,to_date('31/01/2010','DD/MM/YYYY'),to_date('14/02/2010','DD/MM/YYYY'),
'Grade1','20,10,10,15,25,20',5000);


[i]What does mean "(from 20 %X5000"?[/i]


we have 5000 as salary and it should be multiplied with the first value that is 20% of the comma seperated value....

we have an another case

if we have

31/01/2010 to 14/02/2010 is greater than the Bonus_pct Comma seperated value like (50,50) then
the third row..for 14/02/2010 would be put to Zero

insert into test123 values(101,to_date('31/01/2010','DD/MM/YYYY'),to_date('14/02/2010','DD/MM/YYYY'),'Grade1','50,50',8000)


like this o/p
Emp no	from_date	Scale	Each_WeeK_Sal	
101	31/01/2010	Grade1	4000	50 %X8000
101	7/2/2010	Grade1	4000	50 %X8000
101	14/02/2010	Grade1	0	

Thanks a lot in advance

[Updated on: Mon, 01 March 2010 10:37] by Moderator

Report message to a moderator

Re: Generating dates [message #445412 is a reply to message #445410] Mon, 01 March 2010 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from test123;
     EMPNO FROM_DATE  TO_DATE    SCALE           BONUS_PCT                SALARY
---------- ---------- ---------- --------------- -------------------- ----------
       101 31/01/2010 14/02/2010 Grade1          20,10,10,15,25,20          5000
       102 31/01/2010 14/02/2010 Grade1          50,50                      8000

2 rows selected.

SQL> with
  2    lines as ( select level line from dual connect by level <= 10 ),
  3    data as (
  4      select empno, from_date, to_date, ','||bonus_pct||',' bonus_pct, salary
  5      from test123
  6    )
  7  select empno, from_date+7*(line-1) wk_date,
  8         nvl(to_number(substr(bonus_pct,
  9                              instr(bonus_pct,',',1,line)+1,
 10                              instr(bonus_pct,',',1,line+1)-instr(bonus_pct,',',1,line)-1))
 11             ,0)*salary/100 wk_salary
 12  from data, lines
 13  where line <= trunc((to_date-from_date)/7)+1
 14  order by empno, wk_date
 15  /
     EMPNO WK_DATE     WK_SALARY
---------- ---------- ----------
       101 31/01/2010       1000
       101 07/02/2010        500
       101 14/02/2010        500
       102 31/01/2010       4000
       102 07/02/2010       4000
       102 14/02/2010          0

6 rows selected.

Regards
Michel
Re: Generating dates [message #445426 is a reply to message #445404] Mon, 01 March 2010 16:16 Go to previous messageGo to next message
Bill B
Messages: 1457
Registered: December 2004
Senior Member
You might want to think about a redesign. While using instr, your values can be extracted. It is not normally a good idea to put more then one value in a column. this would be a good place to have a child table holding your percentages.
Re: Generating dates [message #445442 is a reply to message #445426] Mon, 01 March 2010 23:25 Go to previous messageGo to next message
kaiserviky
Messages: 6
Registered: March 2010
Junior Member
Can you pls let me know to put scale and bonus_pct in an another table..make a good design

scale   Bonus_pct
Grade1	30,40,30
Grade2	40,25,25,10
Grade3	25,15,30,30


scale would be the foreign key...

Thanks in advance
Re: Generating dates [message #445445 is a reply to message #445442] Mon, 01 March 2010 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
CREATE TABLE SCALE_BONUS (SCALE VARCHAR2(16), PCT NUMBER);
Re: Generating dates [message #445463 is a reply to message #445442] Tue, 02 March 2010 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does bonus_pct only depends on scale?
What is the relation between the 2? Are they dependent or not?
See Normalization.

Regards
Michel

[Updated on: Tue, 02 March 2010 00:16]

Report message to a moderator

Re: Generating dates [message #445740 is a reply to message #445463] Wed, 03 March 2010 13:11 Go to previous message
kaiserviky
Messages: 6
Registered: March 2010
Junior Member
Hi

Thanks all for your help...i got the reqd. result...

Thanks
Previous Topic: performance compare -> insert select and insert select using cursor
Next Topic: timestamp = sysdate-1 (merged 2)
Goto Forum:
  


Current Time: Mon Sep 26 19:38:07 CDT 2016

Total time taken to generate the page: 0.16567 seconds