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

[mr.sirwan]

با سلام دوستان من یک کوئری برای محاسبه سود هفتگی حاصل از فاکتورهای فروش نوشتم اما در شرایطی بهم جواب درست نمیده، این کوئری من هست:
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]

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

[mr.sirwan]

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

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

[mr.sirwan]

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

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

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

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

[mr.sirwan]

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

[Mahmoud.Afrad]

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

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

[aslan]

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

[Mahmoud.Afrad]

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

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

[mr.sirwan]

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

[Mahmoud.Afrad]

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

[mr.sirwan]

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

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

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

[Mahmoud.Afrad]

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


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