Discarding anything except 8-to-5

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Discarding anything except 8-to-5

Author

Topic

outjet
Starting Member

3 Posts

Posted - 2011-02-02 : 10:29:18

Trying to determine how many hours rooms were reserved between the hours of 8:00am and 5:00pm. (9 hours max). If the room was reserved from 3:00am to 11:00pm we'd throw out those extra hours.

For example:

StartTime EndTime Desired total
7:00a     5:00p    9 hours (discard 7am-8am)
8:00a     5:00p    9 hours

1:00p     5:00p    4 hours
1:00p     6:00p    4 hours (discard 5pm-6pm)

Having trouble coding this - started with
SELECT
CASE
WHEN -- START AND END BEFORE 8 OR START AND END AFTER 5
   (datepart(hh,timeeventstart) [ 8 AND DATEPART( (hh,timeeventend) [ 8)
OR (datepart( (hh,timeeventstart) ]= 17 AND DATEPART( (hh,timeeventend) ]= 17)
THEN 0 --(zero hours)
WHEN -- START BEFORE 8 END AFTER 5
datepart( (hh,timeeventstart) [ 8 AND DATEPART( (hh,timeeventend) ]= 17
THEN 9
WHEN -- START BEFORE 8 END BEFORE 5
datepart( (hh,timeeventstart) [ 8 AND DATEPART( (hh,timeeventend) [ 17
THEN
datepart( (hh,timeeventend) + datepart( (mi,timeeventend)/60.00 - 8
WHEN -- START AFTER 8 END AFTER 5
datepart( (hh,timeeventstart) ]= 8 AND DATEPART( (hh,timeeventend) ]= 17
THEN
17 - datepart( (hh,timeeventstart) - (datepart( (mi,timeeventstart)/60.00)
ELSE -- START AFTER= 8 END BEFORE= 5
datediff( (mi,timeeventstart,timeeventend)/60.00
END

I've gotten to the point where I suspect I've WAY over-complicated this... any ideas?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-02 : 12:08:09

I don't have SQL 2008, but this may help you.
Jim

DECLARE @table TABLE (InTime datetime,OutTime datetime)
INSERT INTO @table
SELECT '07:00','17:00'  UNION
SELECT '08:00','17:00'  UNION
SELECT '13:00','17:00'  UNION
SELECT '13:00','18:00'   


SELECT 
       inTime,outTime     
    , [TotHours] = case when datepart(hour,outTime) >17  then 17 else datepart(hour,outTime)   end
     - case when datepart(hour,inTime) <   8 then 8 else datepart(hour,inTime)   end
FROM @table

Everyday I learn something that somebody else already knew

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-02 : 12:37:11

"A problem well stated is a problem half solved." -- Charles F. Kettering

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-02 : 12:37:14

SELECT
inTime,outTime
, [TotHours] =
Datediff (hh,case when inTime < '1900-01-01 8:00:00.000' then '1900-01-01 8:00:00.000' else inTime end
,case when outTime > '1900-01-01 17:00:00.000'then '1900-01-01 17:00:00.000' else outTime end
)
FROM @table

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources