MSSQLServer: Sequences using Date based prefix format

PHOTO EMBED

Tue Nov 21 2023 18:26:52 GMT+0000 (Coordinated Universal Time)

Saved by @marcopinero #sql 

/********* tables *************/

CREATE TABLE xsequences (
	id      int IDENTITY(1,1) NOT NULL,
	code    varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	prefix  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL,
	digits  int DEFAULT 1 NOT NULL,
	currsec int DEFAULT 0 NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX ndx_seq_code_prf ON xsequences(code, prefix);

CREATE TABLE HISDBTST.dbo.xsequences_format (
	id      int IDENTITY(1,1) NOT NULL,
	code    varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	prefix  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL,
	digits  int DEFAULT 1 NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX ndx_seq_fmt_code ON xsequences_format(code);




/************************ p_sequence: Sequence generator ***********/
/*
code            prefix          digits (zero filled)
--------------  -----------     ------
PATRECORD             yyMM-	    4
EMERGENCY       "EM"yyMMdd-	    3
OUTPATIENT	    "OP"yyMMdd-	    3
INPATIENT   	"IP"yyMMdd-	    3
REQUIREMENT	    "RQ"yyMMdd-	    3
*/

CREATE procedure [dbo].p_sequence(
	@code   varchar(20), 
	@date   datetime, 
	@result varchar(100) output
)
as
begin
	declare @currsec int;
	declare @prefix varchar(20);
	declare @digits int;

	select @prefix=prefix,
	        @digits=digits
	from xsequences_format sf 
	where code=@code;

	set @prefix = format(isnull(@date,getdate()), @prefix);
	
	insert into xsequences(code, prefix, currsec)
	select @code, @prefix, 0
	WHERE NOT EXISTS(SELECT ID FROM xsequences WHERE code=@code and prefix=@prefix);	

	update dbo.xsequences 
	set currsec=currsec + 1, @currsec = currsec + 1
	WHERE code=@code and prefix=@prefix;

	set @result = isnull(@prefix,'') + dbo.lpad(@currsec, @digits);
end;



/********************* trigger example **************************/

CREATE TRIGGER [dbo].t_patient_record  ON  [dbo].patient 
for insert not for replication 
AS 
BEGIN
	SET NOCOUNT ON;
	
	declare @RecordNo varchar(30)
	declare @Date datetime = GETDATE();

	-- select @Date = Creation from inserted;

    /***/
	exec p_sequence 'RECORD', @Date, @RecordNo output;
	/***/
	
	update Pacient set RecordNo=@RecordNo where PatientId =(select PatientId from inserted)
END
content_copyCOPY