November 3, 2010 at 10:10 am
Hello:
I'm working on automating the assignment of project numbers for a student science fair. Most of the project numbers consist of a 3-letter CategoryCode (BIO for Biology, CHE for Chemistry, etc.) plus a 3-digit number that's unique to each student within a category (BIO001, BIO002, CHE001, etc.).
There are two Team categories that require a different format, and this is where I'm struggling. Since members of a team have the same project, they must all have the same project number, but I need to tack on a letter at the end to differentiate them. So if John Adams, Jane Johnson, and Mary Winters are in Group X together, I need their numbers to be TEA001a, TEA001b, and TEA001c, respectively. If Sarah Anderson, Becky Jackson, and Pete Wilson are in Group Y together, I need their numbers to be TEA002a, TEA002b, and TEA002c, respectively.
This is how I'm generating the project number so far:
CREATE TABLE #Temp(
Increment int IDENTITY(1,1),
ProjectTitle nvarchar(255),
ProjectCategoryCode nvarchar(10)
)
INSERT INTO #Temp (ProjectTitle, ProjectCategoryCode)
SELECT DISTINCT LTRIM(RTRIM(ProjectTitle)), 'TEA'
FROM Students
WHERE ProjectCategory = 'Team Project'
ORDER BY LTRIM(RTRIM(ProjectTitle))
UPDATE Students
SET ProjectNumber = #Temp.ProjectCategoryCode+CAST(RIGHT('000'+CONVERT(VARCHAR,#Temp.Increment),3) AS nvarchar(50))
FROM Students INNER JOIN #Temp
ON LTRIM(RTRIM(Students.ProjectTitle)) = #Temp.ProjectTitle
DROP TABLE #Temp
This will get the first 6 characters I need into the ProjectNumber field in the Students table, but then I need to tack on the incremental letters for each student within their project. Can anyone help me with this?
November 3, 2010 at 11:26 am
Create a sub-table for students, include a column for "Project Suffix" which will include the "a", "b", "c". For ones that don't need that, put a zero-length string or a blank space in that column.
If a student can be on more than one project, you'll need a join table instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 12:19 pm
Thanks for the quick response! I guess what I'm struggling with is how to increment the "a", "b", "c" part within each project. How do I make sure that the lettering starts over again for each project like this?:
TEA001a
TEA001b
TEA002a
TEA002b
TEA002c
TEA003a
TEA003b
All I can figure out how to do is this:
TEA001a
TEA001b
TEA002c
TEA002d
TEA002e
TEA003f
TEA003g
And students can only have one project each, so I don't need to worry about that.
November 3, 2010 at 12:22 pm
Note that it's not a good design to have a field being a composite of two different definitions. Technically, this isn't even 1NF (first normal form).
You really ought to have your tables more along the lines of:
if OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;
if OBJECT_ID('dbo.Students') IS NOT NULL DROP TABLE dbo.Students;
if OBJECT_ID('dbo.TeamCodes') IS NOT NULL DROP TABLE dbo.TeamCodes;
if OBJECT_ID('dbo.CategoryCodes') IS NOT NULL DROP TABLE dbo.CategoryCodes;
-- reference table for CategoryCodes (ie. BIO, CHE, TEA)
CREATE TABLE dbo.CategoryCodes (
CategoryCodeID INT IDENTITY PRIMARY KEY CLUSTERED,
Name varchar(50));
-- reference for team codes as necessary
CREATE TABLE dbo.TeamCodes (
TeamCodeID INT IDENTITY PRIMARY KEY CLUSTERED,
Name varchar(50));
-- add a "0" value for no team
SET IDENTITY_INSERT dbo.TeamCodes ON;
INSERT INTO dbo.TeamCodes (TeamCodeID, Name) values (0, 'Not Assigned');
SET IDENTITY_INSERT dbo.TeamCodes OFF;
-- student table
CREATE TABLE dbo.Students (
StudentID INT PRIMARY KEY CLUSTERED,
FirstName varchar(50),
LastName varchar(50));
-- table with separate columns for each piece of data
CREATE TABLE MyTable (
CategoryCodeID int,
StudentID int,
TeamCodeID int DEFAULT(0),
CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (CategoryCodeID, StudentID, TeamCodeID));
-- and foreign keys to maintain referential integrity
ALTER TABLE dbo.MyTable ADD CONSTRAINT FK_MyTable_TO_Students FOREIGN KEY (StudentID) REFERENCES dbo.Students(StudentID);
ALTER TABLE dbo.MyTable ADD CONSTRAINT FK_MyTable_TO_CategoryCodes FOREIGN KEY (CategoryCodeID) REFERENCES dbo.CategoryCodes(CategoryCodeID);
ALTER TABLE dbo.MyTable ADD CONSTRAINT FK_MyTable_TO_TeamCodes FOREIGN KEY (TeamCodeID) REFERENCES dbo.TeamCodes(TeamCodeID);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 3, 2010 at 12:27 pm
Offhand, without integrating it into your code (or testing this pseudocode, for that matter, just trying to give you an idea):
You'd use the CHAR() function which turns a numeric into a character, a ROW_NUMBER OVER( PARTION BY ProjectCategoryCode Order By ProjectCategoryCode) to feed that number into the CHAR() function, and using a modulo (%) to make sure it stays inside of 26/52 usable characters. You'll need to adjust the ROW_NUMBER value with an addition to get it into the a-z range. Easiest way is to while loop a counter and print CHAR(@c) to see what #'s you want to use inside your current font.
What that will do is give you a character, based on the row_number(), which will restart at 1 for each ProjectcategoryCode.
If you end up struggling with that give a hollar and I'll see if I can't take a swing at that solution later tonight if noone else can.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 12:32 pm
Hi, Wayne:
Sorry, I guess I should have been more clear on what the purpose of this is. Students register for the fair months in advance, so when their records go into the DB, they do have a primary key and everything is in 1NF. A few days before the fair, we assign each student a project number to track their monetary winnings for tax purposes. So this isn't something that will be done with the intent of using the project number as an IDENTITY field or even as a foreign key; it's just a stored procedure that will be run manually once a year before the fair.
November 3, 2010 at 12:34 pm
Thanks, Craig! I'll readily admit that my understanding of loops in T-SQL is limited at best; I have more experience with looping in VB. Your logic makes sense to me; I'll just need to figure out the proper syntax. I'm off to a meeting now, but I'm going to research your suggestion as soon as I get back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply