October 23, 2004 at 6:48 pm
I have created two tables that are called ReceiptAuto and Receipts. In both tables is a field called "ReceiptAuto" that automatically generates a unique number based upon three types of fields: 1) cost center that is called deptid in the the users table; 2) current year, and 3) sequential number - 00001. The entire field would be displayed as: 2030400001.
Scenario: When a user logs into an application it ties the emplid and deptid (cost center) together. However, the deptid will not show. In the Receipts table I have declared a several variables that I need to tie into the users table. Question 1: How do I change the value '123' that associates the userid and deptid in order for the right value to be declare? (See tables below)
Receipts Table:
CREATE PROCEDURE dbo.spInsert_Receipts
@ReceiptID int,
@ReceiptAuto char(12), (This is the ReceiptAutoID from the ReceiptAuto table shown below.)
@CityID int,
@RcptDate datetime out,
@RcptAmt numeric(18,0),
@OfficeID int,
@PymtTypeID int,
@CheckNum char(10),
@RecdFrom char(10),
@CaseNumber varchar(15),
@CasePlaintiff varchar(30),
@CaseDefendant varchar(30),
@CourtName varchar(30),
@SubpeonaDate datetime,
@UserID int,
@Waived bit,
@WaivedReason varchar(512),
@Voided bit,
@VoidDate datetime out,
@VoidReason varchar(512)
AS
--
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON
DECLARE @costCenter char(3), @nextNum int
SET @costCenter = '123' (this needs to tie into the users table based upon userid and deptid, see below)
SET @nextNum = (select ReceiptAutoID from ReceiptAuto)
UPDATE ReceiptAuto
SET ReceiptAutoID = ReceiptAutoID + 1
SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5)) (I need to move a copy of this to the @ReceiptAuto field, but I'm not sure how to display it correctly)
IF @RcptDate Is Null
SET @RcptDate = (getdate())
IF @VoidDate Is Null
SET @VoidDate = (getdate())
INSERT INTO [Receipts] (
ReceiptAuto, (do I need this field here, since it's being automatically created from the ReceiptAuto table)
CityID,
RcptDate,
RcptAmt,
OfficeID,
PymtTypeID,
CheckNum,
RecdFrom,
CaseNumber,
CasePlaintiff,
CaseDefendant,
CourtName,
SubpeonaDate,
UserID,
Waived,
WaivedReason,
Voided,
VoidDate,
VoidReason)
Values (
@ReceiptAuto, SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5))
@CityID,
@RcptDate,
@RcptAmt,
@OfficeID,
@PymtTypeID,
@CheckNum,
@RecdFrom,
@CaseNumber,
@CasePlaintiff,
@CaseDefendant,
@CourtName,
@SubpeonaDate,
@UserID,
@Waived,
@WaivedReason,
@Voided,
@VoidDate,
@VoidReason)
GO
ReceiptAuto Table:
ReceiptAutoID int
Users Table:
CREATE View dbo.vwSelect_Users
AS
SELECT
UserID,
LogEmpID,
Password,
SecLvlID,
RTRIM(EMPLID) AS EmpID,
LTRIM(RTRIM(LAST_NAME)) + ', ' + LTRIM(RTRIM(FIRST_NAME)) AS "Full Name",
RTRIM(SUBSTRING(DEPTID, 3, 3)) + ' - ' + DESCR AS "Cost Center",
DateCreated,
DateLastLogin,
InActiveDate
FROM [Users]
Sample Data:
1 10200400001 24 Central Station - 10 2004-10-19 07:52:58.310 71 2 NULL 1010 Cottontail, Peter 123456789 Cottontail, Peter Bunny, Bugs San Bernardino Municipal 2004-10-01 00:00:00.000 360 NULL NULL NULL 2004-10-19 07:52:58.310 NULL
2 16200400002 20 City: Highland - 16 2004-10-21 13:02:39.060 120 1 980 4312 Miller, Barney 223459811 Bird, Tweety Stallone, Sylvester Chino Court 2004-11-02 00:00:00.000 360 1 On Welfare NULL 2004-10-21 13:02:39.060 NULL
3 57200400003 2 City: Apple Valley - 57 2004-10-22 10:04:29.047 52 1 452 NULL Stallone, Sylvester 465398712 Sixkiller, Paul Poindexter, Ernesto Victorville Municipal 2005-01-15 00:00:00.000 360 NULL NULL NULL NULL NULL
4 30200400004 20 City: Rancho Cucamonga - 30 2004-10-22 10:14:32.547 45 2 NULL 2365 Bird, Tweey NULL NULL NULL NULL NULL 360 NULL NULL NULL NULL NULL
October 25, 2004 at 3:16 am
Is there any particular reason you bother with an intelligent PK?
In general, one of the criterias for a 'good' PK - is that it should not mean anything. PK's with a meaning is 'bad' - and thus also more difficult to work with. Any chance you could skip all this 'smart-key' stuff and just settle for a meaningless number or such..?
/Kenneth
October 25, 2004 at 5:19 am
Try this:
-- set the value to your query
set @ReceiptAuto = (SELECT @costCenter + RIGHT(DATEPART(yy,GETDATE()) ,2) + REPLICATE('0',5-LEN(@nextNum)) + CAST(@nextNum as varchar(5)))
"-- (I need to move a copy of this to the @ReceiptAuto field, but I'm not sure how to display it correctly)"
--modify the insert
INSERT INTO [Receipts] (
ReceiptAuto, --(do I need this field here, since it's being automatically created from the ReceiptAuto table)
CityID,
RcptDate,
RcptAmt,
OfficeID,
PymtTypeID,
CheckNum,
RecdFrom,
CaseNumber,
CasePlaintiff,
CaseDefendant,
CourtName,
SubpeonaDate,
UserID,
Waived,
WaivedReason,
Voided,
VoidDate,
VoidReason)
Values (
@ReceiptAuto,
@CityID,
@RcptDate,
@RcptAmt,
@OfficeID,
@PymtTypeID,
@CheckNum,
@RecdFrom,
@CaseNumber,
@CasePlaintiff,
@CaseDefendant,
@CourtName,
@SubpeonaDate,
@UserID,
@Waived,
@WaivedReason,
@Voided,
@VoidDate,
@VoidReason)
I think this is what you are looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply