here is my schema...
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this...2007-VU-000, 2007-VU-001, 2007-VU-003...so my question is
1. how do i get the last three numbers to increment each time a new application is created?
2. how do i retrieve the last record in the table?
3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"?
If you want to manually assign the numbers, make sure all your INSERTs are well maintained within TRANSACTIONs so there's no resouce contention.
>>>1. how do i get the last three numbers to increment each time a new application is created?
RIGHT(column,3)
>>>2. how do i retrieve the last record in the table?
MAX(Column) From the Table.
>>>3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"?
Its probably better to have an Identity column, let SQL Server assign the number, then have your Case# as a computed column. Get the Id from the INSERT via SCOPE_IDENTITY() and concatenate it with the Case#.
|||Here is some code w/comments addressing points 1 & 2, I'm heading out, so I don't have time to comment on point 3. I would warn you though that case numbers don't look like they replicate very well. I understand that people like having a sequential number that is human friendly, but I would consider adding on a random sequence or something.
Good luck
-- table to hold test dataCREATE TABLE #TestTable(CaseNumberVARCHAR(20)PRIMARY KEY CLUSTERED,DataVARCHAR(1));-- previous years dataINSERT INTO #TestTable (CaseNumber, Data)VALUES('2006-VU-001','a');INSERT INTO #TestTable (CaseNumber, Data)VALUES('2006-VU-002','a');INSERT INTO #TestTable (CaseNumber, Data)VALUES('2006-VU-003','a');-- we don't want anyone inserting while we are in hereSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- storage for the next caseDECLARE @.NextCaseVARCHAR(20);-- determine the next case by checking if we need a new year, case 1 for the new year-- or if we need to increment the previous years case countSELECT TOP 1 @.NextCase =CASEWHENDATEPART(yy,GETDATE()) =DATEPART(yy,CAST(LEFT(tt.CaseNumber, 4)AS DATETIME))THENLEFT(tt.CaseNumber, 4) +'-VU-' + REPLICATE('0', 3 -LEN(CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR))) +CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR)ELSECAST(DATEPART(yy,GETDATE())AS VARCHAR) +'-VU-001'ENDFROM #TestTableAS ttORDER BY tt.CaseNumberDESC;-- add the new case, note this tests the year roll-overINSERT INTO #TestTable (CaseNumber, Data)VALUES (@.NextCase,'b');-- view the last caseSELECT TOP 1 CaseNumberAS HighestCaseNumberFROM #TestTableORDER BY CaseNumberDESC;-- get the next case againSELECT TOP 1 @.NextCase =CASEWHENDATEPART(yy,GETDATE()) =DATEPART(yy,CAST(LEFT(tt.CaseNumber, 4)AS DATETIME))THENLEFT(tt.CaseNumber, 4) +'-VU-' + REPLICATE('0', 3 -LEN(CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR))) +CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR)ELSECAST(DATEPART(yy,GETDATE())AS VARCHAR) +'-VU-001'ENDFROM #TestTableAS ttORDER BY tt.CaseNumberDESC;-- add the new case to the table, note this tests incrementing the current yearINSERT INTO #TestTable (CaseNumber, Data)VALUES (@.NextCase,'c');-- view the latest caseSELECT TOP 1 CaseNumberAS HighestCaseNumberFROM #TestTableORDER BY CaseNumberDESC;-- get the next case againSELECT TOP 1 @.NextCase =CASEWHENDATEPART(yy,GETDATE()) =DATEPART(yy,CAST(LEFT(tt.CaseNumber, 4)AS DATETIME))THENLEFT(tt.CaseNumber, 4) +'-VU-' + REPLICATE('0', 3 -LEN(CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR))) +CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR)ELSECAST(DATEPART(yy,GETDATE())AS VARCHAR) +'-VU-001'ENDFROM #TestTableAS ttORDER BY tt.CaseNumberDESC;-- add the new case to the table, note this tests incrementing the current yearINSERT INTO #TestTable (CaseNumber, Data)VALUES (@.NextCase,'d');-- view the latest caseSELECT TOP 1 CaseNumberAS HighestCaseNumberFROM #TestTableORDER BY CaseNumberDESC;-- view all casesSELECT *FROM #TestTable;-- drop the test tableDROP TABLE #TestTable;/*-- lets take a look at our cased based goodness-- the first condition in the caseCASE WHEN DATEPART(yy, GETDATE()) = DATEPART(yy, CAST(LEFT(tt.CaseNumber, 4) AS DATETIME))THEN -- the current max case is the current year, build the next case based on the highest number for the yearELSE -- the current max case is not this year, we can shortcut here and build the 001 case number based on the current yearEND-- the first statement of the true/current year build id-- use the left four digits of the case number as for the current year-- concatenate on -VU-LEFT(tt.CaseNumber, 4) + '-VU-' +-- zero pad the right three digits to three positions-- this part is pretty nasty-- we need to calculate the case number twice, once to know how much zero-- padding is necessary and once to actually produce a value-- we need to add one to the number before padding just in case our next digit rolls over-- first get the case number, make it an INT, then add one to it, finally use that length/value-- cast back to varchar and concat everything togetherREPLICATE('0', 3 - LEN(CAST(CAST(RIGHT(tt.CaseNumber, 3) AS INT) + 1 AS VARCHAR))) + CAST(CAST(RIGHT(tt.CaseNumber, 3) AS INT) + 1 AS VARCHAR)*/|||ok...now this maybe a little rediculous of a question but where does this go?? in SQL itself or somewhere else? i've never worked with exceptions before so...
|||I would place the code into a stored procedure that is used for inserting new cases. The stored procedure would take the inputs necessary to create a row, use the code I wrote above to determine the next case number and then perform the insert.
CREATE PROCEDURE dbo.InsertCase
(
@.Parm1 ...
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @.NextCase;
SELECT @.NextCase = Code from above;
INSERT INTO YourTable(CaseNumer, Parm1...) VALUES(@.NextCase, @.Parm1...);
END;
so when i create the stored procedure...it should look like this??
CREATE PROCEDURE dbo.InsertCase
(
@.Parm1 ...
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @.NextCase;
SELECT @.NextCase =CASEWHENDATEPART(yy,GETDATE()) =DATEPART(yy,CAST(LEFT(tt.CaseNumber, 4)AS DATETIME))
THENLEFT(tt.CaseNumber, 4) +'-VU-' + REPLICATE('0', 3 -LEN(CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR))) +CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR)
ELSECAST(DATEPART(yy,GETDATE())AS VARCHAR) +'-VU-001';
INSERT INTO BZA(BZAcaseNum, ApplicantID, OwnerID, ParcelNum, DateFiled, AcerageSize, Zoning, HearingMonth, HearingDay VALUES (@.NextCase, @.ApplicantID, @.OwnerID, @.ParcelNum, @.DateFiled, @.AcerageSize, @.Zoning, @.HearingMonth, @.HearingDay);
END;
what is the tt.CaseNumber?? that is the table its pulling the data from right?? so in my table where it gets the case number is the VarianceOfUse table...so it should be "VarianceOfUse.BZAcaseNum"
|||
sorry to be a pest but does that look right??
|||
keinspahr:
so when i create the stored procedure...it should look like this??
CREATE PROCEDURE dbo.InsertCase
(
@.Parm1 ...
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @.NextCase;SELECT @.NextCase =CASEWHENDATEPART(yy,GETDATE()) =DATEPART(yy,CAST(LEFT(tt.CaseNumber, 4)AS DATETIME))
THENLEFT(tt.CaseNumber, 4) +'-VU-' + REPLICATE('0', 3 -LEN(CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR))) +CAST(CAST(RIGHT(tt.CaseNumber, 3)AS INT) + 1AS VARCHAR)
ELSECAST(DATEPART(yy,GETDATE())AS VARCHAR) +'-VU-001';INSERT INTO BZA(BZAcaseNum, ApplicantID, OwnerID, ParcelNum, DateFiled, AcerageSize, Zoning, HearingMonth, HearingDay VALUES (@.NextCase, @.ApplicantID, @.OwnerID, @.ParcelNum, @.DateFiled, @.AcerageSize, @.Zoning, @.HearingMonth, @.HearingDay);
END;what is the tt.CaseNumber?? that is the table its pulling the data from right?? so in my table where it gets the case number is the VarianceOfUse table...so it should be "VarianceOfUse.BZAcaseNum"
you need to have a FROM and ORDER BY statements for your SELECT @.NextCase.
Possibly, FROM BZA ORDER BY CaseNumber DESC
Other than that and filling in the rest of the parameters, you should be good to go.
|||do you have a good website or anything to learn about that statement...cause it confuses the hell out of me i've never seen something like that before, i kind of get the idea of what is going on but i just always want to know more about it...thanks
|||i just can't get it working...i don't know im sure it works great but i just don't have the understanding of that situational stuff...so maybe if you can take to site or something to learn what is going on there i will understand it and use it...i really appreciate it...
|||The code that I posted should run on its own. Just take that code and put it into a query analyzer window and run it. You should be able to see it work. Once you've made it that far, run it piece-by-piece. Analyze it. Try incorporating your base table instead of the test table. Get that working, then try making a procedure to do the insert. Try doing things step-by-step.
|||ok here is the final of what i did...the table i am pulling from is called VarianceOfUse, which has columns for BZAcaseNum, CurrentUse, ProposedUse, Comments....and when i run this in query analyzer it says "Incorrect Syntax Near BZAcaseNum" im total lost!!!
-- table to hold data
dbo.VarianceOfUse
(
BZAcaseNum CHAR(20) PRIMARY KEY CLUSTERED
,CurrentUse Char(100)
,ProposedUse Char(500)
,Comments Char(500)
);
-- we don't want anyone inserting while we are in here
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- storage for the next case
DECLARE @.NextCase VARCHAR(20);
-- determine the next case by checking if we need a new year, case 1 for the new year
-- or if we need to increment the previous years case count
SELECT TOP 1 @.NextCase = CASE WHEN DATEPART(yy, GETDATE()) = DATEPART(yy, CAST(LEFT(VarianceOfUse.BZAcaseNumber, 4) AS DATETIME))
THEN LEFT(VarianceOfUse.BZAcaseNumber, 4) + '-VU-' + REPLICATE('0', 3 - LEN(CAST(CAST(RIGHT(VarianceOfUse.BZAcaseNumber, 3) AS INT) + 1 AS VARCHAR))) + CAST(CAST(RIGHT(VarianceOfUse.BZAcaseNumber, 3) AS INT) + 1 AS VARCHAR)
ELSE CAST(DATEPART(yy, GETDATE()) AS VARCHAR) + '-VU-001'
END
FROM VarianceOfUse
ORDER BY VarianceOfUse.BZAcaseNumber DESC;
No comments:
Post a Comment