สอบถามการQuery คำนวนค่า

[Earth]

สวัสดีค่ะ

อยากทราบวิธีเขียนQuery หรือ SQL เพื่อคำนวนค่าตามรูปที่อธิบายด้านล่างค่ะ
ต้องการหาค่า ค่าTotal Amount โดยคำนวนจาก
Total Amount(รายการของวันแรก จะยกเอาฟิลด์ Stock Before มาคิด) = StockBefore + Received
Total Amount(ต่อมา จะเอาฟิวด์ตัวเองมาคิด) = Total Amount(วันก่อนหน้า) + Received(วันนี้) - Shipment(ของวันก่อนหน้า)

[Earth]

ตอนนี้คิดแนวทางโค้ดไว้ประมานนี้ค่ะ


IIf(min([Date]) ,[TotalBefore]+[IN], [Total Amount](วันก่อนหน้า) +[IN]-[OUT](วันก่อนหน้า)) AS Total Amount


อธิบาย
 ถ้าเป็นวันที่น้อยสุดของItemนั้น
 ให้ทำ [TotalBefore]+[IN] ถ้าไม่ใช่ให้ทำ >>นำค่า[Total Amount](วันก่อนหน้า) +[IN]-[OUT](วันก่อนหน้า)

[สันติสุข]

สมมุติเทเบิล T มีฟิลด์รายการสินค้า ItemCD, วันที่ D, ยอดยกมา BFQty, จำนวนรับเข้าในวันนั้น InQty, จำนวนจ่ายออกในวันนั้น OutQty  และมี Primary Key เป็น ItemCD, D

คิวรี่คือ
SELECT T1.BFQty+(select nz(sum(T2.InQty),0) from T as T2 where T2.ItemCD = T1.ItemCD and T2.D <= T1.D)-(select nz(sum(T2.OutQty),0) from T as T2 where T2.ItemCD = T1.ItemCD and T2.D < T1.D) AS TotalQty, *
FROM T AS T1
ORDER BY T1.ItemCD, T1.D;

ความหมายคือ

T1.BFQty
ยอดยกมา

+ (select nz(sum(T2.InQty),0) from T as T2 where T2.ItemCD = T1.ItemCD and T2.D <= T1.D)
+ ผมรวมของจำนวนรับเข้าสำหรับสินค้าเดียวกันตั้งแต่วันแรกจนถึงวันนั้น (วันของเรคอร์ดนั้น)

(select nz(sum(T2.OutQty),0) from T as T2 where T2.ItemCD = T1.ItemCD and T2.D < T1.D)
-  ผมรวมของจำนวนจ่ายออกสำหรับสินค้าเดียวกันตั้งแต่วันแรกจนถึงก่อนวันนั้น (วันของเรคอร์ดนั้น)

ที่ต้องใส่ฟังก์ชั่น nz( ) เพราะถ้าผลรวมนั้นไม่มีมาก่อน เช่นจำนวนจ่ายออกของวันที่น้อยกว่าวันแรก ซึ่งไม่มีค่า จะไม่ใช่ศูนย์ แต่เป็น NULL ซึ่งพอเอามาคำนวนกับค่าอื่นแล้ว จะให้ผลลัพธ์ซึ่งก็คือช่อง TotalQty ของเรคอร์ดนั้นเป็น NULL ไปด้วย ดังนั้นถ้าต้องการให้เป็นศูนย์ เราจึงต้องใส่ฟังก์ชั่น nz(... , 0) เพื่อแปลงค่า NULL เป็นศูนย์แทน

[สันติสุข]

ชักงง ข้อมูลเรียงตามอะไรกันแน่ ดูเหมือนผลรวมจะไม่สนใจว่าเป็นสินค้าตัวไหน (ที่ลงท้ายด้วย 21-P, 31-P, 41-P ใช่หรือไม่) และลำดับวันที่ก่อนหลังเลย !!!

[Earth]

ข้อมูลแยกตามItemID ค่ะ และลำดับวันที่ก่อน ไปหลัง
ตามรูปใหม่ด้านล่างค่ะ

[สันติสุข]

ข้อมูลแยกตามItemID ค่ะ และลำดับวันที่ก่อน ไปหลัง
ตามรูปใหม่ด้านล่างค่ะ

งั้นตามคิวรี่ที่ผมให้ไปก็ควรจะให้ผลลัพธ์ที่ถูกต้องแล้วครับ

[Earth]

ถ้าต้องการให้เรียงตามItemID และ Date
โดยที่ให้กลุ่มของItemIDที่มีDate น้อยสุดขึ้นก่อนได้ไหมคะ
เช่น ต้องการให้เป็นแบบนี้ค่ะ
item  Date
B       1
B       2
B       3
A       2
A       4

[สันติสุข]

คุณไปลองทดสอบดูผลลัพธ์นะครับว่าถูกต้องหรือไม่

ที่ตำแหน่งสีแดง เราเปลี่ยนแหล่งข้อมูลจากเทเบิล T ธรรมดาๆที่เรียกในชื่อ T1  มาเป็น T ที่เพิ่มฟิลด์วันที่น้อยที่สุดของสินค้าตัวเดียวกันโดยเรียกชื่อว่า MinD และเรียกรวมทั้งหมดเป็น T1 เหมือนเดิม

ส่วนการจัดเรียงในตำแหน่งสีฟ้า ก็แทรก MinD เข้าเป็นฟิลด์แรกสุดของ Ordered By ของเดิมครับ

SELECT T1.BFQty
+(select nz(sum(T2.InQty),0) from T as T2 where T2.ItemCD = T1.ItemCD and T2.D <= T1.D)
-(select nz(sum(T2.OutQty),0) from T as T2 where T2.ItemCD = T1.ItemCD and T2.D < T1.D) AS TotalQty, *
FROM (SELECT T2.*, (SELECT MIN(T3.D) FROM T AS T3 WHERE T3.ItemCD = T2.ItemCD) as MinD FROM T AS T2) AS T1
ORDER BY T1.MinD, T1.ItemCD, T1.D;

[Earth]

ทำตามแล้วค่ะ แต่เหมือนเรียงวันที่ แต่ไม่ได้เรียงเดือนเลยค่ะ รูปแบบวันที่ตอนimportเข้ามาเป็น 02/02/2018
แต่มาทำFormatเป็น 02/Feb/2018 ข้อมูลได้แบบนี้ค่ะ



SQL

SELECT T1.[Stock Before]+(select nz(sum(T2.Received),0) from StockMonthly_Query as T2
where T2.ItemID = T1.ItemID and T2.Date <= T1.Date)-(select nz(sum(T2.Shipment),0) from StockMonthly_Query as T2 where T2.ItemID = T1.ItemID and T2.Date < T1.Date) AS TotalAmount, *
FROM  (SELECT T2.*, (SELECT MIN(T3.Date) FROM StockMonthly_Query AS T3 WHERE T3.ItemID = T2.ItemID) as MinD FROM StockMonthly_Query AS T2) As T1

ORDER BY T1.MinD, T1.ItemID, T1.Date;

[Earth]

จากที่ไม่ได้เรียงเดือนตามFormat เดือนภาษาอังกฤษ เพิ่งมาเปลี่ยนเดือนให้เป็นรูปแบบนี้ค่ะ
ซึ่งก่อนหน้านี้ก็ไม่สามารถเรียงได้ อยากทราบวิธีเรียงข้อมูลตามวันที่และเดือนที่เป็นรูปแบบ 01/Febค่ะ

ทำตามแล้วค่ะ แต่เหมือนเรียงวันที่ แต่ไม่ได้เรียงเดือนเลยค่ะ รูปแบบวันที่ตอนimportเข้ามาเป็น 02/02/2018
แต่มาทำFormatเป็น 02/Feb/2018 ข้อมูลได้แบบนี้ค่ะ



SQL

SELECT T1.[Stock Before]+(select nz(sum(T2.Received),0) from StockMonthly_Query as T2
where T2.ItemID = T1.ItemID and T2.Date <= T1.Date)-(select nz(sum(T2.Shipment),0) from StockMonthly_Query as T2 where T2.ItemID = T1.ItemID and T2.Date < T1.Date) AS TotalAmount, *
FROM  (SELECT T2.*, (SELECT MIN(T3.Date) FROM StockMonthly_Query AS T3 WHERE T3.ItemID = T2.ItemID) as MinD FROM StockMonthly_Query AS T2) As T1

ORDER BY T1.MinD, T1.ItemID, T1.Date;

[สันติสุข]

ฟิลด์ Date ใน StockMonthly_query อย่าไปกำหนดรูปแบบนะครับ มันต้องการ Data Type เป็น DATE ถ้าไปกำหนดรูปแบบแล้ว มันมีความเป็นไปได้ที่จะมี Data Type เป็น TEXT จะทำให้การเรียงข้อมูลทำตามแบบ TEXT แทน

ส่วนถ้าต้องการแสดงเป็น วัน-เดือน ค่อยมาสร้างฟิลด์ ที่ไปกำหนดรูปแบบใน SQL เพิ่มเติม เป็น Select ...+...-...,  Format$(T1.DATE, " dd-mmm") As FormatD., * From ...

[ekarat]

อ่านดูแล้ว เข้าใจยากนะครับ

[Earth]

ในTableที่import เข้ามาตั้งแต่แรก หนูเลือกชนิดเป็น Date/Timeค่ะ ข้อมูลที่เข้าตารางมาเป็น 01/02/2018
พอมาทำ Queryขั้นที่ 1 เปลียนFormat date ใน SQL เป็น
Format(Receiving_SUM_month.DateIn, 'dd-MMM') AS [Date]  จะได้ 01/Feb

จากนั้นนำมาทำQuery sql ขั้นที่ 2ต่อ ตามที่อาจารย์แนะนำมาข้างต้นค่ะ

แสดงว่าไม่มีวิธีที่จะเรียงตามเดือนภาษาอังกฤษได้เลยหรอคะ

ฟิลด์ Date ใน StockMonthly_query อย่าไปกำหนดรูปแบบนะครับ มันต้องการ Data Type เป็น DATE ถ้าไปกำหนดรูปแบบแล้ว มันมีความเป็นไปได้ที่จะมี Data Type เป็น TEXT จะทำให้การเรียงข้อมูลทำตามแบบ TEXT แทน

ส่วนถ้าต้องการแสดงเป็น วัน-เดือน ค่อยมาสร้างฟิลด์ ที่ไปกำหนดรูปแบบใน SQL เพิ่มเติม เป็น Select ...+...-...,  Format$(T1.DATE, " dd-mmm") As FormatD., * From ...

[สันติสุข]

คิวรี่ที่1 ไม่ต้องมี Format( )  ให้มาใส่ในคิวรี่ที่ 2 ที่ผมบอกไปครั้งล่าสุด

[ekarat]

ช่วยจับหน้าต่างในการรัน query ให้ดูหน่อยสิครับ มันมีหน้าต่างเหมือน query analyzer sql เหรอครับ

[Earth]

อ่อ ขอบคุณมากค่ะ ได้แล้วค่ะ