[SQL] 不足位數補上零 "0" & 編碼方式[NG-年度-流水號] & 編碼方式[CS年度月份流水號]

編碼方式：
NG-年度-流水號

範例一：
假設目前編號最大號 NG-13-037

SQL：找出最大值+1
SELECT top 1 left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) 報表編號 FROM [瑕疵異狀表] WHERE 報表編號 like 'NG-' + Right(year(GetDate()),2) + '-%'  ORDER BY 報表編號 DESC;

insert into [瑕疵異狀表] (報表編號) SELECT top 1 left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) 報表編號 FROM [瑕疵異狀表] WHERE 報表編號 like 'NG-' + Right(year(GetDate()),2) + '-%'  ORDER BY 報表編號 DESC;

結果：
NG-13-038

範例二：
假設今年為 2014 而最大號為 NG-13-038
若是要產生今年的第一筆 NG-14-001 
接續產生今年(2014)第二筆 NG-14-002 
第N筆 NG-14-N...

SQL：承一更改如下
select top 1 case when convert(nvarchar(10),(select count(報表編號) from [瑕疵異狀表] where substring(報表編號,4,2)=right(year(getdate()),2))) = '0' then 'NG-' + right(year(getdate()),2) + '-001' else  left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) end 報表編號 from [瑕疵異狀表] order by  substring(報表編號,4,2) desc,right(報表編號,3) desc; --where 報表編號 like 'NG-' + Right(year(GetDate()),2) + '-%'  ORDER BY 報表編號 DESC;

insert into [瑕疵異狀表] (報表編號) select top 1 case when convert(nvarchar(10),(select count(報表編號) from [瑕疵異狀表] where substring(報表編號,4,2)=right(year(getdate()),2))) = '0' then 'NG-' + right(year(getdate()),2) + '-001' else  left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) end 報表編號 from [瑕疵異狀表] order by  substring(報表編號,4,2) desc,right(報表編號,3) desc; --where 報表編號 like 'NG-' + Right(year(GetDate()),2) + '-%'  ORDER BY 報表編號 DESC;

結果：
NG-14-001

範例三：承二
當資料有 NG-13-001 ~ n, NG-15-001 ~ n...
藍色因條件在, 
調整日期 2013年, 則無法產生 NG-13-(n+1), 而是產出 NG-15-(n+1)
調整日期 2014年, 則正常產生 NG-14-001
調整日期 2016年, 則正常產生 NG-16-001

當資料有 NG-13-001 ~ n, NG-15-001 ~ n...
綠色因條件在, 
調整日期 2013年, 則正常產生 NG-13-(n+1)
調整日期 2014年, 則無法產生 NG-14-001
調整日期 2016年, 則無法產生 NG-16-001

當資料有 NG-13-001 ~ n, NG-14-001 ~ n, NG-15-001 ~ n)
藍色因條件在, 
調整日期 2013年, 則無法產生 NG-13-(n+1), 而是產出 NG-15-(n+1)
調整日期 2014年, 則無法產生 NG-14-(n+1), 而是產出 NG-15-(n+1)
調整日期 2016年, 則正常產生 NG-16-001

當資料有 NG-13-001 ~ n, NG-15-001 ~ n...
綠色因條件在, 
調整日期 2013年, 則正常產生 NG-13-(n+1)
調整日期 2014年, 則正常產生 NG-14-(n+1)
調整日期 2016年, 則無法產生 NG-16-001

SQL：承二更改如下
select top 1 case when convert(nvarchar(10),(select count(報表編號) from [瑕疵異狀表] where substring(報表編號,4,2)=right(year(getdate()),2))) = '0' then 'NG-' + right(year(getdate()),2) + '-001' else   left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) end 報表編號 from [瑕疵異狀表] where substring(報表編號,4,2) <= right(year(getdate()),2)  order by  substring(報表編號,4,2) desc,right(報表編號,3) desc;

insert into [瑕疵異狀表] (報表編號) select top 1 case when convert(nvarchar(10),(select count(報表編號) from [瑕疵異狀表] where substring(報表編號,4,2)=right(year(getdate()),2))) = '0' then 'NG-' + right(year(getdate()),2) + '-001' else   left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) end 報表編號 from [瑕疵異狀表] where substring(報表編號,4,2) <= right(year(getdate()),2)  order by  substring(報表編號,4,2) desc,right(報表編號,3) desc;

試試資料依然正確...
NG-13-001 ~ n, NG-15-001 ~ n, NG-17-001 ~ n (無14年及16年的資料)

﹝另一解﹞ CS150210 + 1 = CS150211
:::資料最大值 CS150210 故取出時後二位數+1 = CS150211
:::若該年份該月份無資料列，則CS該年該月001

select top 1 case when (select count(文號) from [DayCsycDB].[dbo].[公文基本資料表]
where 文號 like 'CS'+right(100+year(getdate()),2)+right(100+month(getdate()),2)+'%') =0 then
'CS'+right(100+year(getdate()),2)+right(100+month(getdate()),2)+'01'
else left(文號,6)+convert(nvarchar(2),(right(文號,2)+1)) end 文號
 from [DayCsycDB].[dbo].[公文基本資料表]
where 文號 like 'CS'+right(100+year(getdate()),2)+right(100+month(getdate()),2)+'%'  ORDER BY Right(文號,2) DESC;

參考：
--select Right(100+month(GetDate()),2)
--select datepart(yyyy,getdate())
--select Right(year(GetDate()),2)
--select REPLICATE('0',4-LEN(substring('123',2,2)))+substring('123',2,2)
--select replicate('0',2)+convert(nvarchar(1),substring('123',3,1)+1)

--select  報表編號 from [瑕疵異狀表] where 報表編號 like '__-' + right(year(getdate()),2) + '-___' order by right(報表編號,3) desc;

--SELECT top 1 報表編號,count(報表編號) FROM [瑕疵異狀表] WHERE 報表編號 like 'NG-' + Right(year(GetDate()),2) + '-%' group by  報表編號 order by 報表編號 desc;

--SELECT top 1 left(報表編號,6) + replicate('0',3-len(substring(報表編號,7,3)+1))+convert(nvarchar(3),substring(報表編號,7,3)+1) 報表編號 FROM [瑕疵異狀表] WHERE 報表編號 like 'NG-' + Right(year(GetDate()),2) + '-%'  ORDER BY 報表編號 DESC;

--select 'CS' + right(right(12303+101,2),2)

--SELECT CASE WHEN MAX(財產電腦單號) IS NULL 

THEN 'FA' + right(year(getdate())-1911,3) + RIGHT(REPLICATE('0', 2) + CAST(month(getdate()) as NVARCHAR), 2) + '001' 

ELSE 'FA' + right(year(getdate())-1911,3) + RIGHT(REPLICATE('0', 2) + CAST(month(getdate()) as NVARCHAR), 2) + replicate('0',3-len(substring(MAX(財產電腦單號),8,3)+1)) + convert(nvarchar(3),substring(MAX(財產電腦單號),8,3)+1)  END 

FROM C000財產基本資料表Tmp 

--WHERE substring(財產電腦單號,3,5) <= right(year(getdate())-1911,3) + RIGHT(REPLICATE('0', 2) + CAST(month(getdate()) as NVARCHAR), 2)

--SELECT CASE WHEN MAX(財產電腦單號) IS NULL 

THEN 'FA' + right(year(getdate())-1911,3) + RIGHT(REPLICATE('0', 2) + CAST(month(getdate()) as NVARCHAR), 2) + '001' 

ELSE 'FA' + right(year(getdate())-1911,3) + RIGHT(REPLICATE('0', 2) + CAST(month(getdate()) as NVARCHAR), 2) + replicate('0',3-len(substring(MAX(財產電腦單號),8,3)+1)) + convert(nvarchar(3),substring(MAX(財產電腦單號),8,3)+1)  END 

FROM C000財產基本資料表 

WHERE substring(財產電腦單號,3,5) <= right(year(getdate())-1911,3) + RIGHT(REPLICATE('0', 2) + CAST(month(getdate()) as NVARCHAR), 2)

[VB.NET] String Format 格式化, 自動補零, 不足位元補零...
Mssql的字符字段如何按位数补零
SQL 字串補0
PLSQL & T-SQL - 字串不足數補零
將不足的位數補零
MS SQL 位數不足補0範例
[Google 搜尋] mssql 位數 補零
SQL函數 查詢SQL資料欄位相符的字串
MSSql 中Charindex ,Substring的使用
CHARINDEX (Transact-SQL)

[MSSQL]取得兩位數的月份或日期
--月
Select Right(100+Month(GetDate()),2)
--日
Select Right(100+Day(GetDate()),2)

Return a value if no rows are found SQL
Return a default value if no rows found
How to Assign a Default Value if No Rows Returned from the Select Query
Getting SELECT to return a constant value even if zero rows match