November 2, 2011 at 1:41 pm
I have 2 tables that contain metrics for a performance review.
1 Table is the Master Review Table and the other is a table that contains Peer Reviews.
CREATE TABLE [dbo].[MasterReview](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PerformanceID] [int] NOT NULL,
[SkillsGroupID] [int] NOT NULL,
[SkillsHeadingID] [int] NOT NULL,
[SkillsMetricID] [int] NOT NULL,
[Rating] [tinyint] NULL
)
CREATE TABLE [dbo].[PeerReview](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MasterReviewID] [int] NOT NULL,
[PerformanceID] [int] NOT NULL,
[PeerEmployeeID] [int] NOT NULL,
[SkillsGroupID] [int] NOT NULL,
[SkillsHeadingID] [int] NOT NULL,
[SkillsMetricID] [int] NOT NULL,
[Rating] [tinyint] NULL
)
INSERT INTO [dbo].[MasterReview]
([PerformanceID],
[SkillsGroupID],
[SkillsHeadingID],
[SkillsMetricID],
[Rating])
VALUES
(10, 2, 5, 10, null),
(10, 2, 5, 11, null),
(10, 2, 5, 12, null),
(20, 2, 5, 10, null),
(20, 2, 5, 11, null),
(20, 2, 5, 12, null)
When a user creates a peer review I want the data from the master review to be inserted into the peer review table. I am using the following which works fine for inserting for the first time. Note: I pass the PerofrmanceID and PeerEmployeeID into the stored proc as these are known items from the front end of the system (10 and 100 in this case).
INSERT INTO PeerReview
("MasterReviewID",
"PerformanceID",
"PeerEmployeeID",
"SkillsGroupID",
"SkillsHeadingID",
"SkillsMetricID",
"Rating")
SELECT
MR.ID,
10,
100,
MR.SkillsGroupID,
MR.SkillsHeadingID,
MR.SkillsMetricID,
null
FROM
MasterReview MR WHERE MR.PerformanceID = 10
The problem is that if the MasterReview has a metric added or deleted, I want to reflect the changes in the Peer Review table when the user clicks the create peer review button on the front end.
If an item is added to the MasterReview, the insert statment will compare the Master and Peer tables and insert anything missing in the PeerReview from the MasterReview.
Just the opposite for a delete. If a row exists in the peer table that doesn't exist in the Master, delete it from the peer.
I can't just overwrite the PeerReview items on an insert because there may be existing Rating data that I don't want to lose.
How can I compare the Master and Peer tables and update the peer table accordingly?
Thanks in advance.
November 2, 2011 at 3:13 pm
For the delete, make MasterReviewID on PeerReview reference MasterReview with a cascading delete.
But for the insert, I don't quite get it...what values will you use in the PeerReview when inserting a new MasterReview? Do you want one for each PeerEmployeeID in PeerReview?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
November 2, 2011 at 5:32 pm
Hi toddasd,
Thanks for the response.
-"For the delete, make MasterReviewID on PeerReview reference MasterReview with a cascading delete."
Can you please provide an example?
-"But for the insert, I don't quite get it...what values will you use in the PeerReview when inserting a new MasterReview? Do you want one for each PeerEmployeeID in PeerReview?"
Nothing is inserted into the PeerReview table when a MasterReview is created. The Peer Reviews are optional.
My application allows a manager to create a MasterReview with various metrics. When the performance review is saved the items are saved into the MasterReview table. No items are inserted in the PeerReview table at this point.
I have another screen that allows the manager to create peer reviews based off the MasterReview. This screen allows the manager to select which employees are to provide a PeerReview. When the button event is fired to create a PeerReview that is when the data is inserted into the PeerReview table. So there doesn't have to be a PeerReview associated with a MasterReview. The MasterReview can be standalone.
What I am trying to accomplish is the ability for multiple people to provide a review on a certain employee.
Does this make sense?
Thanks again.
November 4, 2011 at 7:12 am
bubs (11/2/2011)
-"For the delete, make MasterReviewID on PeerReview reference MasterReview with a cascading delete."Can you please provide an example?
Look up Triggers in SQL Books Online. That's what he means.
-"But for the insert, I don't quite get it...what values will you use in the PeerReview when inserting a new MasterReview? Do you want one for each PeerEmployeeID in PeerReview?"
Nothing is inserted into the PeerReview table when a MasterReview is created. The Peer Reviews are optional.
...
I have another screen that allows the manager to create peer reviews based off the MasterReview. This screen allows the manager to select which employees are to provide a PeerReview. When the button event is fired to create a PeerReview that is when the data is inserted into the PeerReview table. So there doesn't have to be a PeerReview associated with a MasterReview. The MasterReview can be standalone.
But earlier you said:
The problem is that if the MasterReview has a metric added or deleted, I want to reflect the changes in the Peer Review table when the user clicks the create peer review button on the front end.
If an item is added to the MasterReview, the insert statment will compare the Master and Peer tables and insert anything missing in the PeerReview from the MasterReview.
This is what we're trying to understand. What do you mean "insert anything missing into the PeerReview"? How will that work?
November 4, 2011 at 7:49 am
bubs, look at how i changed your table definitions below.
/*
drop table PeerReview
drop table MasterReview
*/
CREATE TABLE [dbo].[MasterReview](
[ID] [int] IDENTITY(1,1) NOT NULL primary key,
[PerformanceID] [int] NOT NULL,
[SkillsGroupID] [int] NOT NULL,
[SkillsHeadingID] [int] NOT NULL,
[SkillsMetricID] [int] NOT NULL,
[Rating] [tinyint] NULL
)
CREATE TABLE [dbo].[PeerReview](
[ID] [int] IDENTITY(1,1) NOT NULL primary key,
[MasterReviewID] [int] NOT NULL references MasterReview([ID]) on delete cascade,
[PerformanceID] [int] NOT NULL,
[PeerEmployeeID] [int] NOT NULL,
[SkillsGroupID] [int] NOT NULL,
[SkillsHeadingID] [int] NOT NULL,
[SkillsMetricID] [int] NOT NULL,
[Rating] [tinyint] NULL
)
INSERT INTO [dbo].[MasterReview]
([PerformanceID],[SkillsGroupID],[SkillsHeadingID],[SkillsMetricID],[Rating])
VALUES
(10, 2, 5, 10, null),(10, 2, 5, 11, null),(10, 2, 5, 12, null),(20, 2, 5, 10, null),(20, 2, 5, 11, null),(20, 2, 5, 12, null)
INSERT INTO PeerReview
(MasterReviewID,PerformanceID,PeerEmployeeID,SkillsGroupID,SkillsHeadingID,SkillsMetricID,Rating)
SELECT
MR.ID,10,100,MR.SkillsGroupID, MR.SkillsHeadingID, MR.SkillsMetricID, null
FROM
MasterReview MR
WHERE
MR.PerformanceID = 10
select * from MasterReview
select * from PeerReview
delete from MasterReview where ID=1
Notice how the delete from MasterReview also deletes related records in PeerReview.
I still don't understand what you want to do on a delete, but I'm suspecting that Brandie is correct in that you'll need a trigger.
Edit: spelling
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
November 4, 2011 at 8:20 am
bubs (11/2/2011)
The problem is that if the MasterReview has a metric added or deleted, I want to reflect the changes in the Peer Review table when the user clicks the create peer review button on the front end.
I'm taking a stab here...are you talking about adding a new column to the MasterReview table when adding a new metric? And then having that new column show up in PeerReview?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
November 4, 2011 at 2:02 pm
Sorry, I am doing a really poor job of explaining the functionality.
Let me take another stab at it.
A Manager creates a performance review for an employee, this is the master review. A peer review does not exist at this point and does not need to exist, it's completely optional.
If a Manager does wish to create a peer review there is functionality that allows them to do so. It's at this point that a peer review is created based off the master review. So now we will have a master review and a peer review that have the same metrics.
Now if the manager later decides that they want to add or remove a metric from the master review, I want the peer review updated as well (if it exists). So if a metric is added to the master it will be added to the peer, if a metric is deleted from the master, it will be deleted from the peer.
I haven't tried it yet, but I think the cascade delete looks like it will work when a metric is removed from the master. I should have a chance to try it today.
If a manager decides to add a metric to the master review, what is the best way to update the peer review as well. The master and peer reviews need to be the same.
My original thought was to query both the MasterReview and PeerReview tables, then compare the table results through a looping mechanism. I would then know the differences between the 2 tables and update the peer accordingly. But there must be a much more elegant way of performing this task rather than lopping though the query results.
Does this make more sense?
Thanks for all the help so far, it is very much appreciated.
November 4, 2011 at 2:23 pm
Sounds like you need the cascading delete and an insert trigger on the master. In your trigger just insert into the peer review table the new metric. No need to do any looping at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 5:26 am
Sean Lange (11/4/2011)
Sounds like you need the cascading delete and an insert trigger on the master. In your trigger just insert into the peer review table the new metric. No need to do any looping at all.
What Sean said... but why do you want to exist the metric in existing peer reviews?
I ask because just inserting it won't help you unless you're making it a calculated column based off existing values. In most environments, once done, a review is done. The individual reviews don't get updated. New reviews get added. So I fail to see the HR value in adding defaulted metrics to existing reviews.
Note that this is my opinion and you are free to ignore it. But still, how does having a defaulted new metric on old data assist anyone?
November 7, 2011 at 7:38 am
Brandie Tarvin (11/7/2011)
Sean Lange (11/4/2011)
Sounds like you need the cascading delete and an insert trigger on the master. In your trigger just insert into the peer review table the new metric. No need to do any looping at all.What Sean said... but why do you want to exist the metric in existing peer reviews?
I ask because just inserting it won't help you unless you're making it a calculated column based off existing values. In most environments, once done, a review is done. The individual reviews don't get updated. New reviews get added. So I fail to see the HR value in adding defaulted metrics to existing reviews.
Note that this is my opinion and you are free to ignore it. But still, how does having a defaulted new metric on old data assist anyone?
My guess is the front needs the record in the peer review (even if there is no response) so it can correctly build the UI. Seems to me that the business rule is flawed here. It indicates that an admin can modify the review questions after it has been completed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2011 at 6:16 am
Sean Lange (11/7/2011)
Brandie Tarvin (11/7/2011)
Sean Lange (11/4/2011)
Sounds like you need the cascading delete and an insert trigger on the master. In your trigger just insert into the peer review table the new metric. No need to do any looping at all.What Sean said... but why do you want to exist the metric in existing peer reviews?
I ask because just inserting it won't help you unless you're making it a calculated column based off existing values. In most environments, once done, a review is done. The individual reviews don't get updated. New reviews get added. So I fail to see the HR value in adding defaulted metrics to existing reviews.
Note that this is my opinion and you are free to ignore it. But still, how does having a defaulted new metric on old data assist anyone?
My guess is the front needs the record in the peer review (even if there is no response) so it can correctly build the UI. Seems to me that the business rule is flawed here. It indicates that an admin can modify the review questions after it has been completed.
In such a case, the UI should be built off the master record, not the individual peer review record. Then, when the individual record is loaded, the UI should take into account that not all values will be filled and just dummy a NULL or DEFAULT value for the master record field (Note: using the term field because I'm talking about the UI, not a table).
November 8, 2011 at 7:41 am
Brandie Tarvin (11/8/2011)
Sean Lange (11/7/2011)
Brandie Tarvin (11/7/2011)
Sean Lange (11/4/2011)
Sounds like you need the cascading delete and an insert trigger on the master. In your trigger just insert into the peer review table the new metric. No need to do any looping at all.What Sean said... but why do you want to exist the metric in existing peer reviews?
I ask because just inserting it won't help you unless you're making it a calculated column based off existing values. In most environments, once done, a review is done. The individual reviews don't get updated. New reviews get added. So I fail to see the HR value in adding defaulted metrics to existing reviews.
Note that this is my opinion and you are free to ignore it. But still, how does having a defaulted new metric on old data assist anyone?
My guess is the front needs the record in the peer review (even if there is no response) so it can correctly build the UI. Seems to me that the business rule is flawed here. It indicates that an admin can modify the review questions after it has been completed.
In such a case, the UI should be built off the master record, not the individual peer review record. Then, when the individual record is loaded, the UI should take into account that not all values will be filled and just dummy a NULL or DEFAULT value for the master record field (Note: using the term field because I'm talking about the UI, not a table).
Agree completely but not every system out there is designed well. Might be as simple as changing from an inner to a left join to fix it, but who knows? 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply