SQL Server - STOCK BALANCE และ ACCUMULATE

เกริ่นหัวโพสว่า STOCK BALANCE และ ACCUMULATE เชื่อหลายท่านถ้าให้เขียนโปรแกรมหาเพื่อยอดคงเหลือของสต็อก หรือหายอดสะสม คงนั่งคิดและเขียนโปรแกรมกันยาวเหยียด แต่ถ้าเราใช้ความสามารถของ SQL Server แล้ว ขอบอกว่าสั้นนิดเดียว ช่วยลดเวลาเอาสมองไปคิดอย่างอื่นได้อีกมากมาย มาเริ่มกันเลย

การหายอดสต็อกคงเหลือ  (STOCK BALANCE)

สร้างตารางชั่่วคราวตัวอย่างทดสอบกันก่อน  (มีชื่อว่า #Test)

CREATE TABLE #Test    (

      id INT IDENTITY(1,1) NOT NULL,

      ItemCode CHAR(10) ,

      QtyIn MONEY ,

      QtyOut MONEY

    )

INSERT  INTO #Test

   VALUES  ( '1', 200, 0 ),

           ( '1', 250, 0 ),

           ( '1', 300, 0 ),

           ( '2', 150, 0 ),

           ( '2', 0, 150 ),

           ( '2', 300, 0 ),

           ( '2', 100, 0 )

-- เรียกดูข้อมูล

SELECT * FROM #Test

-- ทำการหายอดคงเหลือของสต็อก แสดงในลักษณะของการ์ดแสดงยอดคงเหลือ (Stock Card)

SELECT  * ,

        SUM(QtyIn - QtyOut) OVER ( PARTITION BY ItemCode ORDER BY ItemCode, id ) 

                 AS CurrBAL

FROM    #Test

ORDER BY ItemCode, id

จะได้ผลลัพธ์ดังภาพ

จะเห็นว่าการนำเอา OVER PARTITION BY มาใช้งานทำให้เราสามารถคำนวณยอดสต็อกออกมาได้อย่างง่ายดาย

การหายอดสะสม  (ACCUMULATE)

การคำนวณยอดสะสมเราก็มักจะโดยให้ทำรายงานหรีอเขียนโปรแกรมกันบ่อยๆ ซึ่งก็ไม่ยากอีกเช่นกัน มาลองศึกษาตามกันเลยครับ

CREATE TABLE #DrCr   (

      id INT IDENTITY(1,1) NOT NULL,

      ItemCode CHAR(10) ,

      Amt1 MONEY ,

      Amt2 MONEY

    )

INSERT  INTO #DrCr

   VALUES  ( '101', 200, 100 ),

           ( '101', 50,100 ),

           ( '101', 150, 100 ),

           ( '102', 550, 50 ),

           ( '102', 200, 150 ),

           ( '102', 10, 100 ),

           ( '102', 60, 300 )

ทีนี้เราจะนำเอายอดของ Amt1 บวกกับ Amt2 แล้วสะสมไปเรื่อยๆ โดยแบ่งตามกลุ่มของ ItemCode

SELECT id, ItemCode, Amt1, Amt2,
       SUM(Amt1+Amt2) OVER (PARTITION BY ItemCode
             ORDER BY ItemCode, id ROWS UNBOUNDED PRECEDING) AS AccuAmt
FROM #Accu

จะได้ผลลัพธ์ดังภาพ

จะเห็นว่ามีการนำเอา OVER PARTITION BY ItemCode -- คำสั่งนี้จะหมายถึงแบ่งกลุ่มตาม ItemCode
แล้วเรียงลำดับตาม ItemCode, id ด้วยคำสั่ง ORDER BY ItemCode, id
ROWS เป็นการกำหนดการคำนวณค่าที่เกิดขึ้นของแถวต่างๆ ที่เกี่ยวข้องให้กับแถวปัจจุบัน
UNBOUNDED PRECEDING เป็นการกำหนดให้เริ่มคำนวณต้้งแต่แถวแรกของกลุ่ม (PARTITION BY) จนถึงแถวปัจจุบัน

เอาละครับสำหรับการหา Stock Balance และ Accumulate ก็คงทำให้การทำงานสะดวกขึ้น
สวัสดีมีเวลาเยอะๆ