November 10, 2010 at 5:23 am
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.
November 10, 2010 at 5:39 am
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.
This Should do the trick
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) >= cast(dateadd(hh,23,cast(getdate()-1 as int) ) as datetime)
AND convert(char(10), OrderDate, 120) <= cast(dateadd(hh,11,cast(getdate() as int) ) as datetime)
November 10, 2010 at 5:39 am
Just fyi - you could also use BETWEEN instead of WHERE ... >= AND ... <=
brgds
Philipp Post
November 10, 2010 at 5:56 am
Thank you both for the replies.
Philip, I appreciate your response but I am looking for a solution similar to Shanu's.
Shanu, when I run SELECT cast(dateadd(hh,23,cast(getdate()-1 as int) ) as datetime)
It is still selecting today's date, I need yesterday's date in order for it to work correctly. I see you have the -1 in there to get yesterday's date but it isnt working.
Thanks again.
November 10, 2010 at 6:13 am
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
November 10, 2010 at 6:13 am
Either of these will work and stand a chance of using an index (if such an index exists) on the OrderDate column:
SELECT *
FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
SELECT *
FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(day, DATEDIFF(day, 0, GETDATE()), '1899-12-31T23:00:00')
AND DATEADD(day, DATEDIFF(day, 0, GETDATE()), '1900-01-01T11:00:00'))
Edit: Or if you want the two reports for the different time periods in one SQL statement:
SELECT *
FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(hour, ((DATEPART(hour, GETDATE()) + 1) / 12) * 12 - 1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND DATEADD(hour, ((DATEPART(hour, GETDATE()) + 1) / 12) * 12 + 11, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
November 10, 2010 at 7:23 am
Thanks guys - I am just going into a meeting but I will test the submitted solutions in a bit.
November 10, 2010 at 7:34 am
Chris is right
replace
convert(char(10), OrderDate, 120) with
OrderDate
from the solution
November 10, 2010 at 7:51 am
andrewd.smith (11/10/2010)
Either of these will work and stand a chance of using an index (if such an index exists) on the OrderDate column:
SELECT *FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
AndrewD, try this:
DECLARE @Now DATETIME
SET @Now = DATEADD(hh, -9, GETDATE() )
SELECT [Now] = @Now,
StartDate = DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
EndDate = DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
ExpectedEnddate = '2010-11-09 23:00:00.000'
SET @Now = DATEADD(hh, +9, GETDATE() )
SELECT [Now] = @Now,
StartDate = DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
EndDate = DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
ExpectedEnddate = '2010-11-10 23:00:00.000'
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
November 10, 2010 at 9:15 am
Superb solution Chris, many thanks!
I'd like to thank everyone else who took the time to reply, you guys made my day a lot easier 🙂
November 10, 2010 at 9:29 am
You're welcome. Thanks for providing enough information to crack the problem.
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
November 10, 2010 at 10:34 am
Chris Morris-439714 (11/10/2010)
andrewd.smith (11/10/2010)
Either of these will work and stand a chance of using an index (if such an index exists) on the OrderDate column:
SELECT *FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
AndrewD, try this:
DECLARE @Now DATETIMESET @Now = DATEADD(hh, -9, GETDATE() )
SELECT [Now] = @Now,
StartDate = DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
EndDate = DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
ExpectedEnddate = '2010-11-09 23:00:00.000'
SET @Now = DATEADD(hh, +9, GETDATE() )
SELECT [Now] = @Now,
StartDate = DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
EndDate = DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
ExpectedEnddate = '2010-11-10 23:00:00.000'
You're quite right about the query start times, Chris, but my post was a fix up of Shanu's query which was for just one of the two time ranges, and I hadn't properly read OP's requirements for the two separate time ranges at that time.
However, here is another way to derive the query start and end times
SELECT CONVERT(datetime, dt) AS ReferenceTime,
DATEADD(hour, ((DATEPART(hour, dt) + 1) / 12) * 12 - 13, DATEADD(day, DATEDIFF(day, 0, dt), 0)) AS StartTime,
DATEADD(hour, ((DATEPART(hour, dt) + 1) / 12) * 12 - 1, DATEADD(day, DATEDIFF(day, 0, dt), 0)) AS EndTime
FROM (
SELECT '2010-11-10T03:00:00' UNION ALL
SELECT '2010-11-10T13:00:00' UNION ALL
SELECT '2010-11-10T23:30:00'
) AS SampleTime(dt)
so the query could be written as a single statement without local varaiables.
SELECT *
FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(hour, ((DATEPART(hour, GETDATE()) + 1) / 12) * 12 - 13, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND DATEADD(hour, ((DATEPART(hour, GETDATE()) + 1) / 12) * 12 - 1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
Edit: Your solution is probably easier to understand and maintain though.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply