El Gato (11/10/2010)
Hi guys, our developer is not in today and I need help to generate the following query in a dynamic fashion:
SELECT *FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON NO.OrderID = NOLI.OrderID
WHERE NO.CardTransactionID IS NOT NULL
AND convert(char(10), OrderDate, 120) >= '2010-11-09 23:00:00'
AND convert(char(10), OrderDate, 120) <= '2010-11-10 11:00:00'
Now this works in a static way but I need it to be able to generate results on a daily basis whereby it can return orders from 11pm on the previous day, to 11am on the current day and then again from 11am to 11pm on the current day.
Currently I can only get this running by having two separate jobs and manually modifying the dates and times which isn't ideal.
Thanks for reading and I hope you guys can help.
It can't possibly work as it stands because the left hand side of the WHERE clause strips the time component from OrderDate:
SELECT TimelessDate = CONVERT(CHAR(10), GETDATE(), 120)
Result: '2010-11-10'
You need some datetime arithmetic:
DECLARE @Startdate DATETIME, @Enddate DATETIME, @NowDateTimeNoTime DATETIME
SET @NowDateTimeNoTime = CAST(GETDATE() AS DATE)
SET @Enddate = CASE
WHEN DATEPART(hh, GETDATE()) > 23 -- it's after 11pm today,
THEN DATEADD(hh, 23, @NowDateTimeNoTime) -- so enddate is 11pm today
WHEN DATEPART(hh, GETDATE()) > 11 -- it's after 11am today,
THEN DATEADD(hh, 11, @NowDateTimeNoTime) -- so enddate is 11am
ELSE -- it's up to and including 11am
DATEADD(hh, -1, @NowDateTimeNoTime) -- so enddate is 11pm yesterday
END
SET @Startdate = DATEADD(hh, -12, @Enddate)
-- Check the values returned:
SELECT
NowDateTimeNoTime = @NowDateTimeNoTime,
Startdate = @Startdate,
Enddate = @Enddate
-- Query, now SARGable :
SELECT *
FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON NO.OrderID = NOLI.OrderID
WHERE NO.CardTransactionID IS NOT NULL
AND OrderDate >= @Startdate
AND OrderDate <= @Enddate
All the workings are in here so you can see what everything does, but ask if you're unsure.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden