Need advice with a query

Hi,
I need an advice.
I have a new project where I have to store employee salary values in order
to keep historical representation of all changes done to the employee
record. Below is the approximate model how I am going to proceed.
My question is, how would I pull ee salary at certain date, say "what was
employee salary on July 1st, 2005".
I know I can do something like that:
SELECT TOP 1 salary FROM tEmployeeSalary
WHERE employee_id = 1
AND DateDiff(dd, 'July 1, 2005', effective_date) >= 0
ORDER BY effective_date ASC
But I would need to use it in joins with other tables. So, is it possible to
write a query to get to the required record without ORDERing and using TOP?
begin tran
create table tEmployeeSalary
(
employee_id int,
salary money,
effective_date datetime,
is_active bit
)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 45000, 'dec 12, 2004', 1)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 49000, 'mar 1, 2005', 1)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 54000, 'june 20, 2005', 1)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 67000, 'sep 10, 2005', 1)
select * from tEmployeeSalary
drop table tEmployeeSalary
commit tran
Thank you in advance for all suggestions.
GenaNews,
Assuming salarys are continually increasing and never decrease...maybe this
would work:
SELECT MAX(SALARY)
FROM TEMPLOYEESALARY
WHERE EMPLOYEE_ID = 1 AND EFFECTIVE_DATE <= '7/1/2005'
HTH
Jerry
"News" <zoom191919@.yahoo.com> wrote in message
news:S8Cdna9sLsQCnqHeRVn-iA@.magma.ca...
> Hi,
> I need an advice.
> I have a new project where I have to store employee salary values in order
> to keep historical representation of all changes done to the employee
> record. Below is the approximate model how I am going to proceed.
> My question is, how would I pull ee salary at certain date, say "what was
> employee salary on July 1st, 2005".
> I know I can do something like that:
> SELECT TOP 1 salary FROM tEmployeeSalary
> WHERE employee_id = 1
> AND DateDiff(dd, 'July 1, 2005', effective_date) >= 0
> ORDER BY effective_date ASC
> But I would need to use it in joins with other tables. So, is it possible
> to write a query to get to the required record without ORDERing and using
> TOP?
>
> begin tran
> create table tEmployeeSalary
> (
> employee_id int,
> salary money,
> effective_date datetime,
> is_active bit
> )
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 45000, 'dec 12, 2004', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 49000, 'mar 1, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 54000, 'june 20, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 67000, 'sep 10, 2005', 1)
> select * from tEmployeeSalary
> drop table tEmployeeSalary
> commit tran
>
> Thank you in advance for all suggestions.
> Gena
>|||You don't really have every column nullable do you? What are the keys?
Thanks for the DDL and sample data but it would be more helpful if it
was accurate and complete. Assuming that (employee_id, effective_date)
is unique, try this:
SELECT employee_id, salary
FROM tEmployeeSalary AS E
WHERE effective_date =
(SELECT MAX(effective_date)
FROM tEmployeeSalary
WHERE employee_id = E.employee_id
AND effective_date <= '20050701') ;
Be careful with the MONEY datatype. Precision is lost when you multiply
and divide MONEY. In my opinion MONEY is unsuitable for financial data.
(yes, seriously!)
David Portas
SQL Server MVP
--|||You can try to use subquery:
SELECT employee_id, salary
FROM tEmployeeSalary ES1
WHERE ES1.employee_id = 1
AND ES1.effective_date = (SELECT Max(ES2.effective_date )
FROM tEmployeeSalary ES2
WHERE ES2.employee_id =
ES1.employee_id
AND ES2.effective_date <=
'07/01/2004')
Perayu
"News" <zoom191919@.yahoo.com> wrote in message
news:S8Cdna9sLsQCnqHeRVn-iA@.magma.ca...
> Hi,
> I need an advice.
> I have a new project where I have to store employee salary values in order
> to keep historical representation of all changes done to the employee
> record. Below is the approximate model how I am going to proceed.
> My question is, how would I pull ee salary at certain date, say "what was
> employee salary on July 1st, 2005".
> I know I can do something like that:
> SELECT TOP 1 salary FROM tEmployeeSalary
> WHERE employee_id = 1
> AND DateDiff(dd, 'July 1, 2005', effective_date) >= 0
> ORDER BY effective_date ASC
> But I would need to use it in joins with other tables. So, is it possible
> to write a query to get to the required record without ORDERing and using
> TOP?
>
> begin tran
> create table tEmployeeSalary
> (
> employee_id int,
> salary money,
> effective_date datetime,
> is_active bit
> )
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 45000, 'dec 12, 2004', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 49000, 'mar 1, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 54000, 'june 20, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 67000, 'sep 10, 2005', 1)
> select * from tEmployeeSalary
> drop table tEmployeeSalary
> commit tran
>
> Thank you in advance for all suggestions.
> Gena
>|||Thanks David,
The data in my table does not allow nulls, although employee_id id is not
unique and the effective_date can repeat in cases when mistake was made and
I can allow to change salary on the same date or earlier (in this case
is_active will be set to 0).
And, thanks for "money" advice, good point.
I guess, I cannot get away from "TOP - ORDER" or subquery. I think I will
write a UDF and call it anytime I need a salary at a time point.
Another question, would it be a good idea to add a field - primary key
identity? emp_salary_id...
Thanks,
Gena
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128008402.193130.131230@.g14g2000cwa.googlegroups.com...
> You don't really have every column nullable do you? What are the keys?
> Thanks for the DDL and sample data but it would be more helpful if it
> was accurate and complete. Assuming that (employee_id, effective_date)
> is unique, try this:
> SELECT employee_id, salary
> FROM tEmployeeSalary AS E
> WHERE effective_date =
> (SELECT MAX(effective_date)
> FROM tEmployeeSalary
> WHERE employee_id = E.employee_id
> AND effective_date <= '20050701') ;
> Be careful with the MONEY datatype. Precision is lost when you multiply
> and divide MONEY. In my opinion MONEY is unsuitable for financial data.
> (yes, seriously!)
> --
> David Portas
> SQL Server MVP
> --
>|||> Another question, would it be a good idea to add a field - primary key
> identity? emp_salary_id...
More important to add a constraint on the relevant business key. Here
I'm guessing:
ALTER TABLE tEmployeeSalary
ADD CONSTRAINT ak1_employee_salary
UNIQUE (employee_id, effective_date)
or:
ALTER TABLE tEmployeeSalary
ADD CONSTRAINT pk_employee_salary
PRIMARY KEY (employee_id, effective_date)
You can add the IDENTITY key if you need to reference it in another
table but otherwise it would be redundant. Every table should of course
have at least one candidate key.
David Portas
SQL Server MVP
--|||Hmm,
I cannot make PRIMARY KEY (employee_id, effective_date) because it will not
allow me to set different salaries for the same employee on the same date.
What if administrator made a mistake and noticed it few months later. I
cannot update it, I will need to add a new record with the same employee_id
and the same effective_date.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128012586.541851.238920@.z14g2000cwz.googlegroups.com...
> More important to add a constraint on the relevant business key. Here
> I'm guessing:
> ALTER TABLE tEmployeeSalary
> ADD CONSTRAINT ak1_employee_salary
> UNIQUE (employee_id, effective_date)
> or:
> ALTER TABLE tEmployeeSalary
> ADD CONSTRAINT pk_employee_salary
> PRIMARY KEY (employee_id, effective_date)
> You can add the IDENTITY key if you need to reference it in another
> table but otherwise it would be redundant. Every table should of course
> have at least one candidate key.
> --
> David Portas
> SQL Server MVP
> --
>|||another option would be to have effective_from and effective_to columns
in the table|||So add a "created_date" or "modified_date" to the key. It seems your
original specification was too much of a simplified example. Adding
duplicate effective dates without further information and just updating
"is_active" would mean you would lose the audit trail containing the
sequence of changes.
David Portas
SQL Server MVP
--|||If you can't define a primary key, then you are sunk. SQL has no
order. If you have 2 entries for the same employee with the same
effective date, which do you wish to select? The higher salary? The
lower? The one entered last? (sorry, that information is not
available). select top ... has no meaning without an order by -
duplicates can appear in any order SQL Server wants to show them.
Given the requirements I have seen so far, you can't get there from
here.
You have gotten quite a bit of good, professional advice from the
posters in this thread. You may want to rethink things a bit.
Good luck.
Payson
News wrote:
> Hmm,
> I cannot make PRIMARY KEY (employee_id, effective_date) because it will no
t
> allow me to set different salaries for the same employee on the same date.
> What if administrator made a mistake and noticed it few months later. I
> cannot update it, I will need to add a new record with the same employee_i
d
> and the same effective_date.
> Thanks
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128012586.541851.238920@.z14g2000cwz.googlegroups.com...