October 3, 2016 at 4:27 am
i have table for each month which stores the employee's salary, i need SP which takes the input has empno and month with year has input, which should display the salary.
October 3, 2016 at 5:38 am
GA_SQL (10/3/2016)
i have table for each month which stores the employee's salary, i need SP which takes the input has empno and month with year has input, which should display the salary.
A separate table for every month? I think you should consider applying some normalisation before we move forward with a dynamic-SQL proc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 3, 2016 at 6:20 am
Ok,
October 3, 2016 at 6:26 am
GA_SQL (10/3/2016)
Ok,
..Such that there is a single table containing salaries, with appropriate effective dates.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 3, 2016 at 6:29 am
But for each month i have 30 employees, how to maintain that in a single table
October 3, 2016 at 6:47 am
GA_SQL (10/3/2016)
But for each month i have 30 employees, how to maintain that in a single table
What's the problem? You could have 30 million employees and their salaries for several decades. That can be done easily in a single table.
In a simplistic way of designing it, you just need: Date, EmployeeNo, Salary.
October 3, 2016 at 7:50 am
GA_SQL (10/3/2016)
But for each month i have 30 employees, how to maintain that in a single table
First I'd just like to mention that vertically partitioning across multiple tables, when there are only 30 rows in each table, this has no practical value. If refactoring the data model isn't an option at this time, then consider creating a paritioned view to logically unionize all employee salaries into one logical table.
For example:
create view vEmployeeSalary as
select * from EMPSAL201601
union all
select * from EMPSAL201602
union all
select * from EMPSAL201603
... etc
... etc
GO
Once done, it at least looks like one normalized table and you can query using straightforward SQL.
For example:
select * from vEmployeeSalary where EmployeeID = 999;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply