Post reply

Where between and not between?

  • May 8, 2012 at 12:37 pm

    #273742

    Hi all,

    How do i get a report weekly from 00:00:00 Sunday (last 2 Sunday) to 23:59:59 (last saturday)

    1. If StartTime < last 2 Sunday then Starttime is last 2 Sunday

    2. If Endtime > last Saturday then Endtime is last Saturday

    But i can not use Where the Endtime is between last 2 Sunday and last Saturday, because it will not get any record which already start before last 2 sunday and end after last saturday. Any idea?

    Thanks.

    declare @dow int

    declare @2SundaysAgo datetime

    declare @lastSaturday datetime

    select @dow = datepart(dw, getdate())

    select @2SundaysAgo = getdate() - (7 + (@dow - 1))

    select @lastSaturday = getdate() - (@dow) + 1

    select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))

    select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))

    CREATE TABLE #Testing

    (

    Location varchar(255),

    Starttime DATETIME,

    Endtime DATETIME

    )

    INSERT INTO #Testing

    (Location, Starttime, Endtime)

    SELECT 'Dallas','2012-04-22 12:00:00.000','2012-04-25 13:00:12.000' UNION ALL

    SELECT 'Dallas','2012-04-24 12:00:00.000','2012-05-07 13:00:12.000' UNION ALL

    SELECT 'San Jose','2012-04-28 0:00:00.000','2012-05-06 12:59:39.370' UNION ALL

    SELECT 'San Jose','2012-04-30 0:00:00.000','2012-05-04 09:51:17.613' UNION ALL

    SELECT 'San Jose','2012-04-30 12:00:00.000','2012-05-11 09:54:48.483' UNION ALL

    SELECT 'San Jose','2012-04-28 0:00:00.000','2012-05-11 09:55:12.120' UNION ALL

    SELECT 'Tampa','2012-04-25 1:00:00.000','2012-04-30 14:23:33.000' UNION ALL

    SELECT 'Tampa','2012-04-29 1:00:00.000','2012-05-09 14:24:39.000' UNION ALL

    SELECT 'Tampa','2012-04-08 1:00:00.000','2012-05-05 16:53:44.000'

    select * from #Testing

    where Endtime Between @2SundayAgo AND @LastSaturday

    drop table #Testing

  • May 8, 2012 at 12:51 pm

    #1484710

    Based on your sample data, what should be returned by your query?

  • May 8, 2012 at 1:05 pm

    #1484714

    The result will have

    Location Starttime EndTime

    Dallas2012-04-29 12:00:00.0002012-05-05 23:59:59.000

    San Jose2012-04-29 12:00:00.0002012-05-05 23:59:59.000

    San Jose2012-04-30 00:00:00.0002012-05-04 09:51:17.613

    San Jose2012-04-30 12:00:00.0002012-05-05 23:59:59.000

    San Jose2012-04-29 12:00:00.0002012-05-05 23:59:59.000

    Tampa2012-04-29 12:00:00.0002012-04-30 14:23:33.000

    Tampa2012-04-29 01:00:00.0002012-05-09 14:24:39.000

    Tampa2012-04-29 12:00:00.0002012-05-05 16:53:44.000

  • May 8, 2012 at 1:24 pm

    #1484724

    Paraphrasing now based on your expected results. If the data starts and ends within the timeframe, plus if it starts within the time frame or ends within the time frame. Correct?

  • May 8, 2012 at 1:49 pm

    #1484739

    Thanks, I think we can do

    WHERE ((Starttime BETWEEN last2Sunday AND lastSaturday)

    OR

    (Starttime <Last2Sunday AND Endtime > LastSaturday))

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply