|
I have a small programming problem, am requesting Your insight I don't know whether this should be in the C# forum or in the Database Forum, but since it looks more likely a Database thing, I'll ask it Here.
I have an application that is written in C# and has a Backed Database in SQL Server 2005. In Summary the application manages info on a company's distributors and their sales.
In the table, Distributors I have a Primary Key Column DistributorIDNO (char (8)) which stores their IDs in the formart SL000001 (up to a logical maximum of SL999999).
Different branches of the company are allocated number ranges to register new distributors, for example, the range SL002000 to SL002500 may be given to the office in Masaka (a town in My country), and at the Head Office they would continue to register others From SL002501 onwards).
Now, the people in Masaka may not fill up their quarter, which means between SL002000 and SL003000, some numbers are taken up, while others are not.
I would like to write a function that should look through the database and determine which numbers, between a particular range, have not been registered.
Peter
.NET Developer, loved programming from the day I looked at Basic code for the DOS Game "Snake". Learning doesn't stop.
|
|
|
|
|
This is NOT a good design for your primary key, a PK should be stupid and not convey any information.
Create a table variable and fill it with all possible numbers between the range you want to test, then select from that table where the PK is not in your Distributor table.
DECLARE
@Top INT,
@bottom INT
SET @bottom = 2000
SET @Top = 6000
DECLARE
@Count INT,
@Str VARCHAR(20)
DECLARE @Tbl TABLE(Code VARCHAR(20))
SET @Count = 1
WHILE @Count < @Top
BEGIN
SET @Str = 'SL' + RIGHT('000000000'+CAST(@Count + @bottom AS VARCHAR(10)),8)
INSERT @Tbl(Code) VALUES (@Str)
SET @Count = @Count + 1
END
SELECT Code
FROM @Tbl
WHERE Code NOT IN (SELECT PD FROM TableBame)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Does the term "SLOO" change? If not, then your key is built from multiple facts, and should be split into multiple columns. A simple identity-field would do nicely as a surrogate key, as suggested by MyCroft.
Bastard Programmer from Hell 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin 
Submit an article or tip
Post your Blog
