September 16, 2009 at 2:36 am
Hi,
I have a 2 tables (1 Header and 1 Line table) that currently hold Sales contract details.
One of columns in both tables is a 'Contract Reference' field which stores the same value in each
(a one to Many relationship). The 'Line' table also stores an additional field which is a unique (incremental) table value irrerspective of each 'Contract Referemce' number.
For example:
Header Table
Contract_Reference
SCH0134TRI
SCH0135TRI
SCH0157TRI
SCH0158TRI
Line Table
Contract_Reference Line_Number
SCH0134TRI 91114
SCH0135TRI 91115
SCH0135TRI 91116
SCH0157TRI 91756
SCH0157TRI 91758
SCH0157TRI 91759
SCH0158TRI 91760
There is a new requirement to insert another column into the 'Line' table, to house actual 'line count' integers.
These must be set in the order of the existing 'Line numbers' for each 'Contract Reference'.
I've no idea why this wasn't an original requirement!!
So, the 'Line' table should be (along with additional fields).....
[Code]Line Table
Contract_Reference Line_Number Actual_Line_Number
SCH0134TRI 91114 1
SCH0135TRI 91115 1
SCH0135TRI 91116 2
SCH0157TRI 91756 1
SCH0157TRI 91758 2
SCH0157TRI 91759 3
SCH0158TRI 91760 1
[/code]
The Contract Reference and Line Numbers are the 'Order by' sort fields.
Can anyone help with this please?
Thanks in advance,
September 16, 2009 at 3:20 am
You can use NTILE() for the same. Below is the query that you can use on the line table after adding the Actual_Line_Number field:
UPDATE X SET X.Actual_Line_Number = Y.Rank
FROM LINE X JOIN
(SELECT Contract_Reference, Line_Number, NTILE() OVER(PARTITION BY Contract_Reference ORDER BY Contract_Reference) as RANK FROM LINE) Y
ON X.Contract_Reference = Y.Contract_Reference AND X.Line_Number = Y.Line_Number
Hope it Helps. Pardon me if there is any error in the query as I do not have access to my server at present to verify it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply