TẠO CỘT MÃ TĂNG TỰ ĐỘNG

Update lại cột mã cho khách hàng nếu muốn tăng tự động

1. Tìm kiếm và update trên DTECH update cột mã vật tư

-- Lấy cột Vattu là mã ngầm của danh mục Vật tư tăng dần để làm ID tăng dần
select Ma,ten,vattu,'VT'+ FORMAT(Vattu, '000000') from Vattu
where vattu<>0
order by vattu

Update Vattu
set Ma = 'VT'+ FORMAT(Vattu, '000000'),Modidate=getdate()
where vattu<>0

Đánh lại cột mã, bổ sung thêm số 00, không ảnh hưởng mã hiện tại thứ tự

-- Đối tượng công nợ
--1. Kiểm tra trước
select Ma,ten,KhachHang,'NCC'+ FORMAT(TRY_CAST(REPLACE(ma, 'NCC', '') AS INT), '00000') from KhachHang
where KhachHang<>0 and ma like 'NCC%' and charindex('0',ma)=0

--2. Update lại
Update KhachHang
set Ma = 'NCC'+ FORMAT(TRY_CAST(REPLACE(ma, 'NCC', '') AS INT), '00000'),Modidate=getdate()
where KhachHang<>0 and ma like 'NCC%' and charindex('0',ma)=0

-- Vật tư hàng hóa:
--1. Kiểm tra trước

select Ma,ten,Vattu,'RC'+ FORMAT(TRY_CAST(REPLACE(ma, 'RC', '') AS INT), '00000') from vattu
where vattu<>0 and ma like 'RC%' -and charindex('0',ma)=0


select Ma,ten,Vattu,'R'+ FORMAT(TRY_CAST(REPLACE(ma, 'R', '') AS INT), '00000') from vattu
where vattu<>0 and ma like 'R%' and ma not like 'RC%' --and charindex('0',ma)=0

--2. Update lại

Update Vattu
set Ma = 'RC'+ FORMAT(TRY_CAST(REPLACE(ma, 'RC', '') AS INT), '00000'),Modidate=getdate()
where vattu<>0 and ma like 'RC%' --and charindex('0',ma)=0

Update Vattu
set Ma = 'R'+ FORMAT(TRY_CAST(REPLACE(ma, 'R', '') AS INT), '00000'),Modidate=getdate()
where vattu<>0 and ma like 'R%' and ma not like 'RC%'  --and charindex('0',ma)=0

2. Kiểu chữ cái trước và số sau: “KH001 > KH002 > … > KH999”

--1. FUNCTION
CREATE FUNCTION AUTO_IDKH()
RETURNS VARCHAR(5)
AS
BEGIN
	DECLARE @ID VARCHAR(5)
	IF (SELECT COUNT(MAKH) FROM KHACHHANG) = 0
		SET @ID = '0'
	ELSE
		SELECT @ID = MAX(RIGHT(MAKH, 3)) FROM KHACHHANG
		SELECT @ID = CASE
			WHEN @ID >= 0 and @ID < 9 THEN 'KH00' + CONVERT(CHAR, CONVERT(INT, @ID) + 1)
			WHEN @ID >= 9 THEN 'KH0' + CONVERT(CHAR, CONVERT(INT, @ID) + 1)
		END
	RETURN @ID
END
--2. Thiết lập mặc định hàm DBO.AUTO_IDKH() cho trường MAKH
CREATE TABLE KHACHHANG(
	MAKH CHAR(5) PRIMARY KEY CONSTRAINT IDKH DEFAULT DBO.AUTO_IDKH(),
	HOTEN NVARCHAR(50) NOT NULL,
	SDT VARCHAR(11) NULL,
	DIACHI NVARCHAR(100) NULL,
	EMAIL VARCHAR(50) NULL
)
--Các bạn có thể tăng chiều dài mã bằng cách khai báo tăng chiều dài của MAKH và thêm nhiều nhánh CASE hơn.

1. Kiểu chỉ bao gồm chữ cái: AAAA > AAAB > … > ZZZZZ

// Function tạo mã
CREATE FUNCTION TAOMAVE(@BEFORECODE CHAR(6))---65->90
RETURNS CHAR(6)
AS
BEGIN
	DECLARE @ONE AS SMALLINT, @TWO SMALLINT, @THREE SMALLINT, @FOUR SMALLINT, @FIVE SMALLINT, @SIX SMALLINT
	DECLARE @MAVE CHAR(6)
	SELECT @ONE=ASCII(LEFT(@BEFORECODE,1))
	SELECT @TWO=ASCII(SUBSTRING(@BEFORECODE,2,1))
	SELECT @THREE=ASCII(SUBSTRING(@BEFORECODE,3,1))
	SELECT @FOUR=ASCII(SUBSTRING(@BEFORECODE,4,1))
	SELECT @FIVE=ASCII(SUBSTRING(@BEFORECODE,5,1))
	SELECT @SIX=ASCII(RIGHT(@BEFORECODE,1))
	IF @SIX<90
		SELECT @SIX=@SIX+1
	ELSE
		BEGIN
			SELECT @SIX=65
			IF @FIVE<90
				SELECT @FIVE=@FIVE+1
			ELSE
				BEGIN
					SELECT @FIVE=65
					IF @FOUR<90
						SELECT @FOUR=@FOUR+1
					ELSE
						BEGIN
							SELECT @FOUR=65
							IF @THREE<90
								SELECT @THREE=@THREE+1
							ELSE
								BEGIN
									SELECT @THREE=65
									IF @TWO<90
										SELECT @TWO=@TWO+1
									ELSE
										BEGIN
											SELECT @TWO=65
											IF @ONE<90
												SELECT @ONE=@ONE+1
											ELSE
												RETURN NULL
										END				
								END			
						END		
				END	
		END
	SELECT @MAVE=CHAR(@ONE)+CHAR(@TWO)+CHAR(@THREE)+CHAR(@FOUR)+CHAR(@FIVE)+CHAR(@SIX)
	RETURN @MAVE
END

1. Kiểu Ngày Tháng Năm + Chữ cái + Số tự động tăng: 01012001DV00001 > 02012001DV00002

// Function
CREATE FUNCTION auto_iddv()
RETURNS VARCHAR(15)
AS
BEGIN
	DECLARE @id VARCHAR(15)
	IF (SELECT COUNT(MaDV) FROM DATVE) = 0
		SET @id = '0'
	ELSE
		SELECT @id = MAX(RIGHT(MaDV, 5)) FROM DATVE
		SELECT @id = CASE
			WHEN @id = 99999 THEN CONVERT(VARCHAR,GETDATE(),112) + 'DV00001'
			WHEN @id >= 0 and @id < 9 THEN CONVERT(VARCHAR,GETDATE(),112) + 'DV0000' + CONVERT(CHAR, CONVERT(INT, @id) + 1)
			WHEN @id >= 9 THEN CONVERT(VARCHAR,GETDATE(),112) + 'DV000' + CONVERT(CHAR, CONVERT(INT, @id) + 1)
			WHEN @id >= 99 THEN CONVERT(VARCHAR,GETDATE(),112) + 'DV00' + CONVERT(CHAR, CONVERT(INT, @id) + 1)
			WHEN @id >= 999 THEN CONVERT(VARCHAR,GETDATE(),112) + 'DV0' + CONVERT(CHAR, CONVERT(INT, @id) + 1)
			WHEN @id >= 9999 THEN CONVERT(VARCHAR,GETDATE(),112) + 'DV' + CONVERT(CHAR, CONVERT(INT, @id) + 1)
		END
	RETURN @id
END

1. Cập nhật những dòng bị trống cột mã tăng dần theo cái có sẵn:

declare @vitri int -- Vị trí những mã trống theo thứ tự tăng dần
declare @maxso int -- Mã hàng đang đánh số to nhất trên cột mã
declare @kytu nvarchar(30) -- Ký tự đầu của cột mã
declare @vattu int
--HH02672 Mã hàng đang đánh số to nhất trên cột mã
set @maxso = 2672
set @kytu=N'HH' 
declare bangvattu cursor for -- Bảng select ra những mã hàng bị trống cột Mã
--select ROW_NUMBER() OVER(ORDER BY ten ASC) AS vitri,ma,ten,vattu,donvi from vattu where ma='' and donvi=1 and vattu<>0
select ROW_NUMBER() OVER(ORDER BY ten ASC) AS vitri,vattu from vattu where ma='' and donvi=1 and vattu<>0
open bangvattu
Fetch next from bangvattu
	into @vitri,@vattu
While @@FETCH_STATUS=0
BEGIN
	print 'vitri: '+ CAST(@vitri as nvarchar)
	print 'Ma: '+@kytu+FORMAT(@maxso+@vitri,'00000')
	Update VatTu set ma=@kytu+FORMAT(@maxso+@vitri,'00000') where vattu=@vattu and donvi=1
	Fetch next from bangvattu
	into @vitri,@vattu
END

CLOSE bangvattu
DEALLOCATE bangvattu

--select ma,ten,VatTu,donvi from VatTu order by ma

1. Cập nhật lại mã đối tượng theo nhóm

declare @vitri int -- Vị trí những mã trống theo thứ tự tăng dần
declare @maxso int -- Mã hàng đang đánh số to nhất trên cột mã
declare @kytu nvarchar(30) -- Ký tự đầu của cột mã
declare @khachhang int

--HH02672 Mã hàng đang đánh số to nhất trên cột mã
set @maxso = 0
set @kytu=N'NCC.' 

declare bangkh cursor for -- Bảng select ra những mã hàng bị trống cột Mã
--select ROW_NUMBER() OVER(ORDER BY ten ASC) AS vitri,ma,ten,vattu,donvi from vattu where ma='' and donvi=1 and vattu<>0
select ROW_NUMBER() OVER(ORDER BY ten ASC) AS vitri,khachhang from khachhang where ma like 'NCC%' and donvi=1 and khachhang<>0
open bangkh
Fetch next from bangkh
	into @vitri,@khachhang
While @@FETCH_STATUS=0
BEGIN
	print 'vitri: '+ CAST(@vitri as nvarchar)
	print 'Ma: '+@kytu+FORMAT(@maxso+@vitri,'00000')
	Update khachhang set ma=@kytu+FORMAT(@maxso+@vitri,'00000'),modidate=getdate() where khachhang=@khachhang and donvi=1
	Fetch next from bangkh
	into @vitri,@khachhang
END

CLOSE bangkh
DEALLOCATE bangkh

1. update khachhang trống mã theo nhóm KH

---
declare @idMax int = 318
declare @kituDau nvarchar(10)='KH'
UPDATE T1 SET T1.MA= T2.MAMOI
--SELECT T1.*, T2.*
FROM 
(SELECT * FROM KHACHHANG) T1 INNER JOIN 
(
SELECT MA,TEN,KHACHHANG,
@kituDau + FORMAT( ROW_NUMBER() OVER(ORDER BY khachhang ASC) + @idMax, '0000000') AS MAMOI
FROM KHACHHANG
where khachhang<>0 and ma=''
)T2 ON T1.KHACHHANG = T2.KHACHHANG