gpt4 book ai didi

sql - WHERE 子句中的日期范围从 90 天前到今天的日期

转载 作者:行者123 更新时间:2023-12-02 15:22:08 25 4
gpt4 key购买 nike

我编写了以下查询,但在 WHERE 子句中我使用了特定日期作为日期范围。我希望自动计算日期范围,即截至今天的 90 天的时间段。我该怎么做?

SELECT Orders.Day, 
Orders.Month,
Orders.Year,
Orders.Orders_Count,Orders.PaymentAmount,
Orders.Total_Payment_Authorized,
Orders.ShipCity,
Orders.Total_Payment_Received,
Orders.SalesTax1,
Orders.SalesTax2,
Orders.SalesTax3,
Orders.TotalShippingCost,
Orders.OrderDetails_Count,
Orders.Quantity,
Orders.COGS,
Orders.Profit,
Orders.ProfitMargin
FROM (SELECT Orders.Day,Orders.Month,Orders.Year,Orders.Orders_Count, Orders.PaymentAmount,Orders.Total_Payment_Authorized,Orders.ShipCity,Orders.Total_Payment_Received,Orders.SalesTax1,Orders.SalesTax2,Orders.SalesTax3, Orders.TotalShippingCost,OrderDetails.OrderDetails_Count, OrderDetails.Quantity,OrderDetails.COGS,OrderDetails.Profit,OrderDetails.ProfitMargin
FROM (SELECT DATEPART(DD, Orders.OrderDate) AS Day, DATEPART(MM, Orders.OrderDate) AS Month,DATEPART(YY, Orders.OrderDate) AS Year, Max(Orders.OrderDate) As OrderDate ,Max(Orders.ShipDate) As ShipDate, Count(Orders.OrderID) As Orders_Count, Sum(Orders.PaymentAmount) AS PaymentAmount, Sum(Orders.Total_Payment_Authorized) AS Total_Payment_Authorized,Orders.ShipCity AS ShipCity,
Sum(Orders.Total_Payment_Received) AS Total_Payment_Received, Sum(Orders.SalesTax1) AS SalesTax1,Sum(Orders.SalesTax2) AS SalesTax2 , Sum(Orders.SalesTax3) AS SalesTax3, Sum(Orders.TotalShippingCost) AS TotalShippingCost
FROM (((Orders WITH(NOLOCK) LEFT JOIN PaymentMethods WITH(NOLOCK) ON Orders.PaymentMethodID = PaymentMethods.PaymentMethodID)
LEFT JOIN ShippingMethods WITH(NOLOCK) ON Orders.ShippingMethodID = ShippingMethods.ShippingMethodID)
LEFT JOIN Customers WITH(NOLOCK) ON Orders.CustomerID = Customers.CustomerID)
WHERE Orders.OrderStatus <> 'Cancelled' GROUP BY Orders.ShipCity, DATEPART(DD, Orders.OrderDate),DATEPART(MM, Orders.OrderDate),DATEPART(YY, Orders.OrderDate)) Orders
INNER JOIN (SELECT DATEPART(DD, Orders.OrderDate) AS Day,DATEPART(MM, Orders.OrderDate) AS Month,DATEPART(YY, Orders.OrderDate) AS Year, Count(OrderDetails.OrderDetailID) As OrderDetails_Count,
Sum(OrderDetails.Quantity) AS Quantity, Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS,
Case When SUM(Vendor_Price) IS NULL Then null Else Sum((Case When OrderDetails.ProductCode like 'DSC-%' Then
OrderDetails.ProductPrice - ISNULL(OrderDetails.Vendor_Price,0)
Else OrderDetails.ProductPrice - OrderDetails.Vendor_Price End) * OrderDetails.Quantity) End AS Profit,
CASE SUM(OrderDetails.ProductPrice * OrderDetails.Quantity) WHEN 0 THEN 0
ELSE ROUND(((SUM(OrderDetails.ProductPrice * OrderDetails.Quantity) - SUM(OrderDetails.Vendor_Price * OrderDetails.Quantity)) / SUM(OrderDetails.ProductPrice * OrderDetails.Quantity)) * 100, 1) END AS ProfitMargin
FROM (((Orders WITH(NOLOCK) LEFT JOIN PaymentMethods WITH(NOLOCK) ON Orders.PaymentMethodID = PaymentMethods.PaymentMethodID)
LEFT JOIN ShippingMethods WITH(NOLOCK) ON Orders.ShippingMethodID = ShippingMethods.ShippingMethodID) LEFT JOIN Customers
WITH(NOLOCK) ON Orders.CustomerID = Customers.CustomerID) LEFT JOIN OrderDetails WITH(NOLOCK) ON Orders.OrderID = OrderDetails.OrderID
WHERE Orders.OrderStatus <> 'Cancelled' AND DATEDIFF(dd, Orders.OrderDate, '03/01/2012') <= 0 AND DATEDIFF(dd, Orders.OrderDate, '06/30/2012') >= 0 AND Orders.ShipPostalCode IN ('96704','96710','96718','96719','96720','96721','96725','96726','96727','96728','96737','96738','96739','96740','96743','96745','96749','96750','96755','96760','96764','96771','96772','96773','96774','96776','96777','96778','96780','96781','96783','96785')
GROUP BY Orders.ShipCity, DATEPART(DD, Orders.OrderDate),DATEPART(MM, Orders.OrderDate), DATEPART(YY, Orders.OrderDate)) OrderDetails ON Orders.Day = OrderDetails.Day AND Orders.Month = OrderDetails.Month AND Orders.Year = OrderDetails.Year) Orders
ORDER BY Orders.Day DESC, Orders.Month DESC,Orders.Year DESC

最佳答案

如果是 Microsoft SQL 2000 或更高版本(05、08、10),则只需使用:

Where YourDate >= (GetDate() - 90)

关于sql - WHERE 子句中的日期范围从 90 天前到今天的日期,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11238985/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com