Generate a unique number for a combination

Question

Generate a unique number for a combination

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

I still want to know the business reason behind this. Why is it that we need a unique number to represent the combination of a Loan Number and a Date to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 2

sgmunson - Friday, March 30, 2018 1:30 PM
patrickmcginnis59 10839 - Friday, March 30, 2018 1:24 PM
Jeff Moden - Thursday, March 29, 2018 12:48 PM
komal145 - Thursday, March 29, 2018 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
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.

Yes I don't disagree so thats why I asked what happened when you did get a new loan number. If you replace it in place, like update a row to a new value, then I suspect you'd then update the hashbytes value. If you add a new record that references the old (obviously in another column) then you'd probably insert a new hashbytes value. Because essentially you'd have a new loan number + date column.

I do agree that it would be interesting to see the original business case, but if he needs a hashbytes calculation of some sort, seems like it would apply to either of the above scenarios, so I'm also wondering if there is another scenario that I'm not thinking of.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

patrickmcginnis59 10839 - Saturday, March 31, 2018 6:49 AM
sgmunson - Friday, March 30, 2018 1:30 PM
patrickmcginnis59 10839 - Friday, March 30, 2018 1:24 PM
Jeff Moden - Thursday, March 29, 2018 12:48 PM
komal145 - Thursday, March 29, 2018 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
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.
Yes I don't disagree so thats why I asked what happened when you did get a new loan number. If you replace it in place, like update a row to a new value, then I suspect you'd then update the hashbytes value. If you add a new record that references the old (obviously in another column) then you'd probably insert a new hashbytes value. Because essentially you'd have a new loan number + date column.
I do agree that it would be interesting to see the original business case, but if he needs a hashbytes calculation of some sort, seems like it would apply to either of the above scenarios, so I'm also wondering if there is another scenario that I'm not thinking of.

The real key is that it doesn't matter if the loan number get's updated or not. It was just one of many examples where this type of thing will go totally haywire. The OP hasn't stated the business reason for this "conversion" and, having been through many such schemes in the past, I'm trying to protect the company the OP works for by saying that this is a very bad requirement that will lead to a world of hurt in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

komal145 SSCrazy Eights Points: 9915 More actions · Answer 4

Jeff Moden - Friday, March 30, 2018 12:49 PM
m.katrobos - Friday, March 30, 2018 10:11 AM
komal145 - Thursday, March 29, 2018 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
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?

This works fine untill you have null values , how we handle null values for the hashbytes?

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

komal145 - Thursday, April 5, 2018 2:06 PM
Jeff Moden - Friday, March 30, 2018 12:49 PM
m.katrobos - Friday, March 30, 2018 10:11 AM
komal145 - Thursday, March 29, 2018 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
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?
This works fine untill you have null values , how we handle null values for the hashbytes?

ISNULL(NULL, SomethingThatIsntNULL)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

komal145 - Thursday, April 5, 2018 2:06 PM
Jeff Moden - Friday, March 30, 2018 12:49 PM
m.katrobos - Friday, March 30, 2018 10:11 AM
komal145 - Thursday, March 29, 2018 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
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?
This works fine untill you have null values , how we handle null values for the hashbytes?

Heh... wait until you run across dupes in the two columns. What do you want to do then? For that matter, why are you doing this at all. Help me out here... why are you doing this and what is the practical nature of doing this? And please don't say "it's what I was asked to do". Ask the people giving you this task what they're trying to accomplish with this and why it has to be this way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

komal145 - Thursday, March 29, 2018 11:10 AM
How do you generate unique number based on a Loannumber , Date columns
<<snip>>
i want to Generate unique identifier for the above laonnumber and date column , how can i do it?

I know Jeff already asked but the question hasn't yet been answered.
Why do you want to do this? What is the business case?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden