Help with SELECT query between certain time-frame

  • 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.

  • 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)

  • Just fyi - you could also use BETWEEN instead of WHERE ... >= AND ... <=

    brgds

    Philipp Post

  • 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.

  • 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

  • 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)))

  • Thanks guys - I am just going into a meeting but I will test the submitted solutions in a bit.

  • Chris is right

    replace

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

    OrderDate

    from the solution

  • 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

  • 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 🙂

  • You're welcome. Thanks for providing enough information to crack the problem.

    “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

  • 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 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'

    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