March 29, 2018 at 11:10 am
How do you generate unique number based on a Loannumber , Date columns
Example
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'
Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100)
)
Insert into @test-2
SELECT @LOannumber
,@date
i want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
March 29, 2018 at 11:23 am
Is there some format you need? You can certainly use a sequence. You can use a newid() and convert this to a varchar if necessary.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 29, 2018 at 12:21 pm
Steve Jones - SSC Editor - Thursday, March 29, 2018 11:23 AMIs there some format you need? You can certainly use a sequence. You can use a newid() and convert this to a varchar if necessary.
I tried new_id() Example:
id Loannumber Startdate New
1 12345 2015-12-01 6BED3396-7D80-4D50-8877-BAF9AC74F2BB
2 12345 2016-12-01 ED26A4C8-875F-4E0D-BC2B-45B0A96A36E8
3 12335 2016-12-01 02EC8E23-A2A9-4977-8674-72E9DC559090
4 12345 2016-12-01 88F2C7AA-1265-4FE0-AE62-F0459B529DEB
Look at ID 2,4 the values are same but newid is different , i want the unique id changed if value changes otherwise should remain same for the laonnumber and date combination.
March 29, 2018 at 12:34 pm
komal145 - Thursday, March 29, 2018 12:21 PMSteve Jones - SSC Editor - Thursday, March 29, 2018 11:23 AMIs there some format you need? You can certainly use a sequence. You can use a newid() and convert this to a varchar if necessary.I tried new_id() Example:
id Loannumber Startdate New
1 12345 2015-12-01 6BED3396-7D80-4D50-8877-BAF9AC74F2BB
2 12345 2016-12-01 ED26A4C8-875F-4E0D-BC2B-45B0A96A36E8
3 12335 2016-12-01 02EC8E23-A2A9-4977-8674-72E9DC559090
4 12345 2016-12-01 88F2C7AA-1265-4FE0-AE62-F0459B529DEB
Look at ID 2,4 the values are same but newid is different , i want the unique id changed if value changes otherwise should remain same for the laonnumber and date combination.
I tried hash bytes ....but it keeps inserting chinese characters...
drop table #test
CREATE Table #test
(
LOannumber int
,Startdate date
,New nvarchar(100)
)
Insert into #test
SELECT 12345
,'12-01-2016'
,Hashbytes('SHA', cast( 12345 as varchar(10)) + cast ('12-01-2016' as varchar(10)) )
March 29, 2018 at 12:39 pm
komal145 - Thursday, March 29, 2018 12:34 PMkomal145 - Thursday, March 29, 2018 12:21 PMSteve Jones - SSC Editor - Thursday, March 29, 2018 11:23 AMIs there some format you need? You can certainly use a sequence. You can use a newid() and convert this to a varchar if necessary.I tried new_id() Example:
id Loannumber Startdate New
1 12345 2015-12-01 6BED3396-7D80-4D50-8877-BAF9AC74F2BB
2 12345 2016-12-01 ED26A4C8-875F-4E0D-BC2B-45B0A96A36E8
3 12335 2016-12-01 02EC8E23-A2A9-4977-8674-72E9DC559090
4 12345 2016-12-01 88F2C7AA-1265-4FE0-AE62-F0459B529DEB
Look at ID 2,4 the values are same but newid is different , i want the unique id changed if value changes otherwise should remain same for the laonnumber and date combination.I tried hash bytes ....but it keeps inserting chinese characters...
drop table #test
CREATE Table #test
(
LOannumber int
,Startdate date
,New nvarchar(100)
)Insert into #test
SELECT 12345
,'12-01-2016'
,Hashbytes('SHA', cast( 12345 as varchar(10)) + cast ('12-01-2016' as varchar(10)) )
Yes, hashbytes is converting into a binary value it might not convert into a pretty character string.
But if you just want a derived value instead of a new unique key, why not just convert the loan number and date to strings and concatenate them?
March 29, 2018 at 12:48 pm
komal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
This is pretty much a form of "Death by SQL" for more reasons than I can count. One of the reasons is that LoanNumbers are NOT guaranteed to stay the same. Being in the mortgage financial world, I can tell you that we've had to do wholesale LoanNumber replacements for 4 major banks in the last 3 years. What is the business reason that you're trying to accomplish with this?
--Jeff Moden
March 30, 2018 at 10:11 am
komal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
Here's one way:
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'
select Hashbytes('SHA2_256',
(SELECT t.loannumber,t.[date]
FROM (SELECT @loannumber loannumber,@date [date]) t for xml auto)
)
Hope this helps.
March 30, 2018 at 12:49 pm
m.katrobos - Friday, March 30, 2018 10:11 AMkomal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
Here's one way:
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'
select Hashbytes('SHA2_256',
(SELECT t.loannumber,t.[date]
FROM (SELECT @loannumber loannumber,@date [date]) t for xml auto)
)
Hope this helps.
And what do you do when the Loan Number changes someday in the future?
--Jeff Moden
March 30, 2018 at 1:24 pm
Jeff Moden - Thursday, March 29, 2018 12:48 PMkomal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
This is pretty much a form of "Death by SQL" for more reasons than I can count. One of the reasons is that LoanNumbers are NOT guaranteed to stay the same. Being in the mortgage financial world, I can tell you that we've had to do wholesale LoanNumber replacements for 4 major banks in the last 3 years. What is the business reason that you're trying to accomplish with this?
So don't you keep a record of the original loan number tied to the new loan number? Seems a bit risky if you don't right? Isn't it pretty important that the paper trail for the original loan be pretty airtight?
Also triangular joins are set oriented operations.
March 30, 2018 at 1:30 pm
patrickmcginnis59 10839 - Friday, March 30, 2018 1:24 PMJeff Moden - Thursday, March 29, 2018 12:48 PMkomal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
This is pretty much a form of "Death by SQL" for more reasons than I can count. One of the reasons is that LoanNumbers are NOT guaranteed to stay the same. Being in the mortgage financial world, I can tell you that we've had to do wholesale LoanNumber replacements for 4 major banks in the last 3 years. What is the business reason that you're trying to accomplish with this?
So don't you keep a record of the original loan number tied to the new loan number? Seems a bit risky if you don't right? Isn't it pretty important that the paper trail for the original loan be pretty airtight?
Also triangular joins are set oriented operations.
I'm quite sure Jeff wasn't suggesting that you can't or shouldn't keep track of the old number. The problem, however, is larger than that, and separate from it. If you base a calculation on a number that at some point might change, even if you keep track of the old one, what plan would be in place to handle the purpose of this additional hashbytes computation, which would then no longer match, and then you'd have to worry about anywhere else that data might get stored. Seems to me that Jeff was properly exposing a rather large hole in such an idea.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 30, 2018 at 1:57 pm
Couldn't you concat the loan number and the date to create the unique ID? In your example 2 and 4 would have the same UniqueID.ID Loan# Date UniqueID
1 12345 2015-12-01 1234520151201
2 12345 2016-12-01 1234520161201
3 12335 2016-12-01 1233520161201
4 12345 2016-12-01 1234520161201
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 30, 2018 at 3:38 pm
sgmunson - Friday, March 30, 2018 1:30 PMpatrickmcginnis59 10839 - Friday, March 30, 2018 1:24 PMJeff Moden - Thursday, March 29, 2018 12:48 PMkomal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
This is pretty much a form of "Death by SQL" for more reasons than I can count. One of the reasons is that LoanNumbers are NOT guaranteed to stay the same. Being in the mortgage financial world, I can tell you that we've had to do wholesale LoanNumber replacements for 4 major banks in the last 3 years. What is the business reason that you're trying to accomplish with this?
So don't you keep a record of the original loan number tied to the new loan number? Seems a bit risky if you don't right? Isn't it pretty important that the paper trail for the original loan be pretty airtight?
Also triangular joins are set oriented operations.
I'm quite sure Jeff wasn't suggesting that you can't or shouldn't keep track of the old number. The problem, however, is larger than that, and separate from it. If you base a calculation on a number that at some point might change, even if you keep track of the old one, what plan would be in place to handle the purpose of this additional hashbytes computation, which would then no longer match, and then you'd have to worry about anywhere else that data might get stored. Seems to me that Jeff was properly exposing a rather large hole in such an idea.
Good points, all. Thank you.
March 30, 2018 at 3:52 pm
Here's another option that actually uses the loan number and date as a part of a calculation.
It's efficient enough to be calculated on the fly or it could be added as a persisted computed column... So, if either of the underlying values changes, the computed value changes accordingly.
It also has the advantage of returning an INT data type so you'd only be eating up 4 bytes of storage per row.
DECLARE
@_loan_num VARCHAR(20) = 'abc1234567',
@_date DATE = GETDATE();
SELECT CHECKSUM(CONCAT(@_loan_num, @_date));
The only potential downside is that, while unlikely, it is possible to get hash collisions... Aka, different input values produce the same output value.
March 30, 2018 at 3:59 pm
If the potential for collisions is an absolute deal breaker. Computing the checksums separately and then concatenating them should make it damned near (if not completely) impossible to get a collision due to the fact that no two dates should ever create the same checksum value.
DECLARE
@_loan_num VARCHAR(20) = 'abc1234567',
@_date DATE = GETDATE();
SELECT CONVERT(BIGINT, CONCAT(ABS(CHECKSUM(@_loan_num)), ABS(CHECKSUM(@_date))));
The downside, of course, is that you've now got a 16 bit BIGINT taking up double the storage space.
March 30, 2018 at 6:17 pm
patrickmcginnis59 10839 - Friday, March 30, 2018 1:24 PMJeff Moden - Thursday, March 29, 2018 12:48 PMkomal145 - Thursday, March 29, 2018 11:10 AMHow do you generate unique number based on a Loannumber , Date columnsExample
Declare @loannumber Int = 1234565
,@date Date = '12-01-2017'Declare @test-2 table
(
LOannumber int
,Startdate date
--,UNiqueidentifier nvarchar(100))
Insert into @test-2
SELECT @LOannumber
,@datei want to Generate unique identifier for the above laonnumber and date column , how can i do it?
select * from @test-2
This is pretty much a form of "Death by SQL" for more reasons than I can count. One of the reasons is that LoanNumbers are NOT guaranteed to stay the same. Being in the mortgage financial world, I can tell you that we've had to do wholesale LoanNumber replacements for 4 major banks in the last 3 years. What is the business reason that you're trying to accomplish with this?
So don't you keep a record of the original loan number tied to the new loan number? Seems a bit risky if you don't right? Isn't it pretty important that the paper trail for the original loan be pretty airtight?
Also triangular joins are set oriented operations.
Triangular joins may be set based but they are a form of RBAR.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply