要求:根据年份后2位及月份2位作为前面的前缀,后缀是要求加3位流水号。
且每年或每月流水号从0开始计算。
数据库用的是HANA Studio数据库
SELECT TT.YY||TT.MM||
CASE
WHEN TT.YY<>TT.MAXYY OR TT.MM<> TT.MAXMM THEN '001' ELSE TT.XH END XH
FROM (
SELECT T0.YY,T0.MM,
CASE WHEN T0.XH IS NULL THEN '001' ELSE T0.XH END AS XH,
T0.MAXID,
CASE WHEN T0.MAXYY IS NULL THEN T0.YY ELSE T0.MAXYY END AS MAXYY,
CASE WHEN T0.MAXMM IS NULL THEN T0.MM ELSE T0.MAXMM END AS MAXMM
FROM
(
SELECT
SUBSTRING(TO_CHAR(CURRENT_DATE),3,2) AS YY,
SUBSTRING(TO_CHAR(CURRENT_DATE),6,2) AS MM,
SUBSTRING(TO_CHAR(MAX("PrjCode")+1),5,3) AS XH,
MAX("PrjCode") AS MAXID,
SUBSTRING(TO_CHAR(MAX("PrjCode")),1,2) AS MAXYY,
SUBSTRING(TO_CHAR(MAX("PrjCode")),3,2) AS MAXMM
FROM OPRJ where length("PrjCode")>6
)T0
)TT