محاسبه سود هفتگی

با سلام دوستان من یک کوئری برای محاسبه سود هفتگی حاصل از فاکتورهای فروش نوشتم اما در شرایطی بهم جواب درست نمیده، این کوئری من هست:

SELECT (dbo.SalesOrders.UnitSalesPrice - dbo.SalesOrders.UnitPurchasePrice) * dbo.SalesOrders.ProductQty AS TotalNetProfit,

                    InvoiceDate, CAST((Datediff(Day, '12/28/2018 00:00:00', InvoiceDate) - 1) / 7 AS INT) + 1 AS WeekNumber

        FROM  dbo.SalesInvoices Right outer JOIN

                dbo.SalesOrders ON SalesInvoices.Id = dbo.SalesOrders.InvoiceId

        WHERE InvoiceDate >= '12/28/2018 00:00:00' AND InvoiceDate <= '01/28/2019 23:59:00'

        

        UNION 

        SELECT NULL as TotalNetProfit,NULL as Date,1 as WeekNumber

        UNION 

        SELECT NULL,NULL,2

        UNION 

        SELECT NULL,NULL,3

        UNION 

        SELECT NULL,NULL,4

        UNION 

        SELECT NULL,NULL,5

دوتا جدول دارم یکی به اسم SalesInvoices که مشخصات کلی و ثابت فاکتور رو در خودش ذخیره میکنه و یکی هم جدول SalesOrders که اقلام هر فاکتور در اون ذخیره میشن
حالا در کوئری بالا وقتی من این بخش از کوئری رو به تنهایی اجرا میگیرم نتیجه و حاصل جمع هر هفته رو که از روش محاسبه میکنم نتیجه درست رو بهم میده:

SELECT (dbo.SalesOrders.UnitSalesPrice - dbo.SalesOrders.UnitPurchasePrice) * dbo.SalesOrders.ProductQty AS TotalNetProfit,

                    InvoiceDate, CAST((Datediff(Day, '12/28/2018 00:00:00', InvoiceDate) - 1) / 7 AS INT) + 1 AS WeekNumber

        FROM  dbo.SalesInvoices Right outer JOIN

                dbo.SalesOrders ON SalesInvoices.Id = dbo.SalesOrders.InvoiceId

        WHERE InvoiceDate >= '12/28/2018 00:00:00' AND InvoiceDate <= '01/28/2019 23:59:00'

و اینم خروجیش:

ضمیمه 149688

که حاصل جمع ها این مقدار میشن و درست هستش:

ضمیمه 149689

اما وقتی کوئری رو بصورت کامل (یعنی همون کوئری اول) اجرا میگیرم نتیجه درست نمیگیرم (به مقادیر هفته سوم و پنجم دقت کنید که با نتیجه قبلی مغایرت دارن):

ضمیمه 149690

کجای کوئری رو باید اصلاح کنم که نتیجه درست بگیرم؟ اون null هایی هم که توی کوئری گذاشتم به این خاطر هست که ممکنه یک یا چندین هفته هیچ فروشی نداشته باشیم برای همین باید به ازای اون هفته های بدون فروش، مقدار صفر رو برگردونم به همین دلیل از null ها استفاده کردم، اگر راه دیگه ای بلد هستین ممنون میشم کمک کنین

اسکریپت جداول و اسکریپت دیتای جداول رو بزارید تست کنم.

نقل قول:

---

نوشته شده توسط Mahmoud.Afrad

اسکریپت جداول و اسکریپت دیتای جداول رو بزارید تست کنم.

---

ممنون جناب افراد، بفرمایید

اون چیزی که شما میگین به خاطر اینه که به ازای هر سطر از SaleOrder میاد سود رو محاسبه میکنه، فرضا همون تاریخی که شما گفتین یک فاکتوره به شماره 1010 و آیدی 3007 که آیتم ها یا اقلام این فاکتور در داخل جدول Order ذخیره شدن. این تصویر سطر مربوط به فاکتور در جدول Invoice:

ضمیمه 149694

اینم اقلام مربوط به همین فاکتور در جدول order:

ضمیمه 149695

من با همین داده هایی که داخل اون فایل زیپ قرار دادم دوباره این کوئری رو تست کردم نتیجه بازم مثل قبله و تفاوتی نکرده، اعداد و ارقامی که در عکس اول و دوم اومدن درست هستن چون خودم بصورت دستی محاسبه کردم و به این نتیجه رسیدم که اون ارقام صحیحن اما ارقام عکس سوم اشتباهن، یعنی چیزی که مدنظر منه باید مثل عکس اول و دوم باشه اما چیزی که بدست میارم اعداد شکل سومه.
لطفا خودتون هم تست کنین مثل همون کوئری های من یک بار کوئری رو بدون union ها اجرا کنین خودتون متوجه میشین

دقیقا خودم هم به همین نکته ای که اشاره کردین دقت کردم و برای خودم هم جای سواله که چرا توی عکس اول و دوم تعداد سطرهای واکشی شده 31 تاست اما توی عکس دوم با وجود 5 سطر null که باید به 31 سطر قبلی اضافه بشن و مجموع سطرهای واکشی شده 36 تا باشه اما میبینیم که 32 سطر واکشی شده

در کل اگر با کوئری دیگه ای میشه همین نتیجه ای که مد نظرم هست (یعنی محاسبه سود هفتگی) رو بدست آورد ممنون میشم بگین

خب، مشکل از union هست که سطرهایی که شبیه هستند رو یکبار میاره. از union all استفاده کنید.

منظورتون از هفتگی ، سود با آغاز از شنبه هست؟ یا صرفا 7 روز از تاریخ داده شده؟

ضمیمه 149696سلام
تصویر را ببینید :

نقل قول:

---

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

ضمیمه 149696سلام
تصویر را ببینید :

---

چون تاریخ ها کوچکتر از 28/12 هست اونها رو صفر میزنه. شرط where را اضافه کنید درست میشه.

ممنون جناب افراد مشکل با Union All حل شد سپاس از کمکتون

اگر هفته از شنبه تا جمعه منظور هست راحت تر و دقیقتر هم میشه نوشت.

نقل قول:

---

نوشته شده توسط Mahmoud.Afrad

اگر هفته از شنبه تا جمعه منظور هست راحت تر و دقیقتر هم میشه نوشت.

---

به نظر شما بهتر اینه که از کاربر تاریخ رو دریافت کنیم و طبق اون تاریخ، بیایم و بصورت هفت روز محاسبه رو انجام بدیم یا سوای تاریخ ابتدایی انتخاب شده توسط کاربر بیایم و هفته رو بصورت شنبه تا جمعه در نظر بگیریم؟ در حالت دوم ممکنه مثلا کاربر تاریخی انتخاب کنه که اون تاریخ سه شنبه باشه حالا تکلیف چیه؟ باید 3 شنبه تا جمعه اون هفته رو نادیده بگیریم و از شنبه هفته بعدی شروع کنیم؟ یا اینکه شنبه، یک شنبه و دوشنبه رو هم به همون هفته اضافه کنیم؟ یا اینکه راه دیگه ای در نظر دارین؟

اگر لطف کنین و این کوئری شنبه تا جمعه رو قرار بدین که خیلی خوب میشه

کوئری که شما نوشتی ابتدای هفته مهم نبوده.


با SET DATEFIRST 6 روز اول هفته را روی شنبه تنظیم کنید. در اینصورت اگر براساس سال و شماره هفته از سال ، گروهبندی کنید کوئری شنبه تا جمعه بدست میاد.
https://rextester.com/DDLYD95506