April 22, 2014 at 4:04 am
Hi,
Script attached which needs to be optimized.
Request background:
1) Add a new record with top priority
Related Requirement:
1) Modify the priority of current records by moving one down.
2) Do not modify the priority of current records if the EffDate and TermDate are different
My Question:
1) Is there a simple way to do it?
April 22, 2014 at 6:16 am
A concept where a new row requires an update of (almost) all current rows in a table is questionable in terms of the relational theory.
Taking this aside: what would you do, if someone by mistake update all rows and set MyPriority to Zero?
What would be the business rule to get the correct values for MyPriority?
What makes me wondering: The priority of a row is based on the values of EffDate and TermDate. But if one or the other values is changed, the priority will remain the same until a new row is inserted.
Can you please explain a little more detailed what business case requires such an "interesting" table design?
April 22, 2014 at 7:51 am
...interesting" table design?
That's a very diplomatic way to phrase that question. Very well done! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 22, 2014 at 10:18 am
if you wanted to force that business requirement to work, maybe you could insert the original request with a priority of say, 2,000,000,000 and with all future requests decrement that number by selecting the MIN(priority) - 1?
Hey, sometimes you just gotta do what they want.
April 22, 2014 at 10:41 am
lnardozi 61862 (4/22/2014)
if you wanted to force that business requirement to work, maybe you could insert the original request with a priority of say, 2,000,000,000 and with all future requests decrement that number by selecting the MIN(priority) - 1?Hey, sometimes you just gotta do what they want.
A simple decrementing model won't work since there's also a re-numbering depending on EffDate and TermDate values, as described in his example.
If decrementing would be all the OP is looking for, I'd either use a descending MyID or I'd change the query logic to look for the max(MyID) value.
For presentation purposes I'd use ROW_NUMBER approach, but that would require to know the business logic of how to calculate the priority - hence my related question.
Edit: I disagree with your last statement (to do what they want).
The business case needs to be covered. That's correct. But how to it do it is the job of the person responsible for the code (either DBA or Developer).
I'd strongly argue against anybody outside the "coding area" who's trying to force me into a specific data model. Especially, if it's against relational theory.
April 22, 2014 at 10:47 am
Yep, I understand what you're saying. Really, I don't think the business requirement was well thought out (who breaks ties) but I've tried to explain these things to stakeholders before and some of them simply will not be swayed. When a request of this nature comes around, it's best to document the decision process carefully and give exactly what is asked for, so that when it is revisited (and it will be) the requestor's insistence and the developer's objection are both noted.
April 22, 2014 at 8:08 pm
I do understand the concern here. But there is nothing wrong in inserting a new record with high priority as per business. The details I provided is an example. In actual, there are few other fields which contains decimal values.
As per business, for certain calculation, the decimal values in this table is used per priority. Determining which priority record should be taken to consideration is another logic in a PROCEDURE. Not only the priority, but also the dates are used to take record.
A new record in this table means it will be an effective record with no termination date, records with no termination dates will be of top priority (not always) and other records will be pushed down further. In future if a new value arrives for a future date, then the current record will be terminated and moved down.
The scenario here in my case is, user doesn't want to move down the records which have different effdate and termdate because they are still considered as valid ones. But the records with same effedate and termdate are considered invalid and have to push down.
April 22, 2014 at 8:37 pm
I do not know how to explain the business in more sensible manner, but please consider that the original table here have few more fields which are used for business, there are other fields to provide uniqueness. But for priority, it is just a field which used to order the record so that the high priority could be taken. What if two records with same priority is there? No problem, just take the top one in arbitrary order. If any one complains, as of now, we will ask them to change the priority. What if someone changes the priority to zero for all records in production? well, I think that guy will be sacked and team will get data from daily back-ups (we lose some changes between back-ups).
Priority is just a field to prioritize the records which needs to be considered for calculation, user can add any number to it, according to that number the PROCEDURE will take values.
April 23, 2014 at 10:27 am
I was trying to develop something similar, can't get time to get back to it. What I was thinking is if I know I'm adding a new entry and it is to be priority #1, I would just add to to all other records priority before inserting the new record, based on certain criteria. In my case I was also going to allow them to enter the priority say as #4 and I would take everything currently in the table that meet the criteria with a priority of 4 or greater(5, 6, 7,...) and add 1 to it, then insert the new record.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 23, 2014 at 11:27 am
I'm still waiting for a description of the business rule how to sort the rows (without the term "priority").
April 23, 2014 at 10:28 pm
There is no other way to sort the records other than using 'Priority' column as well as 'EffDate' column. Business rule will select highest priority column for calculation where the given date is within the range of selected records. There will be no two records with same priority.
This is how to get the value I need for calculation:
Select top 1 MyRate
from Temp1
where @GivenDate between MyEffDate and Isnull(MyTermDate, MyEffDate)
order by MyPriority
Below code taken original procedure, which won't work in my case (as per the requirement):
update CustomerNetworkcontract
set NetworkPriority = NetworkPriority + 1
where customerid = @GivenCustId
andNetworkPriority >= @GivenPriority;
<< Here I insert new record with @GivenPriority>>
Below code makes sure everything in right order:
with RowNumber as
(
select Contract_Id,
row_number() over (order by NetworkPriority asc, ContractEffDate desc) as RowNumber
from CustomerNetworkContract
where customerid = @GivenCustId
)
update cnc
set NetworkPriority = rn.RowNumber
from CustomerNetworkContract cnc
inner join RowNumber rn
on cnc.Contract_Id = rn.Contract_Id
where cnc.NetworkPriority <> rn.RowNumber;
April 24, 2014 at 9:50 pm
Trying to make it on top
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply