Home » SQL & PL/SQL » SQL & PL/SQL » SQL query (oracle)
SQL query [message #333254] Fri, 11 July 2008 00:55 Go to next message
svikash
Messages: 3
Registered: July 2008
Junior Member
Dear All,

I have a problem in retrieving the right salary on a monthly basis for an employee. Consider the example below:

Table t1 consist of the following rows:
Employee# date_from date_to Amount
e1 01/01/2008 29/02/2008 1000
e1 01/03/2008 2500
e2 01/01/2008 31/03/2008 1500
e2 01/04/2008 31/05/2008 1600
e2 01/06/2008 2200

Employee e1 got a salary of 1000 from 01/01/2008 to 29/02/2008, then the salary of 2500 is active as from 01/03/2008. Same scenario for e2 whose salary is 2200 since 01/06/2008.

Now I want to extract data for each employee and their respective salary from January 2008 to June 2008, that is, the format of the report should be as follows:

Employee# Month Salary_Amount
e1 Jan2008 1000
e1 Feb2008 1000
e1 Mar2008 2500
e1 Jun2008 2500
e2 jan2008 1500
e2 feb2008 1500
e2 mar2008 1500
...
e2 jun2008 2200


I did use a group by but it does not work, but it seem that I have to write a procedure. Can anyone help?

Thanks
Re: SQL query [message #333264 is a reply to message #333254] Fri, 11 July 2008 01:25 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to:
1/ create a "calendar" view (search for this word)
2/ join this view with your table using "between"

Next time please follow 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.

Post a test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: running multiple packages in database
Next Topic: Dynamic Insert Statement
Goto Forum:
  


Current Time: Fri Dec 02 12:08:40 CST 2016

Total time taken to generate the page: 0.13831 seconds