Here's a step-by-step tutorial for using ' to create an easy reporting tool. This guide will walk through the setup, data preparation, view creation, and generating Excel reports.
Step 1: Data Preparation
First, create the necessary tables in your SQL database. Use the following SQL scripts to set up the Customers, Products, Orders, and OrderItems tables.
CODE
CREATE TABLE Customers (
CustomerID BIGINT IDENTITY(1,1) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
FirstName NVARCHAR(100) NOT NULL,
DOB DATE NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (YEAR(DOB) >= 1900),
PRIMARY KEY (CustomerID)
);
CREATE TABLE Products (
ProductID BIGINT IDENTITY(1,1) NOT NULL,
ProductName NVARCHAR(1000) NOT NULL,
ProductCode NVARCHAR(1000) NOT NULL,
AvailableQuantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (AvailableQuantity >= 0),
PRIMARY KEY (ProductID)
);
CREATE TABLE Orders (
OrderID BIGINT IDENTITY(1,1) NOT NULL,
CustomerID BIGINT,
OrderNumber NVARCHAR(1000) NOT NULL,
OrderDate DATETIME NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderItems (
OrderItemID BIGINT IDENTITY(1,1) NOT NULL,
OrderID BIGINT,
ProductID BIGINT,
Quantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (OrderItemID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
);
--Create customers
INSERT INTO Customers (LastName, FirstName, DOB) VALUES
('Au Yeung', 'David', '19801231')
, ('Chan', 'Peter', '19820115')
--Create products
INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
('Android Phone', 'A0001', 100)
, ('iPhone', 'I0001', 100)
, ('iPad', 'I0002', 100)
, ('iPad Mini', 'I0003', 100)
--David bought 10 iPhone
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
, 'ORD0001'
, GETDATE())
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0001' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
, 10)
--Peter bought 1 Android Phone
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'Peter' AND IsDeleted = 0)
, 'ORD0002'
, GETDATE())
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0002' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'A0001' AND IsDeleted = 0)
, 1)
--David bought 1 more iPhone next month
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
, 'ORD0003'
, DATEADD(MONTH, 1,GETDATE()))
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0003' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
, 1)
SELECT * FROM Customers
SELECT * FROM Products
SELECT * FROM Orders
SELECT * FROM OrderItems
Step 2: Create a View for Reporting
Create a SQL view to summarize the top product for each month based on sales. This will help encapsulate your data for reporting.
CODE
CREATE VIEW [v_Product_Top_1] AS
WITH cte AS (
SELECT
YEAR(o.OrderDate) AS SalesYear,
MONTH(o.OrderDate) AS SalesMonth,
p.ProductName,
SUM(oi.Quantity) AS TotalSales,
ROW_NUMBER() OVER(PARTITION BY p.ProductName, YEAR(o.OrderDate), MONTH(o.OrderDate) ORDER BY SUM(oi.Quantity) DESC) AS rn
FROM OrderItems oi
INNER JOIN Orders o ON o.OrderID = oi.OrderID AND o.IsDeleted = 0
INNER JOIN Products p ON p.ProductID = oi.ProductID AND p.IsDeleted = 0
WHERE oi.IsDeleted = 0
GROUP BY p.ProductName, YEAR(o.OrderDate), MONTH(o.OrderDate)
HAVING SUM(oi.Quantity) > 0
)
SELECT SalesYear, SalesMonth, ProductName, TotalSales
FROM cte
WHERE rn = 1;
Love C#!
Vollständiger Original-Bericht
Ausführliche Details, Code-Beispiele & Hersteller-Stellungnahme auf dev.to.
SOCIAL SHARE CARD GENERATOR