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



0 สมาชิก และ 1 บุคคลทั่วไป กำลังดูหัวข้อนี้

09 เม.ย. 61 , 16:56:17
อ่าน 1228 ครั้ง

Earth

สอบถามการQuery คำนวนค่า
« เมื่อ: 09 เม.ย. 61 , 16:56:17 »
สวัสดีค่ะ

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

 
โพสต์นี้ได้รับคำขอบคุณจาก: ekarat

10 เม.ย. 61 , 11:34:52
ตอบกลับ #1

Earth

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #1 เมื่อ: 10 เม.ย. 61 , 11:34:52 »
ตอนนี้คิดแนวทางโค้ดไว้ประมานนี้ค่ะ


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


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

 
โพสต์นี้ได้รับคำขอบคุณจาก: ekarat

10 เม.ย. 61 , 16:46:57
ตอบกลับ #2

สันติสุข

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #2 เมื่อ: 10 เม.ย. 61 , 16:46:57 »
สมมุติเทเบิล 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 เป็นศูนย์แทน
« แก้ไขครั้งสุดท้าย: 10 เม.ย. 61 , 16:58:33 โดย สันติสุข »
ช่วยพกถุงผ้า/ถุงพลาสติกใช้แล้วไปซื้อของเพื่อลดการใช้พลาสติก ขยะ รักษาสิ่งแวดล้อม และไม่ให้ภาวะโลกร้อนวิกฤติเร็วขึ้นกว่านี้
ช่วยคลิกโฆษณาข้างล่างนี้ เพื่อสนับสนุนเวปบอร์ดด้วยครับ
:nice day:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Earth

10 เม.ย. 61 , 17:06:02
ตอบกลับ #3

สันติสุข

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #3 เมื่อ: 10 เม.ย. 61 , 17:06:02 »
ชักงง ข้อมูลเรียงตามอะไรกันแน่ ดูเหมือนผลรวมจะไม่สนใจว่าเป็นสินค้าตัวไหน (ที่ลงท้ายด้วย 21-P, 31-P, 41-P ใช่หรือไม่) และลำดับวันที่ก่อนหลังเลย !!!
ช่วยพกถุงผ้า/ถุงพลาสติกใช้แล้วไปซื้อของเพื่อลดการใช้พลาสติก ขยะ รักษาสิ่งแวดล้อม และไม่ให้ภาวะโลกร้อนวิกฤติเร็วขึ้นกว่านี้
ช่วยคลิกโฆษณาข้างล่างนี้ เพื่อสนับสนุนเวปบอร์ดด้วยครับ
:nice day:
 

10 เม.ย. 61 , 17:12:04
ตอบกลับ #4

Earth

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #4 เมื่อ: 10 เม.ย. 61 , 17:12:04 »
ข้อมูลแยกตามItemID ค่ะ และลำดับวันที่ก่อน ไปหลัง
ตามรูปใหม่ด้านล่างค่ะ


 

10 เม.ย. 61 , 17:37:30
ตอบกลับ #5

สันติสุข

> > สอบถามการQuery คำนวนค่า
« ตอบกลับ #5 เมื่อ: 10 เม.ย. 61 , 17:37:30 »
ข้อมูลแยกตามItemID ค่ะ และลำดับวันที่ก่อน ไปหลัง
ตามรูปใหม่ด้านล่างค่ะ



งั้นตามคิวรี่ที่ผมให้ไปก็ควรจะให้ผลลัพธ์ที่ถูกต้องแล้วครับ
ช่วยพกถุงผ้า/ถุงพลาสติกใช้แล้วไปซื้อของเพื่อลดการใช้พลาสติก ขยะ รักษาสิ่งแวดล้อม และไม่ให้ภาวะโลกร้อนวิกฤติเร็วขึ้นกว่านี้
ช่วยคลิกโฆษณาข้างล่างนี้ เพื่อสนับสนุนเวปบอร์ดด้วยครับ
:nice day:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Earth

11 เม.ย. 61 , 11:23:17
ตอบกลับ #6

Earth

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #6 เมื่อ: 11 เม.ย. 61 , 11:23:17 »
ถ้าต้องการให้เรียงตามItemID และ Date
โดยที่ให้กลุ่มของItemIDที่มีDate น้อยสุดขึ้นก่อนได้ไหมคะ
เช่น ต้องการให้เป็นแบบนี้ค่ะ
item  Date
B       1
B       2
B       3
A       2
A       4



 

11 เม.ย. 61 , 14:01:17
ตอบกลับ #7

สันติสุข

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #7 เมื่อ: 11 เม.ย. 61 , 14:01:17 »
คุณไปลองทดสอบดูผลลัพธ์นะครับว่าถูกต้องหรือไม่

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

ช่วยพกถุงผ้า/ถุงพลาสติกใช้แล้วไปซื้อของเพื่อลดการใช้พลาสติก ขยะ รักษาสิ่งแวดล้อม และไม่ให้ภาวะโลกร้อนวิกฤติเร็วขึ้นกว่านี้
ช่วยคลิกโฆษณาข้างล่างนี้ เพื่อสนับสนุนเวปบอร์ดด้วยครับ
:nice day:
 

18 เม.ย. 61 , 13:33:20
ตอบกลับ #8

Earth

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #8 เมื่อ: 18 เม.ย. 61 , 13:33:20 »
ทำตามแล้วค่ะ แต่เหมือนเรียงวันที่ แต่ไม่ได้เรียงเดือนเลยค่ะ รูปแบบวันที่ตอน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;

 

18 เม.ย. 61 , 14:01:19
ตอบกลับ #9

Earth

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #9 เมื่อ: 18 เม.ย. 61 , 14:01:19 »
จากที่ไม่ได้เรียงเดือนตาม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;

 

18 เม.ย. 61 , 14:34:19
ตอบกลับ #10

สันติสุข

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #10 เมื่อ: 18 เม.ย. 61 , 14:34:19 »
ฟิลด์ Date ใน StockMonthly_query อย่าไปกำหนดรูปแบบนะครับ มันต้องการ Data Type เป็น DATE ถ้าไปกำหนดรูปแบบแล้ว มันมีความเป็นไปได้ที่จะมี Data Type เป็น TEXT จะทำให้การเรียงข้อมูลทำตามแบบ TEXT แทน

ส่วนถ้าต้องการแสดงเป็น วัน-เดือน ค่อยมาสร้างฟิลด์ ที่ไปกำหนดรูปแบบใน SQL เพิ่มเติม เป็น Select ...+...-...,  Format$(T1.DATE, " dd-mmm") As FormatD., * From ...
« แก้ไขครั้งสุดท้าย: 18 เม.ย. 61 , 14:45:03 โดย สันติสุข »
ช่วยพกถุงผ้า/ถุงพลาสติกใช้แล้วไปซื้อของเพื่อลดการใช้พลาสติก ขยะ รักษาสิ่งแวดล้อม และไม่ให้ภาวะโลกร้อนวิกฤติเร็วขึ้นกว่านี้
ช่วยคลิกโฆษณาข้างล่างนี้ เพื่อสนับสนุนเวปบอร์ดด้วยครับ
:nice day:
 
โพสต์นี้ได้รับคำขอบคุณจาก: ekarat

18 เม.ย. 61 , 15:02:14
ตอบกลับ #11

ekarat

  • สมาชิกไท.Access
  • กระทู้: 16

  • ขอบคุณ ไท.Access

    • ดูรายละเอียด

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #11 เมื่อ: 18 เม.ย. 61 , 15:02:14 »
อ่านดูแล้ว เข้าใจยากนะครับ

 

18 เม.ย. 61 , 15:28:18
ตอบกลับ #12

Earth

> > สอบถามการQuery คำนวนค่า
« ตอบกลับ #12 เมื่อ: 18 เม.ย. 61 , 15:28:18 »
ใน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 ...

 

18 เม.ย. 61 , 15:47:09
ตอบกลับ #13

สันติสุข

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #13 เมื่อ: 18 เม.ย. 61 , 15:47:09 »
คิวรี่ที่1 ไม่ต้องมี Format( )  ให้มาใส่ในคิวรี่ที่ 2 ที่ผมบอกไปครั้งล่าสุด
ช่วยพกถุงผ้า/ถุงพลาสติกใช้แล้วไปซื้อของเพื่อลดการใช้พลาสติก ขยะ รักษาสิ่งแวดล้อม และไม่ให้ภาวะโลกร้อนวิกฤติเร็วขึ้นกว่านี้
ช่วยคลิกโฆษณาข้างล่างนี้ เพื่อสนับสนุนเวปบอร์ดด้วยครับ
:nice day:
 
โพสต์นี้ได้รับคำขอบคุณจาก: Earth

18 เม.ย. 61 , 16:04:48
ตอบกลับ #14

ekarat

  • สมาชิกไท.Access
  • กระทู้: 16

  • ขอบคุณ ไท.Access

    • ดูรายละเอียด

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #14 เมื่อ: 18 เม.ย. 61 , 16:04:48 »
ช่วยจับหน้าต่างในการรัน query ให้ดูหน่อยสิครับ มันมีหน้าต่างเหมือน query analyzer sql เหรอครับ

 

18 เม.ย. 61 , 16:10:11
ตอบกลับ #15

Earth

> สอบถามการQuery คำนวนค่า
« ตอบกลับ #15 เมื่อ: 18 เม.ย. 61 , 16:10:11 »
อ่อ ขอบคุณมากค่ะ ได้แล้วค่ะ

 


บอร์ดเรียนรู้ Access สำหรับคนไทย