November 9, 2004 at 10:41 am
I have created a table to create a computed column using one field that has an identity to create autonumber, an additional field to return the identity plus year using the format 0000-00. The table was designed using the following format:
CREATE TABLE dbo.ReceiptingAcct
(
LogId int NOT Null IDENTITY (1, 1),
Receipt AS RIGHT('000' + CAST(LogID as varchar(4)),4) + '-' + RIGHT(CAST(DATEPART(yy, LogDate) as varchar(4)),2), LogDate smalldatetime NOT NULL DEFAULT GetDate(),
OfficeCostCtr varchar(5) /*Cost Center Number represents office number or division, e.g., 44135)
I would like to incorporate the "OfficeCostCtr" into the Receipt, stripping the first two numbers '44' from '44135', and display only the last three numbers. The catch is to display the OfficeCostCtr number first, followed by the year and then the LogID number. If the field is viewed displays the following format: 1350400025. Again, the 135 would be the OfficeCostCtr, 04 displays the current year (2004) and the last five numbers displays the identity field.
Using the current table format above, how can I incorporate the OfficeCostCtr into the above format, excluding the + ' - ' + from the format. I also need to have leading zeros after the year and to have the year increment by 1 for the upcoming year (2005) with the identity field set automatically back to zero.
If I create a stored procedure to insert a new record. Do I need to have the LogID, Receipt, OfficeCostCtr should as part of the parameters, since this is being incremented automatically, that does not require users input?
November 9, 2004 at 1:32 pm
Sorry I don't have time to give you any code, but what if you create an INSERT trigger on dbo.ReceiptingAcct? You can slice and dice the data you inserted into a local variable, then update the Receipt column.
November 9, 2004 at 2:11 pm
Would I use the DECLARE @Receipt, @OfficeCostCtr, then use SET @Receipt = SELECT etc.
I've never used a variable before, so I'm not sure how to use it in a TRIGGER. Sorry!
November 9, 2004 at 3:23 pm
OK, assuming you are inserting one row at a time within a transaction scope (otherwise the trigger as I have written it will puke on too many rows returned) like so:
BEGIN TRANS
INSERT INTO dbo.ReceiptingAcct (Receipt, LogDate, OfficeCostCtr)
VALUES ('AlmostDone', CURRENT_TIMESTAMP, '44135')
COMMIT
The update trigger should look like so (no, I didn't test it):
CREATE TRIGGER ON dbo.ReceiptingAcct
FOR INSERT AS
DECLARE @Receipt char(10)
SELECT @Receipt = RIGHT(OfficeCostCtr, 3) +
RIGHT(CAST(DATEPART(yy, LogDate) as varchar(4)),2) +
-- hokey way of formatting the lower order five digits into a string
CASE WHEN LogID < 10 THEN '0000' + RIGHT(STR(LogID, 10, 0), 1)
WHEN LogID < 100 THEN '000' + RIGHT(CAST(LogID AS varchar(5)), 2)
WHEN LogID < 1000 THEN '00' + RIGHT(CAST(LogID AS varchar(5)), 3)
WHEN LogID < 10000 THEN '0' + RIGHT(CAST(LogID AS varchar(5)), 4)
ELSE CAST(LogID AS varchar(5))
END
FROM Inserted AS i
UPDATE e -- updates the one row just inserted, will replace 'AlmostDone' in Receipt
SET Receipt = @Receipt
FROM dbo.ReceiptingAcct AS e
JOIN Inserted AS i
ON i.LogID = e.LogID
END
Luck, Dave
November 9, 2004 at 3:27 pm
Thanks, I will give that a shot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply