New record column value should be defaulted to the previous value

Question

Post reply

New record column value should be defaulted to the previous value

sk-108165

Newbie

Points: 2
More actions
February 8, 2005 at 1:17 pm

#65351

Hi,
I am a newbie to SQL Server. I need your help.
In one of our table, I have to set the value of a column to the 'previous' value in the new record, as the default value for that record.
Ex. the table A has the following columns (patient_id,serialno,effectivedate, glucose).
Patient_id SerialNo Effectivedate Glucose
11             1           12-02-04          100
11              2           12-25-04          101
11              3           02-07-05           91
say for eg, for the new record for 'EffectiveDate' 02-08-05 the value of glucose should be defaulted to 91 for patient_id '11'. The 'serialno' column is the incremented value column. Is there a way to get this?
Any idea or help is greatly appreciated. Thanks in advance.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

CREATE FUNCTION fnGetGlucoseDefault (@Patient_id as int)

RETURNS SMALLINT

AS

Return (Select Glucose FROM dbo.MyTable where Patient_id = @Patient_id AND EffectiveDate = (Select MAX(Effectivedate) from dbo.MyTable where Patient_id = @Patient_id))

GO

Then you can set the default value of Glucose to this :

dbo.fnGetGlucoseDefault (patient_id)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I just reread your post... Does the column SerialNo needs to be incremented individually for each patient or just needs to be incremented regardless of which patient has been inserted?

sk-108165 Newbie Points: 2 More actions · Answer 3

The column SerialNo increments regardless of the patientid, it is not dependent on patient_id.

Is this can be achieved through a trigger? Please advise.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I would simply create the SerialNo as an identity(1,1) field

you can run this script to change it's state so that it increments on its own :

ALTER TABLE YourTableNAME

ALTER COLUMN SerialNo int identity (1,1) not null

Go

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

ALTER TABLE YourTableNAME

ALTER COLUMN SerialNo int identity (1,1) not null

Go

Interesting script. Do you think it will work?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Looks like it can't be done that way.

Any other shortcut to propose Frank? (besides letting EM recreate the table)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

No shortcut here. And that's good so. Here are some thought on this from SQL Server MVP Kenneth Wilhelmsson:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=158711

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]