Need help with writing Querie..

Database

Need help with writing Querie..

Hi,

I have table Table_A:

Date Employee_Name Salary
2005.06.01 John 10
2005.06.02 John 10
2005.06.03 John 10
2005.06.04 John 20
2005.06.06 John 10
2005.06.07 John 10
2005.06.02 Peter 50
2005.06.03 Peter 50

and I want to get data about employee's salary - how it is changing by periods. For example, I want result like this:

Start_Date End_date Name Salary
2005.06.01 2005.06.03 John 10
2005.06.04 2005.06.04 John 20
2005.06.06 2005.06.07 John 10
2005.06.02 2005.06.03 Peter 50

It would be easy to get min/max dates about Jonh where salary is 10 (or other), but if in the middle of this period (2005.06.01-2005.06.07) John had different salary, this period must be divided into several periods. I think I need RANK() OVER(PARTITION BY... but still don't know how to write right query.

Thanks in advance,
Trubert
2 REPLIES
Enthusiast

Re: Need help with writing Querie..



Hi Trubert,

with the above dates we will not be able to answer the question " what was john's salary for 2005.06.05?". Since this date is not covered in any row. Do you want the data to be
Start_Date End_date Name Salary

2005.06.01 2005.06.03 John 10

2005.06.04 John 20

2005.06.06 2005.06.07 John 10

2005.06.02 2005.06.03 Peter 50

cheers,
Novice
Enthusiast

Re: Need help with writing Querie..

hi,

I created a volatile table 'emp' and inserted the data u mentioned.Then i created another table as below

create volatile table emp2 as
(sel a.* ,csum(1,1,a.emp,a.dt) as row_num from emp a)with data on commit preserve rows;

then the following query gives the required result:

SEL dt as start_dt,emp as employee_name,sal as salary FROM emp2 qualify row_number() over (partition by emp order by dt asc)=1
UNION
SEL a.dt as start_dt,emp,sal FROM emp2 A WHERE EXISTS
(SEL 'V' FROM emp2 B WHERE A.emp=B.emp AND A.row_num=B.row_num+1 AND NOT(A.sal=B.sal))

hope this is helpful to you

Cheers:-)