Post
Ask Question
Raysefo

Raysefo

  • 1.1k
  • 284
  • 150.2k

How to calculate annual and last 3 months sales

Aug 15 2022 3:39 PM

I have Order, OrderDetail, and Vendor entities. How can I obtain annual, last 3 months' total sales data, and a total number of orders data on a vendor basis from the entities of these sample data? I tried but I'm not very familiar with Linq. By the way, I am using EF Core 6. I ran the query without any errors on LinqPad, but I don't know how to fetch the data for the last 3 months' total sales.

OrdersDetails.GroupBy(x => new { Year = x.Order.OrderDateTime.Year, Month = x.Order.OrderDateTime.Month })
.Select(u => new 
{
      Year = u.Key.Year,
      Month = u.Key.Month,
      TotalPrice = u.Sum(x => x.TotalSellPrice)
            
})

 

 

Sample Data:

SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (1, N'2022-06-07 16:46:21', N'customer1', N'Completed', N'test1111')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (2, N'2022-06-08 00:00:00', N'Vestel', N'Continues', N'Cenk')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (3, N'2022-07-09 00:00:00', N'Arçelik', N'Cancelled', N'Cavit')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (4, N'2022-08-08 23:31:59', N'Fener', N'Continues', N'Test')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (5, N'2022-08-08 23:37:54', N'Ümraniye', N'Continues', N'Ümraniye')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (6, N'2021-08-04 23:43:28', N'Bruma', N'Completed', N'Gustova')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (7, N'2021-09-09 13:16:36', N'Custom', N'Completed', N'Test')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (8, N'2022-08-09 13:21:14', N'Tsubasa', N'Continues', N'Employee')
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [CustomerName], [Status], [DoneBy]) VALUES (9, N'2021-10-27 13:37:36', N'New Customer', N'Completed', N'Test')
SET IDENTITY_INSERT [dbo].[Orders] OFF

SET IDENTITY_INSERT [dbo].[OrdersDetail] ON
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (4, N'1', N'r', 12, 34, 2, 0, 56, 4, N'shipment', N'Completed', N'001', N'desc', 1, 2, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (5, N'12345-ABCN', N'Ipad', 5, 5, 10, 1000, 1000, 1500, N'S1', N'Getting ready', N'T11111', N'description1', 2, 1, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (6, N'0000-DDDD', N'Modem', 6, 6, 15, 200, 150, 225, N'S2', N'Getting ready', N'T11112', N'description2', 2, 2, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (7, N'98937-OPSKJ', N'ZTE', 7, 7, 10, 5000, 4500, 5500, N'S3', N'Getting ready', N'T11113', N'description3', 2, 1, N'TL')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (8, N'11111-YUSH', N'Laptop', 8, 8, 6, 50, 50, 75, N'S4', N'Cancelled', N'T11114', N'description1', 3, 2, N'Dolar')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (9, N'0090', N'Knife', 20, 677, 40, 0, 700, 343, N'9202930', N'Completed', N'uei9q9u', N'test', 1, 2, N'Euro')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (10, N'4', N'mouse', 12, 10, 10, 0, 10, 10, N's', N'Cancelled', N't', N'd', 3, 1, N'Euro')
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [BuyQuantity], [SellQuantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency]) VALUES (11, N'rrrrrrrr', N'tank palet', 100, 8878, 40, 0, 288, 39, N'trk*0292', N'Getting ready', N'79iwowu998', N'desc', 4, 1, N'Dolar')
SET IDENTITY_INSERT [dbo].[OrdersDetail] OFF

LinqPad


Answers (1)