Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help with SELECT query between certain time-frame Expand / Collapse
Author
Message
Posted Wednesday, November 10, 2010 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 5:56 AM
Points: 5, Visits: 108
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.


Post #1018499
Posted Wednesday, November 10, 2010 5:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310


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)
Post #1018502
Posted Wednesday, November 10, 2010 5:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:29 AM
Points: 593, Visits: 629
Just fyi - you could also use BETWEEN instead of WHERE ... >= AND ... <=

brgds

Philipp Post
Post #1018503
Posted Wednesday, November 10, 2010 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 5:56 AM
Points: 5, Visits: 108
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.


Post #1018509
Posted Wednesday, November 10, 2010 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 7,108, Visits: 13,463
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1018522
Posted Wednesday, November 10, 2010 6:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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)))

Post #1018524
Posted Wednesday, November 10, 2010 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 5:56 AM
Points: 5, Visits: 108
Thanks guys - I am just going into a meeting but I will test the submitted solutions in a bit.
Post #1018593
Posted Wednesday, November 10, 2010 7:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310
Chris is right

replace
convert(char(10), OrderDate, 120) 

with
OrderDate

from the solution
Post #1018609
Posted Wednesday, November 10, 2010 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 7,108, Visits: 13,463
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'





“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1018625
Posted Wednesday, November 10, 2010 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 5:56 AM
Points: 5, Visits: 108
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

Post #1018725
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse