|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need advice with a queryI 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 News,
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 Show quote "News" <zoom191***@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 -- 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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@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 More important to add a constraint on the relevant business key. Here> identity? emp_salary_id... 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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1128012586.541851.238920@z14g2000cwz.googlegroups.com... >> 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 > -- > another option would be to have effective_from and effective_to columns
in the table I am trying to stay away from this, because I would need to maintain
effective_to and update it along with addition of a new record. Spasibo, Sasha Gena Show quote "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1128015747.429032.246440@g43g2000cwa.googlegroups.com... > another option would be to have effective_from and effective_to columns > in the table > Gena,
one more approach: SELECT employee_id, salary FROM tEmployeeSalary ES1 WHERE ES1.employee_id = 1 AND ES2.effective_date <= '07/01/2004' and not exists(SELECT 1 FROM tEmployeeSalary ES2 WHERE ES2.employee_id = ES1.employee_id AND ES1.effective_date < ES2.effective_date AND ES2.effective_date <= '07/01/2004') not sure which one would perform better Udachi! Now, I've got 3 statements to choose from:
1. SELECT TOP 1 salary, effective_date FROM EmployeeSalary WHERE employee_id = 1 AND DateDiff(dd, effective_date, 'aug 1, 2005') >=0 ORDER BY effective_date DESC 2. SELECT salary, effective_date FROM EmployeeSalary AS E WHERE employee_id = 1 AND effective_date = (SELECT MAX(effective_date) FROM EmployeeSalary WHERE employee_id = E.employee_id AND DateDiff(dd, effective_date, 'aug 1, 2005') >= 0) 3. SELECT salary, effective_date FROM EmployeeSalary ES1 WHERE ES1.employee_id = 1 AND ES1.effective_date <= 'aug 1, 2005' AND NOT exists(SELECT 1 FROM EmployeeSalary ES2 WHERE ES2.employee_id = ES1.employee_id AND ES1.effective_date < ES2.effective_date AND ES2.effective_date <= 'aug 1, 2005') I checked Statistics in Query Analyzer and it seems overall statement #2 looks the best. Although, in some categories it looses. What is the most important to look at when checking stats and trace? I imagine Reads are very important in Trace. But, there are also Application Profile Statistics Number of SELECT statements Rows effected by SELECT statements Number of user transactions Network Statistics Number of server roundtrips Number of TDS packets sent Number of TDS packets received Number of bytes sent Number of bytes received Time Statistics Cumulative client processing time Cumulative wait time on server replies Thanks, Gena Show quote "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1128022980.883781.112920@o13g2000cwo.googlegroups.com... > Gena, > > one more approach: > > SELECT employee_id, salary > FROM tEmployeeSalary ES1 > WHERE ES1.employee_id = 1 > AND ES2.effective_date <= '07/01/2004' > and not exists(SELECT 1 > FROM tEmployeeSalary ES2 > WHERE ES2.employee_id = ES1.employee_id > AND ES1.effective_date < ES2.effective_date > AND ES2.effective_date <= '07/01/2004') > > not sure which one would perform better > > Udachi! > 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 -- Thanks a lot,
I added created date and made it part of PK (employee_id, effective_date, trx_date). Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1128016367.702743.260980@g47g2000cwa.googlegroups.com... > 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: Show quote > 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_dpor***@acm.org> wrote in message > news:1128012586.541851.238920@z14g2000cwz.googlegroups.com... > >> 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 > > -- > > Yes, you are right,
I am still in the process of deciding. I added date of transaction as a datetime to the table and this will be sort factor when employee_id and effective_date are the same. Although, effective_date is also datetime data type, so it carries timestamp. Show quote "Payson" <payso***@hotmail.com> wrote in message news:1128016717.161132.122570@g49g2000cwa.googlegroups.com... > 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 >> 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_dpor***@acm.org> wrote in message >> news:1128012586.541851.238920@z14g2000cwz.googlegroups.com... >> >> 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 >> > -- >> > > 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 Show quote "News" <zoom191***@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 >  |
|||||||||||||||||||||||
Other interesting topics