Untitled

Cái này là để lấy Bill theo ngày

SELECT o.OrderID, CONVERT(datetime, o.OrderDate, 103) as OrderDate, p.ProductName, p.Price as ProductPrice, p.Cost as ProductCost,
       od.Quantity, o.TotalPrice, p.ProductImageURL, p.ProductID, od.Active, r.RateNumber,u.FullName,u.Email,u.PhoneNumber
FROM tblOrder o 
INNER JOIN tblOrderDetail od ON o.OrderID = od.OrderID
INNER JOIN tblProduct p ON od.ProductID = p.ProductID
LEFT JOIN tblRating r ON od.ProductID = r.ProductID AND o.UserID = r.UserID
LEFT JOIN tblUser u ON o.UserID = u.UserID
WHERE o.OrderDate lIKE '22/10/2023%'



Này là để tìm thu nhập dựa vào sum Price và Cost ở Bill theo ngày

SELECT 
    CONVERT(DATE, o.OrderDate, 103) as OrderDay,
    SUM(p.Price) as TotalProductPrice, 
    SUM(p.Cost) as TotalProductCost
FROM tblOrder o 
INNER JOIN tblOrderDetail od ON o.OrderID = od.OrderID
INNER JOIN tblProduct p ON od.ProductID = p.ProductID
WHERE CONVERT(DATE, o.OrderDate, 103) = '2023-10-22'
GROUP BY CONVERT(DATE, o.OrderDate, 103)


Này là tìm sum thu chi dựa theo tháng


SELECT 
    CONVERT(DATE, o.OrderDate, 103) as OrderDay,
    SUM(p.Price) as TotalProductPrice, 
    SUM(p.Cost) as TotalProductCost
FROM tblOrder o 
INNER JOIN tblOrderDetail od ON o.OrderID = od.OrderID
INNER JOIN tblProduct p ON od.ProductID = p.ProductID
WHERE MONTH(CONVERT(DATE, o.OrderDate, 103)) = 11 AND YEAR(CONVERT(DATE, o.OrderDate, 103)) = 2023
GROUP BY CONVERT(DATE, o.OrderDate, 103)