สอบถามการ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]

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

[Earth]

หนูเอาQueryที่ได้มา make table ใหม่ค่ะ เพราะข้อมูลแสดงช้ามาก
พอนำมาmake table ใหม่ ก็แสดงผลitem ตามวันที่และเดือนภาษาอังกฤษปกติ
แต่พอนำtableที่make ใหม่ มาสร้าง query อีกทีเพื่อไปใช้ต่อ วันที่เดือนกลับไม่เรียงเหมือนเดิมค่ะ

อยากทราบว่า สามารถนำโค้ดที่อาจารย์ทำ มาเพิ่มตรงส่วนไหนของsqlนี้คะ

SELECT Monthly_FG.Type2, Monthly_FG.[GR/GI], Monthly_FG.[m^2], Monthly_FG.[m^3], Monthly_FG.Stack, Monthly_FG.[U Price_1 m^2], Monthly_FG.[U Price_2 m^3], Monthly_FG.Date, Sum(Monthly_FG.Received) AS SumOfReceived, Sum(Monthly_FG.Shipment) AS SumOfShipment, Monthly_FG.[IN TOTAL (m3)], Monthly_FG.[OUT TOTAL (m3)], Sum(Monthly_FG.[Good Receive]) AS [SumOfGood Receive], Sum(Monthly_FG.[Good Issue]) AS [SumOfGood Issue]

FROM Monthly_FG

GROUP BY Monthly_FG.Type2, Monthly_FG.[GR/GI], Monthly_FG.[m^2], Monthly_FG.[m^3], Monthly_FG.Stack, Monthly_FG.[U Price_1 m^2], Monthly_FG.[U Price_2 m^3], Monthly_FG.Date, Monthly_FG.[IN TOTAL (m3)], Monthly_FG.[OUT TOTAL (m3)];



ต้องการให้ Order by Date และType2 ค่ะ โดยเรียงวันเดือนตามรูปแบบ 1-Feb> ..... 2-Mar

[สันติสุข]

อ่านแล้วไม่เข้าใจเลยครับ

1. หนูเอาQueryที่ได้มา...  คิวรี่ไหน
2. เพราะข้อมูลแสดงช้ามาก ... น่าเป็นเพราะเทเบิลไม่ได้สร้าง index ซึ่งประกอบด้วยฟิลด์ที่อยู่ใน WHERE ครับ
3. อยากทราบว่า สามารถนำโค้ดที่อาจารย์ทำ มาเพิ่มตรงส่วนไหนของsqlนี้คะ  ... ไม่เข้าใจว่าที่ผมตอบไปก่อนหน้า โค้ดอะไรตรงส่วนไหนที่สัมพันธ์กับคิวรี่ที่เขียนมาใหม่นี้
4. ต้องการให้ Order by Date และType2 ค่ะ โดยเรียงวันเดือนตามรูปแบบ 1-Feb> ..... 2-Mar ... ก็สั่ง ORDER BY Monthly_FG.Date,  Monthly_FG.Type2 เท่านั้นครับ แต่ฟิลด์ Date ต้องมี Data Type เป็น DATE ครับ

[Earth]

Query ที่ได้มาหน้าตาเป็นแบบนี้ค่ะ

โดยใส่คำสั่งเรียงวันที่ตามโค้ดที่อาจารย์ได้ให้คือ

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

ที่ตำแหน่งสีแดง เราเปลี่ยนแหล่งข้อมูลจากเทเบิล 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;

จากนั้นหนูเลยเอาQuery นี้ไปMake tableใหม่ค่ะ ชื่อTable Monthly_FG

เปิดตารางดูเป็นแบบด้านล่างคือเรียงวันที่ 2-Marไปอยุ่ท้ายเหมือนเดิม ถูกต้อง

แต่พอไปเปิดโครงสร้างTable Monthly_FG ที่ได้จาก Query make table ตรงDate มันเลยเปลี่ยนชนิดเป็นText

ทีนี่เลยจะเอาTable Monthly_FG  มาทำQueryกลับไม่เรียงค่ะ

ถ้าอย่างนี้ มีกรณีไหนที่ Date เป็นTextแล้วให้เรียงวันที่ตามเดือนได้ไหมคะ
หรือมีวิธีไหนที่ ตอน Query make table แล้วให้ Date เป็นชนิด Date/time เหมือนข้อมูลแรกไหมคะ

[สันติสุข]

หลักๆก็คือ อย่าเอาวันที่ที่ผ่าน Format$(  ) มาสร้างเป็นเทเบิล  ในคำตอบที่ #13 ที่บอกว่าคิวรี่ที่ 2 ให้ใส่ Format$( ) เราก็ไม่ต้องใส่ เพราะเราจะนำเอาคิวรี่นี้ไปสร้างเทเบิลอีกที  คุณก็เลือกฟิลด์วันที่ตรงๆไปใช้สร้างแทน ถ้าต้องการแสดง วว-ดดด ก็ให้ทำเมื่อจะแสดงผลลัพธ์จากคิวรี่สุดท้ายที่อ่านจากเทเบิลที่สร้างมาใหม่ครับ

[Earth]

ขอบคุณมากค่ะ