Make it look at todays date first

[Cpreston]

Hi

I have this code (see below) and its start date is tomorrow. I need the code to show todays date also. I have another query that as todays date but cannot see the difference. Could someone tell me what I need to change to make it look at todays date please

SELECT dbrderHeader.DateRequired, SUM(dbrderLine.TotalVolume)
AS [Total Volume]
FROM dbrderHeader INNER JOIN
dbrderLine ON dbrderHeader.OrderID = dbrderLine.OrderID INNER JOIN
dbo.Product ON dbrderLine.ProductID = dbo.Product.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.ProductGroup AS ProductGroup_1 ON dbo.ProductGroup.ParentID = ProductGroup_1.ProductGroupID
WHERE (dbrderHeader.StockIssued = 0) AND (ProductGroup_1.Name = 'Arborflor') AND (dbrderHeader.DateRequired >= GETDATE()) OR
(dbrderHeader.StockIssued = 0) AND (dbrderHeader.DateRequired >= GETDATE()) AND (dbo.ProductGroup.Name IN ('TREX')) AND
(dbrderHeader.DateRequired >= CAST(GETDATE() AS DATE)) AND (dbrderHeader.DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE)))
GROUP BY dbrderHeader.DateRequired, dbo.ProductGroup.Name
ORDER BY dbrderHeader.DateRequired

 

[Cpreston]

OR

if the above is not possible, is it possible for this to always look at tomorrow onwards?

SELECT DateRequired, SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
WHERE (DateRequired >= CAST(GETDATE() AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE))) AND (OrderStatus <> 1) AND (OrderStatus <> 0) AND
(OrderStatus <> 11)
GROUP BY DateRequired
ORDER BY DateRequired ASC

Thanks

 

[Cpreston]

Hi

I managed to get the second code to start at tomorrow by adding in +1 in

WHERE (DateRequired >= CAST(GETDATE()+1 AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE()+1 AS DATE)))

I would prefer for the first lot of code to look at todays date and I cannot see why it does not do this. Any ideas please.

Thanks

 

[Cpreston]

Hi

Managed it I just put in (GETDATE()-1 AS DATE))) in the areas required.

Thanks

 

[SQLBill]

Here's something to consider. Every time you code has GETDATE() in it, SQL Server has to figure out what that value is. It can change during your code run (maybe only by less than a second...but it still is changing).

Try this instead:

DECLARE @mydate DATETIME
SET @mydate = GETDATE()

<some code>
WHERE (DateRequired >= CAST(@mydate+1 AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(@mydate+1 AS DATE)))

So everywhere you use GETDATE(), you would use @mydate instead.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SgtJarrow]

I'll add one more piece of advice to SQLBill's...

Figure the actual date needed for the work AND remove the CAST x AS DATETIME. The value is already a date so there is no need to have SQL keep converting each for each row.
I'd also recommend calculating the start and end dates as separate variables and use them instead of using DATEADD in the WHERE clause wherever possible.

Again, there is nothing "wrong" with your final code. The suggestions might just speed up performance and decrease maintenance issues.
Always test these type of optimizations before moving them into production to ensure you are getting the expected performance changes.

DECLARE @startdate DATETIME
SET @startdate = DATEADD(DAY, 1, GETDATE())
DECLARE @enddate DATETIME
SET @enddate = DATEADD(DAY, 7, @startdate)

<some code>
WHERE (DateRequired >= @startdate) AND (DateRequired <= @enddate)

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer