سوال در مورد جدول فروش

[daffy_duck376]

یه جدول فروش رو چطور باید تعریف کرد که بشه میزان کاهش یا افزایش فروش یک کالا رو تو دو سال بشه مقایسه کرد و نتیجه رو به این شکل نشون داد؟ اگه میشه کد sql اون رو هم بنویسید که این نتیجه رو با چه کدی بدست بیارم
نام کالا - سال_1- میزان فروش_سال_1-سال_2-میزان فروش سال سال_2-میزان افزایش یا کاهش فروش

[Mahmoud.Afrad]

کد لینک زیر را ببینید
https://rextester.com/KZXVG10464

[mr.sirwan]

کد لینک زیر را ببینید
https://rextester.com/KZXVG10464

جناب افراد چطوری میشه واسه سال های شمسی همچین کدی نوشت؟ در حالی که تاریخ ها رو بصورت میلادی توی دیتابیس ذخیره کردم

[Mahmoud.Afrad]

جناب افراد چطوری میشه واسه سال های شمسی همچین کدی نوشت؟ در حالی که تاریخ ها رو بصورت میلادی توی دیتابیس ذخیره کردم

دو تا راه دارید
یکی اینکه بازه شروع و پایان هر سال شمسی مورد نظر را به میلادی بدست بیارید و کوئری بنویسید .
راه دیگر هم استفاده از فانکشن برای تبدیل میلادی به شمسی هست که کافیه همون کوئری pivot را اصلاح کرد.

[mr.sirwan]

نمیخوام از فانکشن استفاده کنم چون دارم از ef code firstاستفاده میکنم نمیخوام اصلا هیچ وابستگیی به توابع و ستور پروسیجرها داشته باشم، اما مورد اولی که گفتین بله بدست آوردم، بازه شروع و پایان سال شمسی به اینصورته، مثلا برای سال ۹۸:
2019/03/21 تا ۲۰۲۰/۰۳/۲۰
حالا اینو به چه صورت داخل کوئری بذارم؟

[Mahmoud.Afrad]

نمیخوام از فانکشن استفاده کنم چون دارم از ef code firstاستفاده میکنم نمیخوام اصلا هیچ وابستگیی به توابع و ستور پروسیجرها داشته باشم، اما مورد اولی که گفتین بله بدست آوردم، بازه شروع و پایان سال شمسی به اینصورته، مثلا برای سال ۹۸:
2019/03/21 تا ۲۰۲۰/۰۳/۲۰
حالا اینو به چه صورت داخل کوئری بذارم؟

در کد linq ، برای یک سال کافیه کوئری به همراه شرط برای مقایسه تاریخها و گروهبندی براساس کدکالا صورت بگیرد اما برای چند سال کار پیچیده میشه.
برای pivot هم باید کد تبدیل رو خودتون بنویسید و یا از کتابخانه های آماده استفاده کنید.

[mr.sirwan]

توی استفاده از ado توی برنامه مشکلی نیست میتونم از دستورات اس کیو ال توسط sqlcommand و sqlconnection استفاده کنم اونیکه گفتم فقط واسه فانکشن ها و ستورپروسیجر ها بود که برنامه رو وابسته به فانکشن های تعریف شده توی بانک میکنه و کار توسعه و انتشارش رو پیچیده میکنه، همونطور که خودتون میدونین ef code first خودش بانک رو ایجاد میکنه و کار باهاش خیلی راحته و نیازی به چیز اضافه نیس مثلا نیازی نیس فانکشن ها رو هم همراه بانک توی ستاپ قرار بدی، اصلا لازم نیس بانک رو توی ستاپ قرار بدیم، خودش توی سیستم مقصد بانک رو ایجاد میکنه
حالا با این اوصاف با همون دستورات اس کیو ال چجوری برای دوتا سال شمسی این نتیجه رو بگیرم

میتونم بازه سال شمسی رو توسط کد سی شارپ به دست بیارم و به اون دستور توی sql command بفرستم

[Mahmoud.Afrad]

به طریقی رکوردهای هر سال(بازه) را نشانه گذاری (در مثال زیر با case این کار انجام شده) و براساس اون گروهبندی کنید.
declare @d1from as date;
set @d1from = '2018/03/21'; -- آغاز سال 1397
declare @d1to as date;
set @d1to = '2019/03/21'; -- آغاز سال 1398

declare @d2from as date;
set @d2from = '2019/03/21'; -- آغاز سال 1398
declare @d2to as date;
set @d2to = '2020/03/21'; -- آغاز سال 1399


select ProductId, [year1], [year2], [year2]-[year1] as Diff
from
(
select
ProductId,
gLabel,
SUM(TotalPrice) as TotalPrice
from
(
select
sales.*,
(case when (SalesDate>=@d1from and SalesDate<@d1to) then 'year1'
when (SalesDate>=@d2from and SalesDate<@d2to) then 'year2' else null end) as gLabel
from sales
where (SalesDate>=@d1from and SalesDate<@d1to) OR (SalesDate>=@d2from and SalesDate<@d2to)
) nnnnnnn
group by ProductId, gLabel
) ssss
PIVOT
( SUM(TotalPrice)
FOR gLabel IN ([year1],[year2])
) ppppp

[mr.sirwan]

خیلی خیلی ممنون از لطفتون جناب افراد جواب داد سپاس از پیگیریتون

[Mahmoud.Afrad]

کد قبلی را اصلاح کردم.
یک اشکالی وجود داشت و اون هم اینکه روز آخر سال محاسبه نمیشد. به همین دلیل باید در برنامه خودتون ابتدای سال بعد رو به عنوان انتهای بازه به sql بفرستید(نیاز نیست یک روز از انتها کم کنید) و در شرطهای کوئری مساوی را از SalesDate<=@d2to حذف کنید تا به صورت SalesDate<@d2to در بیاید.

[mr.sirwan]

کد قبلی را اصلاح کردم.
یک اشکالی وجود داشت و اون هم اینکه روز آخر سال محاسبه نمیشد. به همین دلیل باید در برنامه خودتون ابتدای سال بعد رو به عنوان انتهای بازه به sql بفرستید(نیاز نیست یک روز از انتها کم کنید) و در شرطهای کوئری مساوی را از SalesDate<=@d2to حذف کنید تا به صورت SalesDate<@d2to در بیاید.

ممنون بابت لطفتون این مشکلو قبلا حل کردم، شاید راه خوبی نباشه ولی چون فیلد InvoiceDate من شامل زمان هم میشه برای مقایسه هام میام تاریخ رو همراه زمان بصورت 24 ساعتی چک میکنم، مثلا برای سال 98 به این شکل:
declare @DateTwoFrom date = '03/21/2019 00:00:00';
declare @DateTwoTo date = '03/19/2020 23:59:59';


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

[Mahmoud.Afrad]

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

در کوئری قبل، جدول کالا را با نتیجه مجموع فروش left join کنید
به صورت زیر
declare @d1from as date;
set @d1from = '2018/03/21'; -- آغاز سال 1397
declare @d1to as date;
set @d1to = '2019/03/21'; -- آغاز سال 1398

declare @d2from as date;
set @d2from = '2019/03/21'; -- آغاز سال 1398
declare @d2to as date;
set @d2to = '2020/03/21'; -- آغاز سال 1399

with cte_Sales
AS
(
select
sales.ProductId,
sales.QUANTITY,
sales.SalesDate,
sales.TotalPrice,
(case when (SalesDate>=@d1from and SalesDate<@d1to) then 'year1'
when (SalesDate>=@d2from and SalesDate<@d2to) then 'year2' else null end) as gLabel
from sales
where (SalesDate>=@d1from and SalesDate<@d1to) OR (SalesDate>=@d2from and SalesDate<@d2to)
),
cte_allProductSales
AS
(
select
Product.ProductId,
Product.Name,
SUM(cte_Sales.TotalPrice) AS SumOfTotalPrice,
cte_Sales.gLabel
from Product left join cte_Sales on Product.ProductId = cte_Sales.ProductId
group by
Product.ProductId,
Product.Name,
cte_Sales.gLabel
)
select
ProductId,
ISNULL([year1] , 0) AS YEAR1,
ISNULL([year2] , 0) AS YEAR2,
ISNULL([year2],0)-ISNULL([year1],0) as Diff
from cte_allProductSales
PIVOT
( SUM(SumOfTotalPrice)
FOR gLabel IN ([year1],[year2])
) pivotResualt


کوئری زیر هم همان نتیجه رو میده و فکر کنم performance بهتری داشته باشه
declare @d1from as date;
set @d1from = '2018/03/21'; -- آغاز سال 1397
declare @d1to as date;
set @d1to = '2019/03/21'; -- آغاز سال 1398

declare @d2from as date;
set @d2from = '2019/03/21'; -- آغاز سال 1398
declare @d2to as date;
set @d2to = '2020/03/21'; -- آغاز سال 1399

select
ssssss.*,
ssssss.year2-ssssss.year1 as diff
from
(
select
Product.ProductId,
SUM(case when Sales.ProductId=Product.ProductId AND SalesDate>=@d1from and SalesDate<@d1to then sales.totalPrice else 0 end) as year1,
SUM(case when Sales.ProductId=Product.ProductId AND SalesDate>=@d2from and SalesDate<@d2to then sales.totalPrice else 0 end) as year2
from Product, Sales
group by Product.ProductId
) ssssss

[mr.sirwan]

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