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

[m2326499]

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

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

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

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

[mazoolagh]

سلام و روز خوش

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





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

[mazoolagh]

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

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

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

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

[m2326499]

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

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

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

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


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

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

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

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

[mazoolagh]

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

سلام دوباره

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

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

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

[mazoolagh]

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

خب اول باید 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

[mazoolagh]

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

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

[mazoolagh]

برای ترکیب دو کوئری 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

[mazoolagh]

اما برای نرمال کردن جداول باید یک جدول کدینگ داشته باشیم و فیلد 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

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

[amirzazadeh]

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