Query Generate Running Key

ผมขอโพสแชร์ความรู้ในการ Generate Running Key ให้ทุกคนได้รู้ เผื่อจะนำไปใช้ประโยชน์ได้บ้าง

บางครั้งการทำงานอาจจะโจทย์ว่าจะต้อง Gen Key เช่น หมายเลข PO ในรูปแบบ String

เช่น ต้อง Gen Po Number ใน Format PO-YYMMNNNNNN

PO - Fix
YY - ปีสองตัวท้าย เช่น ปัจจุบัน 2019 เราต้องการแค่ 19
MM - เดือนปัจจุบัน 2 หลัก เช่น 09
NNNNNN - ตัวเลข 6 หลัก

ตัวอย่าง
PO-1909000001
PO-1909000002
PO-1909000003
PO-1909000004
PO-1909000005

ตัวเลข NNNNNN จะถูกนับใหม่ทุกๆ เดือน หมายความว่าใน 1 เดือน จะมีเลขที่เป็นไปได้คือ
000001 - 999999
ความยาวนี้ก็แล้วแต่การออกแบบของแต่ละระบบของแต่ละบริษัท ก็ว่ากันไป

สมมติว่าใน Database มี Table ชื่อ PurchaseOrder
มี Field
PO เป็น varchar(20)
CREATE_DATE เป็น DateTime

Query เพื่อ Generate PO คือ
(MS-SQL)


select 'PO-' + RIGHT(CONVERT(char(4),YEAR(GETDATE())),2) + RIGHT('00'+CONVERT(varchar(2),MONTH(GETDATE())),2) + RIGHT('000000'+convert(varchar(6), ISNULL(MAX(CONVERT(integer, (RIGHT(PO, 6)))),0)+1),6)
from PurchaseOrder
where YEAR(CREATE_DATE) = YEAR(GETDATE())
and MONTH(CREATE_DATE) = MONTH(GETDATE())

ขออธิบายเพิ่มเติม เป็นส่วนๆ นะครับ เมื่อแยกเป็นส่วนๆ จะทำให้เข้าใจง่ายขึ้น ว่าทำไม Query ต้องเขียนแบบนั้น

เริ่มจากตำแหน่ง YY

ทำไม Query ถึงต้องเขียนว่า

RIGHT(CONVERT(char(4),YEAR(GETDATE())),2)

ขั้นตอนวิธีคิด มาจาก

Step1 ตำแหน่ง YY

select YEAR(GETDATE()) 

ซึ่ง Function GETDATE() บน MS-SQL คือจะได้เวลาปัจจุบัน ของเครื่อง Server

และเมื่อมาใส่ YEAR() ก็จะได้ปีปัจจุบัน

select YEAR(GETDATE())

ผลจากการรันก็จะได้ 2019 

*หมายเหตุ บน server ผมใช้ DateTime แบบฝรั่ง เป็น ค.ศ.

เมื่อเราได้ 2019 มาแล้ว แต่โจทย์เราต้องการแค่ 2 ตัวท้าย

เราต้องแปลง 2019 ค่าที่ได้มาจาก YEAR() ไปเป็น string ก่อน แล้วค่อยใช้ function ตัด string

แปลงปีเป็น string

select CONVERT(char(4),YEAR(GETDATE()))

ตัด string ทางขวามา 2 ตำแหน่ง

select RIGHT(CONVERT(char(4),YEAR(GETDATE())),2)

ผมไม่อธิบายลงลึกนะครับว่า function RIGHT ใช้งานอย่างไร

ผลก็จะได้ 19 จาก Query สุดท้าย เป็นอันจบเรื่องตำแหน่ง YY

Step2 ตำแหน่ง MM

select RIGHT('00'+CONVERT(varchar(2),MONTH(GETDATE())),2)

วิธีคิดก็จะคล้ายๆ ตำแหน่ง YY มีเพิ่มขึ้นมาก็คือก่อนจะตัด string ด้วย RIGHT 2 ตำแหน่งขวา ผมมีการต่อ string ไปก่อน

หลังจากใช้ function เพื่อได้เดือน ปัจจุบันคือ เดือน 9

ผมก็จะ '00'+'9'

ได้เป็น '009'

แล้วใช้ RIGHT('009', 2) 

จะได้เป็น '09'

ถ้าสมมติเป็นเดือน 12

ผลลัพธ์จะได้เป็น RIGHT('012', 2)

= 12 

คงพอนึกภาพออกนะครับ

Step3 ตำแหน่ง NNNNNN

select RIGHT('000000'+convert(varchar(6), ISNULL(MAX(CONVERT(integer, (RIGHT(PO, 6)))),0)+1),6)

from PurchaseOrder

where YEAR(DateSendFile) = YEAR(GETDATE())

and MONTH(DateSendFile) = MONTH(GETDATE())

Query นี้จะค่อนข้างซับซ้อนหน่อย จะค่อยๆ อธิบายทีละขั้นๆ

สิ่งที่ทำก็คือ ดึงข้อมูล MAX ของตำแหน่ง NNNNNN แล้วเอามา +1 แต่ถ้าได้เป็น NULL ก็จะแปลงเป็น 0 แล้ว + 1

Step 3.1

ดึงค่า NNNNNN แล้วแปลงเป็นตัวเลข

select CONVERT(integer, (RIGHT(PO, 6)))

from PurchaseOrder

where YEAR(CREATE_DATE) = YEAR(GETDATE())

and MONTH(CREATE_DATE) = MONTH(GETDATE())

Step 3.2

ดึงค่า MAX ของ NNNNNN

select MAX(CONVERT(integer, (RIGHT(PO, 6))))

from PurchaseOrder

where YEAR(CREATE_DATE) = YEAR(GETDATE())

and MONTH(CREATE_DATE) = MONTH(GETDATE())

Step 3.3

หาเลข PO ปัจจุบัน ว่า Run ถึงไหน

select ISNULL(MAX(CONVERT(integer, (RIGHT(PO, 6)))),0)

from PurchaseOrder

where YEAR(CREATE_DATE) = YEAR(GETDATE())

and MONTH(CREATE_DATE) = MONTH(GETDATE())

Query นี้จะมีผลลัพธ์ได้ 2 ทาง

1. ได้ค่า Max จริงๆ กรณีที่เคยมี PO แล้วในข้อมูล

2. ไม่ได้ค่า Max เพราะยังไม่มีข้อมูล เราจะแปลงค่า NULL ให้เป็น 0

Step 3.4

หลังจากได้ค่า PO ปัจจุบัน ก็ไม่ยากละ เราก็ +1 เพิ่มไปเป็นเลขถัดไป

select ISNULL(MAX(CONVERT(integer, (RIGHT(PO, 6)))),0)+1

from PurchaseOrder

where YEAR(CREATE_DATE) = YEAR(GETDATE())

and MONTH(CREATE_DATE) = MONTH(GETDATE())

Step 3.5

ตกแต่งผล MAX PO ให้เป็น 6 ตำแหน่ง NNNNNN

ตัวอย่าง '0000001'

เพราะผลจาก Step 3.4 มันจะได้เป็นตัวเลขหลังเดียว 1 หรือ 2 เป็นต้น

select RIGHT('000000'+CONVERT(VARCHAR(6), ISNULL(MAX(CONVERT(integer, (RIGHT(PO, 6)))),0)+1), 6)

from PurchaseOrder

where YEAR(CREATE_DATE) = YEAR(GETDATE())

and MONTH(CREATE_DATE) = MONTH(GETDATE())

Step 4

สุดท้ายก็เอามาประกอบกัน คือ Query นี้

select 'PO-' + RIGHT(CONVERT(char(4),YEAR(GETDATE())),2) + RIGHT('00'+CONVERT(varchar(2),MONTH(GETDATE())),2) + RIGHT('000000'+convert(varchar(6), ISNULL(MAX(CONVERT(integer, (RIGHT(PO, 6)))),0)+1),6)
from PurchaseOrder
where YEAR(CREATE_DATE) = YEAR(GETDATE())
and MONTH(CREATE_DATE) = MONTH(GETDATE())

ส่งท้าย

ผมหวังว่าผมจะอธิบายได้พอรู้เรื่องสำหรับคนที่มีพื้นฐานบ้างแล้ว แต่ถ้าสงสัยอย่างไร ลอง comment ถามดูนะครับ