Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Nov 2008
    Posts
    42

    Unanswered: Half Hour Impacts

    I am trying to come up with a way to identify the half hour impact from
    several exceptions across multiple days. I have access to SQL 2000 and SQL 2005.

    The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM),
    stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact.

    When I have one exception with a start time of 7:15 and an end time of 8:20,
    I would like to see the following impacts:

    7:00 - .5
    7:30 - 1
    8:00 - .66

    When I have another exception with a start time 7:30 and an end time of 9:15, I would like to see the following impacts:

    7:30 1
    8:00 1
    8:30 1
    9:00 - .5

    Then when I roll them all up, I would see the following:

    7:00 - .5
    7:30 2
    8:00 1.66
    8:30 1
    9:00 - .5

    What would be the best approach to get these results?

    Thanks again for all of your help!
    Drew

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Assuming you have your two queries to produce the impact sets then this air code should do it...
    Code:
    SELECT Coalesce(i1.start_time, i2.start_time) As [start_time]
         , Coalesce(i1.impact, 0) + Coalesce(i2.impact, 0) As [impact]
    FROM   (
           <your first select>
           ) As [i1]
     FULL
      JOIN (
           <your second select>
           ) As [i2]
        ON i1.start_time = i2.start_time
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Drew - one question, one thread please.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2008
    Posts
    42
    Quote Originally Posted by pootle flump
    Drew - one question, one thread please.

    My appologies. I posted it in two forums because I thought the answer would be different in SQL and Access. I was trying to detemine if there was an easier solution in either SQL or Access.

    Thanks
    Drew

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, that's my lunchbreak gone. I am certain this can be improved loads.

    Code:
    USE test
    GO 
    
    DECLARE      @start    DATETIME
            , @end        DATETIME
    
    SELECT      @start    = '2008-03-09T07:00:00'
            , @end        = '2008-03-09T09:00:00'
    
    DECLARE    @exceptions TABLE
        (
              start_moment    DATETIME
            , end_moment    DATETIME
            , exception_id    INT
        )
    
    INSERT @exceptions
    SELECT    '2008-03-09T07:15:00', '2008-03-09T08:20:00', 1
    UNION ALL
    SELECT    '2008-03-09T07:30:00', '2008-03-09T09:15:00', 2
    UNION ALL
    SELECT    '2008-03-09T07:35:00', '2008-03-09T07:45:00', 3
    
    SELECT      lower_bound
            , impact    = SUM(impact)
    FROM    --dear lord - surely there's a better way?
            (
                SELECT      lower_bound
                        , start_moment
                        , end_moment
                        , impact        =    CASE
                                                WHEN start_moment <= lower_bound AND end_moment > upper_bound THEN
                                                    1
                                                WHEN lower_to_start BETWEEN 0 AND 29 AND NOT end_moment < upper_bound THEN
                                                    lower_to_start/30.0
                                                WHEN lower_to_end BETWEEN 0 AND 29 AND NOT DATEDIFF(minute, start_moment, end_moment) <=30 THEN
                                                    lower_to_end/30.0
                                                WHEN start_moment > lower_bound AND end_moment <= upper_bound  THEN
                                                    DATEDIFF(minute, start_moment, end_moment)/30.0
                                            END
                FROM    --more_derived_values
                        (
                            SELECT      start_moment
                                    , end_moment
                                    , lower_bound
                                    , upper_bound
                                    , lower_to_start    = DATEDIFF(minute, lower_bound, start_moment)
                                    , upper_to_end        = DATEDIFF(minute, end_moment, upper_bound)
                                    , lower_to_end        = DATEDIFF(minute, lower_bound, end_moment)
                            FROM    --Derived dates
                                    (
                                        SELECT      lower_bound        = DATEADD(minute, number*30, @start)
                                                , upper_bound        = DATEADD(minute, ((number+1)*30) - 1, @start)
                                                , start_moment
                                                , end_moment
                                        FROM    dbo.numbers--http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx
                                        CROSS JOIN 
                                                @exceptions AS exceptions
                                        WHERE    number BETWEEN 0 AND (DATEDIFF(hour, @start, @end)*2.0)
                                    ) AS derived_dates
                        ) AS more_derived_values
            ) AS yet_more_derived_values
    GROUP BY lower_bound
    ORDER BY lower_bound
    Last edited by pootle flump; 11-26-08 at 11:03.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2008
    Posts
    42
    Thank you very much!

    Drew

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, not only is it unwieldy, I think it might miss the odd scenario.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I clearly got the wrong end of the stick with this question... Anyone care to explain? I've read through your query poots but haven't learned much from it
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2008
    Posts
    42
    Alright, I created the numbers table and ran the code. The impacts are what I am looking for, but what would I modify to capture the exceptions that are in a different table?

    The table is called ux and the fields that contain the start and stop time of each segment are called 'start_moment' and 'stop_moment'.

    Thanks again for your time and effort to help me.

    Drew

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well....
    The inner most table uses a tally table (have I ever mentioned how brill they are?) to create all 30 minute segments between a start and end time.
    A cartesian product is created (cross join) with the exceptions, so we can compare every exception with every 30 minute segment. Thinking about it, I guess we could use an outer Theta Join instead to make it more efficient.

    The next table just calculates some time differences to make the later code a bit more compact.....

    The CASE statement is ugly. It basically accounts for exceptions that
    1) span a segment
    2) start during a segment
    3) end during a segment
    4) start AND and during a segment
    Thinking about it further, moving 4 to 2 would mean the code could be simplified a bit.

    The final part groups by segment and SUMS the interval fractions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Based on that, slightly improved. Still ugly:
    Code:
    SELECT    lower_bound
            , impact    = SUM(impact)
    FROM    --dear lord - surely there's a better way?
            (
                SELECT    lower_bound
                        , impact        =    CASE
                                                WHEN start_moment <= lower_bound AND end_moment > upper_bound THEN
                                                    1
                                                WHEN start_moment > lower_bound AND end_moment <= upper_bound  THEN
                                                    DATEDIFF(minute, start_moment, end_moment)/30.0
                                                WHEN lower_to_start BETWEEN 0 AND 29 THEN
                                                    lower_to_start/30.0
                                                WHEN lower_to_end BETWEEN 0 AND 29 THEN
                                                    lower_to_end/30.0
                                            END
                FROM    --more_derived_values
                        (
                            SELECT    start_moment
                                    , end_moment
                                    , lower_bound
                                    , upper_bound
                                    , lower_to_start    = DATEDIFF(minute, lower_bound, start_moment)
                                    , upper_to_end        = DATEDIFF(minute, end_moment, upper_bound)
                                    , lower_to_end        = DATEDIFF(minute, lower_bound, end_moment)
                            FROM    --Derived dates
                                    (
                                        SELECT    lower_bound        = DATEADD(minute, number*30, @start)
                                                , upper_bound        = DATEADD(minute, ((number+1)*30) - 1, @start)
                                                , start_moment
                                                , end_moment
                                        FROM    dbo.numbers--http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx
                                        LEFT OUTER JOIN 
                                                @exceptions AS exceptions
                                        ON    exceptions.start_moment        <= DATEADD(minute, ((number+1)*30) - 1, @start)
                                        AND    exceptions.end_moment        >= DATEADD(minute, number*30, @start)
                                        WHERE    number BETWEEN 0 AND (DATEDIFF(hour, @start, @end)*2.0)
                                    ) AS derived_dates
                        ) AS more_derived_values
            ) AS yet_more_derived_values
    GROUP BY lower_bound
    ORDER BY lower_bound
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by qh8519a
    what would I modify to capture the exceptions that are in a different table?

    The table is called ux and the fields that contain the start and stop time of each segment are called 'start_moment' and 'stop_moment'.
    Well, you would change any mention of the @exceptions table for ux. Ditto for any columns.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nah, I just don't understand the question well enough to help, sorry.
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2008
    Posts
    42
    Quote Originally Posted by pootle flump
    Well, you would change any mention of the @exceptions table for ux. Ditto for any columns.

    I am still a novice when it comes to SQL, so I am sure I am asking some pretty silly questions.....I appreciate your patience.

    How would I insert my exception data from the other table where the example exceptions are?


    Code:
    INSERT @exceptions
    SELECT    '2008-03-09T07:15:00', '2008-03-09T08:20:00', 1
    UNION ALL
    SELECT    '2008-03-09T07:30:00', '2008-03-09T09:15:00', 2
    UNION ALL
    SELECT    '2008-03-09T07:35:00', '2008-03-09T07:45:00', 3
    Thanks again
    Drew

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The only query you are interested in is the last one. The code you quoted is for debugging, because I don't have a table of your exceptions - I can't test my query without something that approximates your environment - I'd just end up writing some useless aircode otherwise

    So, everywhere that I use @exceptions in the final query you need to swap with your real table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •