ساخت query جهت موجودی انبار

سلام واحترام خدمت دوستان عزیز

در نمونه ای که گذاشتم ، 2 تیبل (ورودی / خروجی) موجود هست که میخایم با استفاده از ساخت یک کویری ، مقدار کل ورودی و خروجی بصورت تجمعی محاسبه بشه %

مثلا : کالای cpu i3 9100f ، طی دو مرحل (50 + 8) وارد انبار شده و طی سه مرحله (3 + 1 + 2) از انبار خارج شده.

چطور میتونیم با استفاده از کویری، مقادیر کل ورودی (58) و کل خروجی (6) و باقیمانده (52) رو بصورت تجمعی بدست بیاریم ؟

سلام و روز خوش

همیشه جدول ها و خروجی خواسته رو پیوست کنین و روی همون توضیح بدین،
سریعتر پاسخ میگیرین و شاید اصلا نیازی به پیوست دیتا هم نباشه:

ID	ItemCode	Description	InQty
1	1011	cpu i3 9100f	50
2	1012	cpu i3 1200f	20
3	1013	cpu i5 6500	30
4	1014	hdd 1tb sata	10
5	1015	hdd 2tb sata	12
6	1016	vga 4060 8gb	15
7	1017	vga 3050 8gb	18
8	1011	cpu i3 9100f	8
9	1012	cpu i3 1200f	5

ID	ItemCode	Description	OutQty
1	1011	cpu i3 9100f	3
2	1012	cpu i3 1200f	7
3	1013	cpu i5 6500	2
4	1014	hdd 1tb sata	5
5	1015	hdd 2tb sata	1
6	1016	vga 4060 8gb	4
7	1017	vga 3050 8gb	2
8	1011	cpu i3 9100f	1
9	1012	cpu i3 1200f	1
10	1013	cpu i5 6500	2
11	1014	hdd 1tb sata	3
12	1015	hdd 2tb sata	5
13	1016	vga 4060 8gb	1
14	1017	vga 3050 8gb	3
15	1011	cpu i3 9100f	2

به هر حال این یک مسئله ساده total query هست:

گرچه با یک کوئری هم میشه، ولی بهتر و ساده تر هست 2 کوئری جدا بسازین،
یکی برای ورودی که در اون روی itemcode گروپ میگیرین و روی inqty هم sum ،
و مشابه همین رو برای خروجی میسازین.

حالا این دو تا کوئری رو روی itemcode با هم join میکنین.

البته میتونین کد این دو کوئری رو در کوئری سوم جایگزین کنین و یک کوئری داشته باشین،
ولی واقعا مزیتی نمیبینم.

نقل قول:

---

نوشته شده توسط mazoolagh

به هر حال این یک مسئله ساده total query هست:

گرچه با یک کوئری هم میشه، ولی بهتر و ساده تر هست 2 کوئری جدا بسازین،
یکی برای ورودی که در اون روی itemcode گروپ میگیرین و روی inqty هم sum ،
و مشابه همین رو برای خروجی میسازین.

حالا این دو تا کوئری رو روی itemcode با هم join میکنین.

البته میتونین کد این دو کوئری رو در کوئری سوم جایگزین کنین و یک کوئری داشته باشین،
ولی واقعا مزیتی نمیبینم.

---

سلام. ممنون از پاسخ شما

بله من هم ابتدا دوتا کویری ساختم و در کویری سوم نتیجه نهایی رو بدست آوردم....

اتفاقا برای دیتابیس انبار که با تعداد زیادی (گزارش / جدول / کویری / ماژول) سروکار داریم ، مهمه و مزیت اون در اینه که شما بجای 3 کویری فقط 1 کویری خواهید داشت. اینطوری هم از تعداد فایها کم میشه وهم حجم اون کاهش پیدا میکنه...

بهرحال ممنون میشم اگه کسی بتونه اینکار رو در قالب یک کویری انجام بده.. سپاس

نقل قول:

---

نوشته شده توسط m2326499

اتفاقا برای دیتابیس انبار که با تعداد زیادی (گزارش / جدول / کویری / ماژول) سروکار داریم ، مهمه و مزیت اون در اینه که شما بجای 3 کویری فقط 1 کویری خواهید داشت. اینطوری هم از تعداد فایها کم میشه وهم حجم اون کاهش پیدا میکنه...

---

سلام دوباره

خب این کاملا اشتباه هست!
هر کوئری نهایتا در حد چند کیلوبایت به اندازه دیتابیس شما اضافه میکنه.
و اگر دیتابیس شما درست طراحی شده باشه (که اینجا دیده میشه اینجور نیست)
این که کوئری روی 2 کوئری میانی ساخته بشه یا مستقیما روی جدولها (کافی هست در تعریف کوئری نهایی همون تعریف اون دو کوئری رو جایگزین کنین - با alias)
واقعا هیچ تاثیری نه تنها در اندازه بلکه در سرعت اجرا هم نداره:
برای این که یک ایده داشته باشین فرضا اگر چند 100 کالا داشته باشین
و جدول های in و out هم هر کدوم چند 100 هزار رکورد داشته باشن
تفاوت سرعت اجرا شاید در اندازه چند 100م ثانیه باشه!

ولی الان طراحی جداول شما نادرست هست چون normalize نیست،
در واقع این هست که اندازه دیتابیس رو بی دلیل زیاد میکنه و البته آثار جانبی دیگه هم داره.

با این وجود در تعداد رکورد کم این نرمالایز نبودن جداول خودش رو نشون نمیده (از نظر سرعت اجرای کوئری)
ولی آثار مخربش در هر صورت هست.

نقل قول:

---

نوشته شده توسط m2326499

بهرحال ممنون میشم اگه کسی بتونه اینکار رو در قالب یک کویری انجام بده.. سپاس

---

خب اول باید 2 کوئری جدا یکی برای ورودی و یکی خروجی بسازیم:

SELECT

    ItemCode,

    Description,

    Sum(InQty) AS [Quantity In],

    Count(ItemCode) AS [Count In]

FROM in_wh

GROUP BY ItemCode, Description

ضمیمه 156579

ضمیمه 156580

SELECT

    ItemCode,

    Description,

    Sum(OutQty) AS [Quantity Out],

    Count(ItemCode) AS [Count Out]

FROM out_wh

GROUP BY ItemCode, Description

ضمیمه 156581

ضمیمه 156578

حالا از روی این دو میتونیم کوئری تراز رو تعریف کنیم:

SELECT

    StockIN.ItemCode,

    StockIN.Description,

    StockIN.[Quantity In],

    StockOUT.[Quantity Out],

    StockIn.[Quantity In] - NZ(StockOut.[Quantity Out], 0) AS [Stock Balance],

    StockIN.[Count In], StockOUT.[Count Out]

FROM StockIN 

    LEFT JOIN StockOUT

    ON StockIN.ItemCode = StockOUT.ItemCode

ضمیمه 156582

برای ترکیب دو کوئری StockIn و StockOut در یک کوئری کافی هست تعریف اونها رو با alias بیاریم:

SELECT

    input.ItemCode,

    input.Description,

    input.Q AS [Quantity In],

    output.Q AS [Quantity Out],

    Input.Q - Nz(output.Q, 0) AS [Stock Balance],

    input.N AS [Count In],

    output.N AS [Count Out]

FROM (

    SELECT

        ItemCode,

        Description,

        Sum(InQty) AS Q,

        Count(ID) AS N

    FROM in_wh

    GROUP BY ItemCode, Description) AS [input]

LEFT JOIN (

    SELECT

        ItemCode,

        Description,

        Sum(OutQty) AS Q,

        Count(ID) AS N

   FROM out_wh

   GROUP BY ItemCode, Description) AS [output]

   ON input.ItemCode = output.ItemCode

ضمیمه 156583

اما برای نرمال کردن جداول باید یک جدول کدینگ داشته باشیم و فیلد description رو از جدول های in و out حذف کنیم:

SELECT

     ItemCode,

    Description

INTO Items

FROM in_wh

GROUP BY ItemCode, Description

ضمیمه 156584

حالا برای گرفتن description کوئری پست قبلی رو با این جدول join میکنیم:

SELECT

   input.ItemCode, 

  Items.Description, 

  input.Q AS [Quantity In], 

  output.Q AS [Quantity Out], 

  Input.Q - Nz(output.Q, 0) AS [Stock Balance], 

  input.N AS [Count In], 

  output.N AS [Count Out] 

FROM 

  (

    (

      SELECT 

        ItemCode, 

        Sum(InQty) AS Q, 

        Count(ID) AS N 

      FROM 

        in_wh 

      GROUP BY 

        ItemCode

    ) AS [input] 

    LEFT JOIN (

      SELECT 

        ItemCode, 

        Sum(OutQty) AS Q, 

        Count(ID) AS N 

      FROM 

        out_wh 

      GROUP BY 

        ItemCode

    ) AS [output] ON input.ItemCode = output.ItemCode

  ) 

  LEFT JOIN Items ON input.itemcode = Items.itemcode

حالا هم دیتا تکراری و بدردنخور نداریم (کاهش چشمگیر اندازه فایل دیتابیس)
هم عملیات سریعتر هست
و از همه مهمتر یکپارچگی دیتا رو داریم.

نقل قول:

---

نوشته شده توسط mazoolagh

حالا هم دیتا تکراری و بدردنخور نداریم (کاهش چشمگیر اندازه فایل دیتابیس)
هم عملیات سریعتر هست
و از همه مهمتر یکپارچگی دیتا رو داریم.

---

دست مریزاد استاد :تشویق::تشویق::تشویق:

عالی استاد:تشویق: