Post reply

Fudging time

  • February 9, 2014 at 7:55 am

    #289335

    Hi All,

    I am working on a report that demonstrates how much time a room is on use between parameters for a start time and end time.

    The users specifies a start time and an end time and then the report returns meetings that are running between these two times.

    One requirement is to include meetings that starts before the parameter start time but is completed after the parameter start time but before the end parameter start time.

    For example

    Start Time Parameter: 09:00

    End Time Parameter: 12:00

    Meeting Starts at 08:30

    Meeting Finishers at 09:30

    This meeting would be included as it ends within the specified time range.

    What I need to do though is rather than the meeting being calculated at being 60 minutes in length, it only used the room for 30 minutes between the start and end parameters and so I need to get a calculation in the SQL that does this.

    Likewise using the same time ranges if the meeting started at 09:30 and finished at 10:30 this would be included as 60 minutes instead of 30 as the full meeting took place within the parameter time range and not just part of it.

    Any ideas how I can do this please?

    Thanks

    Eliza

  • February 9, 2014 at 9:52 am

    #1687377

    Sounds like homework 🙂

    What have you been able to come up with so far? Can you please post your TSQL?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • February 9, 2014 at 10:44 am

    #1687381

    Yes please share what you have so far.

    Do you have a table structure?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 9, 2014 at 7:04 pm

    #1687411

    Eliza (2/9/2014)

    Hi All,

    I am working on a report that demonstrates how much time a room is on use between parameters for a start time and end time.

    The users specifies a start time and an end time and then the report returns meetings that are running between these two times.

    One requirement is to include meetings that starts before the parameter start time but is completed after the parameter start time but before the end parameter start time.

    For example

    Start Time Parameter: 09:00

    End Time Parameter: 12:00

    Meeting Starts at 08:30

    Meeting Finishers at 09:30

    This meeting would be included as it ends within the specified time range.

    What I need to do though is rather than the meeting being calculated at being 60 minutes in length, it only used the room for 30 minutes between the start and end parameters and so I need to get a calculation in the SQL that does this.

    Likewise using the same time ranges if the meeting started at 09:30 and finished at 10:30 this would be included as 60 minutes instead of 30 as the full meeting took place within the parameter time range and not just part of it.

    Any ideas how I can do this please?

    Thanks

    Eliza

    The following article contains precisely what you're looking for along with an explanation of how it works. A simple modification of the input parameters would accomplish your task nicely.

    http://www.sqlservercentral.com/articles/T-SQL/105968/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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