Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Date range in WHERE clause from 90 days ago to today's date

I have written the following query, but in the WHERE clause I have used specific dates for the date range. I want the date range to be calculated automatically--that is, a period of 90 days up to today's date. How can I do it?

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
like image 320
user818671 Avatar asked Jun 28 '12 06:06

user818671


People also ask

How can I get 90 days from current date in SQL?

GETDATE() - 90 works just fine giving me 2/10/05, which is 90 days ago!

Can datediff be used in a WHERE clause?

The DATEDIFF function can also be used in a WHERE clause as well as ORDER BY and HAVING clauses. The units of time available for the DATEDIFF are the same as those for the DATEADD function.

How can I get 30 days from current date in SQL?

SELECT (column name) FROM (table name) WHERE (column name) < DATEADD(Day,-30,GETDATE()); Example.

How can I get 90 days before date in MySQL?

php $d = date ( $format, strtotime ( '-90 days' ) ); mysql_query("SELECT * FROM recurringPayments WHERE lastpmt <= '$d'"); ?> Assuming you want data 90 days and older.


1 Answers

If it is Microsoft SQL 2000 or up (05, 08, 10) then simply use:

Where YourDate >= (GetDate() - 90)
like image 181
Tim S. Avatar answered Nov 09 '22 13:11

Tim S.