Home » SQL & PL/SQL » SQL & PL/SQL » Display all the months between two dates
Display all the months between two dates [message #348440] Tue, 16 September 2008 15:08 Go to next message
texan
Messages: 14
Registered: September 2008
Junior Member
Hi everyone,hope eveyone doing great! I am new to this forum and i would like to learn new things and suggessions.

I am getting a problem while resolving a scenario to load the dates and corresponding slaries.

I have employee table and Time_Dim tables in my DW

Employee table has columns like Emp_id,emp_start_month,emp_end_month, emp_type and annual_sal
Time_Dim table has columns like f_date,f_month_number,f_month_name like that

Now my scenario is

Based on Emp_type and Emp_start_date i want to calcualte emp_salary for each month.

I want to show each emp salary for every month.

If EMP_TYPE = 'MANAGER' then (total_sal/add_months(emp_start_dt,12))

i want to do it like above, but it has to display for all the months like jan, feb,mar

My hard time is tp define a filed that shows each month name and number in a Cursor.


Example: LIKE BELOW
Emp_id, Month, Emp_Start_dt, Month_sal
111,   3,     1-Mar-08,        1000
111,   4,     1-Mar-08,        1000
111,   5,     1-Mar-08 ,       1000
111,   6,     1-Mar-08,        1000
222,   4,     15-Apr-08,       2000
222,   5,     15-Apr-08,       2000
222,   6,     15-Apr-08,       2000


[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Tue, 16 September 2008 23:38] by Moderator

Report message to a moderator

Re: Display all the months between two dates [message #348442 is a reply to message #348440] Tue, 16 September 2008 15:24 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
As advised,

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above.

Post a test case with actual and desired results.

Quote:

If EMP_TYPE = 'MANAGER' then (total_sal/add_months(emp_start_dt,12))


I guess total_sal of the above quote is annual_sal in your employee table.

Giving a proper test case will help others understand the problems you are facing better thereby giving better solutions rather than mere guesses.


Regards,
Jo
Re: Display all the months between two dates [message #348446 is a reply to message #348442] Tue, 16 September 2008 15:55 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
Thanks for the reply. Please find the attachment for test case. Please let me know, if that is not clear.

My scenario is

if emp_type = 'MANAGER' then calculations shouls start from emp_start_dt + 12 months

that means (total_sal/12) starting from emp_start_dt.

If emp_type = 'CONTRACTOR' then total_price/(emp_end_dt-emp_start_dt)

But in both scenario's i would like to present the data in monthly basis.

Please find the attachment.
Re: Display all the months between two dates [message #348475 is a reply to message #348440] Tue, 16 September 2008 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Please find the attachment.
Am I going blind? I don't see any attachment.
Re: Display all the months between two dates [message #348649 is a reply to message #348475] Wed, 17 September 2008 08:41 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
I am sorry, please find it now. I did it yesterday but that was a .xls file. Please let me know if you still not find it.
Re: Display all the months between two dates [message #348653 is a reply to message #348649] Wed, 17 September 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A test case is create table and insert statements.

Regards
Michel
Re: Display all the months between two dates [message #348660 is a reply to message #348653] Wed, 17 September 2008 09:01 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
I did not understand why we need a create statement and insert statement?

I am trying to split or each month name along with eqally devided montly salaray.

Re: Display all the months between two dates [message #348670 is a reply to message #348660] Wed, 17 September 2008 09:27 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
A sample test case would be like....

-- For Create Statement of your table
CREATE TABLE TEST_CASE
(
  EMP_ID      VARCHAR2(255 BYTE),
  EMP_TYPE    VARCHAR2(255 BYTE),
  START_DATE  DATE,
  END_DATE    DATE,
  TOT_SAL     VARCHAR2(255 BYTE)
)


-- For Sample Records (Your Insert Statements)

Insert into TEST_CASE
   (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
 Values
   ('111', 'MANAGER', TO_DATE('01/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('04/10/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    '150000');

Insert into TEST_CASE
   (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
 Values
   ('222', 'MANAGER', TO_DATE('02/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    '200000');

Insert into TEST_CASE
   (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
 Values
   ('333', 'CONTRACTOR', TO_DATE('12/15/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('12/15/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    '2500000');

COMMIT;


Hope you get the idea now.

Regards,
Jo

[Updated on: Fri, 19 September 2008 14:03] by Moderator

Report message to a moderator

Re: Display all the months between two dates [message #348673 is a reply to message #348670] Wed, 17 September 2008 09:45 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
I am really sorry guys, the one you said it for actual(source) table. You want me to write another Insert statement to show how i want to insert into the target table?
Re: Display all the months between two dates [message #348680 is a reply to message #348660] Wed, 17 September 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
texan wrote on Wed, 17 September 2008 16:01
I did not understand why we need a create statement and insert statement?

I am trying to split or each month name along with eqally devided montly salaray.

We need create table and insert statements in order to test our ideas and provide you some solution that really works.
You in return have to help us help you.
You have to provide something we can work with.

Now provide the test case necessary to get the output you want.

Regards
Michel

Re: Display all the months between two dates [message #348692 is a reply to message #348680] Wed, 17 September 2008 11:30 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
Hi all, here i am attaching the test case with the details. Please let me know if this is not clear

If type= 'MANAGER' then totl_sal/(start + 12 months)
If type = 'CONTRACTOR' then total_sal/(end_date - start_date)

For the above criteria, i want to display all the months with monthly sal.

Hope you all undertand it.


Thanks
Re: Display all the months between two dates [message #348838 is a reply to message #348692] Thu, 18 September 2008 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We have not "Time_Dim" table and don't know what "TEST_CASE_EXPLORE" is.
I assume "TEST_CASE" is "Employee" in your post.

Post a test case that tallies with your question.
Post data that fit with your output or post an output that fit with your data.

Regards
Michel

[Updated on: Thu, 18 September 2008 01:34]

Report message to a moderator

Re: Display all the months between two dates [message #348999 is a reply to message #348838] Thu, 18 September 2008 09:31 Go to previous messageGo to next message
justbegginer
Messages: 3
Registered: September 2008
Junior Member
Hi Michel, in the attached file TEST_CASE is my source table. Data in that table looks like in the attachment.
And TEST_CASE_EXPLORE table is my target table that i am retreiving data from my source(TEST_CASE) table.

Please find the attachemet that shows, how my source and target table looks like

Let me know if you did not understand my tables and my scenario

Re: Display all the months between two dates [message #349032 is a reply to message #348999] Thu, 18 September 2008 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think I understood what you mean, in the end. As I haven't your dim_tim table, I created my own calendar:
SQL> select * from test_case where emp_id in ('111','333') order by emp_id;
EMP_ID     EMP_TYPE        START_DATE END_DATE   TOT_SAL
---------- --------------- ---------- ---------- ----------
111        MANAGER         01/10/2006 04/10/2008 150000
333        CONTRACTOR      12/15/2006 12/15/2007 2500000

2 rows selected.

SQL> with
  2    base as (
  3      select min(trunc(start_date,'month')) first_month,
  4             max(trunc(end_date,'month')) last_month
  5      from test_case
  6    ),
  7    calendar as (
  8      select add_months(first_month,level-1) month
  9      from base
 10      connect by level <= months_between(last_month,first_month)+1
 11    )
 12  select emp_id, emp_type, 
 13         extract(year from c.month) year, extract(month from c.month) month, 
 14         decode(emp_type,
 15                'MANAGER', tot_sal/12,
 16                'CONTRACTOR', tot_sal/months_between(end_date,start_date)
 17               ) monthly_sal
 18  from test_case partition by (emp_id) left outer join calendar c 
 19       on (c.month between trunc(start_date,'month') and nvl(end_date,sysdate))
 20  where emp_id in ('111','333')
 21  order by 1, 3, 4
 22  /
EMP_ID     EMP_TYPE              YEAR      MONTH MONTHLY_SAL
---------- --------------- ---------- ---------- -----------
111        MANAGER               2006          1       12500
111        MANAGER               2006          2       12500
111        MANAGER               2006          3       12500
111        MANAGER               2006          4       12500
111        MANAGER               2006          5       12500
111        MANAGER               2006          6       12500
111        MANAGER               2006          7       12500
111        MANAGER               2006          8       12500
111        MANAGER               2006          9       12500
111        MANAGER               2006         10       12500
111        MANAGER               2006         11       12500
111        MANAGER               2006         12       12500
111        MANAGER               2007          1       12500
111        MANAGER               2007          2       12500
111        MANAGER               2007          3       12500
111        MANAGER               2007          4       12500
111        MANAGER               2007          5       12500
111        MANAGER               2007          6       12500
111        MANAGER               2007          7       12500
111        MANAGER               2007          8       12500
111        MANAGER               2007          9       12500
111        MANAGER               2007         10       12500
111        MANAGER               2007         11       12500
111        MANAGER               2007         12       12500
111        MANAGER               2008          1       12500
111        MANAGER               2008          2       12500
111        MANAGER               2008          3       12500
111        MANAGER               2008          4       12500
333        CONTRACTOR            2006         12  208333.333
333        CONTRACTOR            2007          1  208333.333
333        CONTRACTOR            2007          2  208333.333
333        CONTRACTOR            2007          3  208333.333
333        CONTRACTOR            2007          4  208333.333
333        CONTRACTOR            2007          5  208333.333
333        CONTRACTOR            2007          6  208333.333
333        CONTRACTOR            2007          7  208333.333
333        CONTRACTOR            2007          8  208333.333
333        CONTRACTOR            2007          9  208333.333
333        CONTRACTOR            2007         10  208333.333
333        CONTRACTOR            2007         11  208333.333
333        CONTRACTOR            2007         12  208333.333

41 rows selected.

Regards
Michel
Re: Display all the months between two dates [message #349102 is a reply to message #349032] Thu, 18 September 2008 22:01 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
THanks for the reply Michel! you understand me correct.I am looking for same query. But one thing did not understand is can we do like this?

from test_case partition by (emp_id) left outer join calendar c
on (c.month between trunc(start_date,'month') and nvl(end_date,sysdate)

Here we are making a join with employee id with number of months

And also can we extract like this?

extract(year from c.month) year, extract(month from c.month) month,


THanks a lot Michel
Re: Display all the months between two dates [message #349104 is a reply to message #349102] Thu, 18 September 2008 22:16 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@texan,

texan wrote on Fri, 19 September 2008 08:31

from test_case partition by (emp_id) left outer join calendar c
on (c.month between trunc(start_date,'month') and nvl(end_date,sysdate)

Here we are making a join with employee id with number of months



The join used by @Michel is a Non-equi join . Following link explains about non-equi join.
http://www.orafaq.com/wiki/Nonequi_join
And the join is not between empid and number of months. Below is your join condition:
 on (c.month between trunc(start_date,'month') and nvl(end_date,sysdate))



texan wrote on Fri, 19 September 2008 08:31

And also can we extract like this?

extract(year from c.month) year, extract(month from c.month) month,


extract function extracts a value from a date or interval value. It applies to Oracle Versions 9i and above. The following link might be useful about Extract function.

http://www.techonthenet.com/oracle/functions/extract.php

Hope these helps,
Regards,
Jo

[Updated on: Thu, 18 September 2008 23:35]

Report message to a moderator

Re: Display all the months between two dates [message #349271 is a reply to message #349104] Fri, 19 September 2008 10:19 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
Thanks John. I understand it clearly now.

Thanks Michel and John.

Re: Display all the months between two dates [message #349296 is a reply to message #349104] Fri, 19 September 2008 13:54 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
with
base as (
select min(trunc(start_date,'month')) first_month,
max(trunc(end_date,'month')) last_month
from test_case
),
calendar as (
select add_months(first_month,level-1) month
from base
connect by level <= months_between(last_month,first_month)+1
)
select emp_id, emp_type,
extract(year from c.month) year, extract(month from c.month) month,
decode(emp_type,
'MANAGER', tot_sal/12,
'CONTRACTOR', tot_sal/months_between(end_date,start_date)
) monthly_sal
from test_case partition by (emp_id) left outer join calendar c
on (c.month between trunc(start_date,'month') and nvl(end_date,sysdate))
where emp_id in ('111','333')
order by 1, 3, 4


This query is working good, but only thing i have problem with this query is,
the output is same for both employee types. I want to see output only 12 months if the empoyee type = 'MANAGER' evethough months_between(start_date,end_date) > 12 or < 12.
But when i execute above query it shows more than 12 records when the employee_type = 'MANAGER'

I will try doing union, but if you guys have better one, please let me know.

Thanks
Re: Display all the months between two dates [message #349297 is a reply to message #349296] Fri, 19 September 2008 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now you know the rules:
- post a test case
- post the result for this test case
- format the post

Also you can try to just modify the query for this, it is not so difficult. You are an employee of a consulting entreprise, I think you are paid for this work, so at least post what you tried.

Regards
Michel

[Updated on: Fri, 19 September 2008 14:03]

Report message to a moderator

Re: Display all the months between two dates [message #349694 is a reply to message #349297] Mon, 22 September 2008 13:54 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
Michel, here is the test case and the way i tried

CREATE TABLE TEST_CASE
(
EMP_ID VARCHAR2(255 BYTE),
EMP_TYPE VARCHAR2(255 BYTE),
START_DATE DATE,
END_DATE DATE,
TOT_SAL VARCHAR2(255 BYTE)
)

Insert into TEST_CASE
(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
Values
('111', 'MANAGER', TO_DATE('01/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/10/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'150000');

Insert into TEST_CASE
(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
Values
('222', 'MANAGER', TO_DATE('02/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'200000');

Insert into TEST_CASE
(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
Values
('333', 'CONTRACTOR', TO_DATE('12/15/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'2500000');

COMMIT;



Here is my query i modified



with
base as (
select trunc(start_date,'month') first_month,
trunc(end_date,'month') last_month
from test_case
),
calendar as (
select distinct add_months(first_month,level) month
from base
connect by level <= months_between(add_months(first_month,12),first_month)
)
select distinct emp_id, emp_type,
extract(year from c.month) year, extract(month from c.month) month,
decode(emp_type,'MANAGER', tot_sal/12,
'CONTRACTOR', tot_sal/months_between(end_date,start_date)
) monthly_sal
from test_case partition by (emp_id) left outer join calendar c
on (c.month between trunc(start_date,'month') and add_months(start_Date,12))
order by 1,2,3,4


This query is doing well for 3 records and the output i got, the exactly i am looking for.


EMP_ID EMP_TYPE YEAR MONTH MONTHLY_SAL
111 MANAGER 2006 1 12500
111 MANAGER 2006 2 12500
111 MANAGER 2006 3 12500
111 MANAGER 2006 4 12500
111 MANAGER 2006 5 12500
111 MANAGER 2006 6 12500
111 MANAGER 2006 7 12500
111 MANAGER 2006 8 12500
111 MANAGER 2006 9 12500
111 MANAGER 2006 10 12500
111 MANAGER 2006 11 12500
111 MANAGER 2006 12 12500
111 MANAGER 2007 1 12500
222 MANAGER 2005 3 16666.66667
222 MANAGER 2005 4 16666.66667
222 MANAGER 2005 5 16666.66667
222 MANAGER 2005 6 16666.66667
222 MANAGER 2005 7 16666.66667
222 MANAGER 2005 8 16666.66667
222 MANAGER 2005 9 16666.66667
222 MANAGER 2005 10 16666.66667
222 MANAGER 2005 11 16666.66667
222 MANAGER 2005 12 16666.66667
222 MANAGER 2006 1 16666.66667
222 MANAGER 2006 2 16666.66667
333 CONTRACTOR 2006 12 208333.3333
333 CONTRACTOR 2007 1 208333.3333
333 CONTRACTOR 2007 2 208333.3333
333 CONTRACTOR 2007 3 208333.3333
333 CONTRACTOR 2007 4 208333.3333
333 CONTRACTOR 2007 5 208333.3333
333 CONTRACTOR 2007 6 208333.3333
333 CONTRACTOR 2007 7 208333.3333
333 CONTRACTOR 2007 8 208333.3333
333 CONTRACTOR 2007 9 208333.3333
333 CONTRACTOR 2007 10 208333.3333
333 CONTRACTOR 2007 11 208333.3333
333 CONTRACTOR 2007 12 208333.3333


But my employee table has 500 recods and it the query is taking too long to execute. I do have index defined on the table on emp_id. Does that help to run query faster. Can i define index on start_date field and use hints to improve the query performace?




Re: Display all the months between two dates [message #349696 is a reply to message #349694] Mon, 22 September 2008 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide.

Regards
Michel
Re: Display all the months between two dates [message #349712 is a reply to message #349696] Mon, 22 September 2008 16:45 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
I followed the rules. What else you want me to follow?
Re: Display all the months between two dates [message #349751 is a reply to message #349712] Tue, 23 September 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
texan wrote on Mon, 22 September 2008 23:45
I followed the rules. What else you want me to follow?

Don't you see any difference between the way I posted and the way you posted?
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.

Regards
Michel
Re: Display all the months between two dates [message #349925 is a reply to message #349751] Tue, 23 September 2008 09:00 Go to previous message
texan
Messages: 14
Registered: September 2008
Junior Member
Ignore it man...i tried the same way you posted last time.
Previous Topic: Trigger Information
Next Topic: Password encryption with high security
Goto Forum:
  


Current Time: Sun Dec 04 15:03:51 CST 2016

Total time taken to generate the page: 0.10166 seconds