select
max (SSN)
from
MyTable
where
SSN between '001330000' and '001379999'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.Take a look at this
select RIGHT('000000000' + CONVERT(VARCHAR(9),12345),9)
change 12345 to your output
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ILCSP@.NETZERO.NET wrote:
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||What does Homeland Security think about you creating 'fake SSNs'?
g,d,r
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
>
Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA
will be looking for you... :-)
Oh crap, now me too...|||What if you create a 'fake' SSN for someone and then later on someone
signs up with that SSN, then what happens? Hint: Big Headache since
you have to assign a new 'fake' SSN to the old person and changes this
probably all over the place
Better to use XXXX00001 or some other Format like FAKE00001 etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Arnie Rowland wrote:[vbcol=seagreen]
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
I would think they are already contacting
CoreComm - Voyager, Inc
East Lansing, Michigan|||Hello all, I used Tom's way and it works great. Thanks for replying.
I think I should have not used the phrase fake SSNs, but I guess I just
wanted to get some attention.
:-)
Tom Moreau wrote:
> Is casting really necessary? Could you try:
> select
> max (SSN)
> from
> MyTable
> where
> SSN between '001330000' and '001379999'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||You're guarenteed to be high on the DHS/NSA watch list!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151438702.180395.194640@.u72g2000cwu.googlegroups.com...
> Hello all, I used Tom's way and it works great. Thanks for replying.
> I think I should have not used the phrase fake SSNs, but I guess I just
> wanted to get some attention.
> :-)
>
> Tom Moreau wrote:
>|||Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
assign them in a range that is currently in use by a state. That's just
asking for trouble.
According to the SSA, any valid number beginning with "000" will never be a
valid SSN. Here are some links to help with the business rules:
http://www.socialsecurity.gov/employer/highgroup.txt
http://www.socialsecurity.gov/employer/stateweb.htm
Given the three numbers you've passed in, the SELECT below will grab the
MAX(). It sounds like you want to add one and left pad it with zeroes.
Here's a sample:
CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @.iSSN INT
SELECT @.iSSN = CAST(@.ssn AS INT) + 1
DECLARE @.newSSN VARCHAR(9)
SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
RETURN @.newSSN
END
GO
CREATE TABLE #temp(ssn VARCHAR(9))
INSERT INTO #temp(ssn)
SELECT '001330000'
UNION SELECT '001379999'
UNION SELECT '001340062'
SELECT dbo.AddOneAndPadSSN(MAX(ssn))
FROM #temp
DROP TABLE #temp
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Nobody here but us wannabe fashion clothes reps...
"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>
>
> I would think they are already contacting
> CoreComm - Voyager, Inc
> East Lansing, Michigan
>
