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

Fudging time Expand / Collapse
Author
Message
Posted Sunday, February 09, 2014 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 8:54 AM
Points: 32, Visits: 77
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
Post #1539565
Posted Sunday, February 09, 2014 9:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 3,733, Visits: 7,072
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; They'll drag you down to their level and beat you with experience"
Post #1539576
Posted Sunday, February 09, 2014 10:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 20,460, Visits: 14,086
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1539580
Posted Sunday, February 09, 2014 7:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 35,958, Visits: 30,249
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1539612
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse