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 2005 Forums
 Transact-SQL (2005)
 Mapping start and stop time as row of int

Author  Topic 

graabein
Starting Member

16 Posts
Posted - 2007-01-26 : 05:29:48
Hi, I have records with start and stop times and I'd like to display it visually as rows of different colour where I can see when the different records where active.

For example I have three records on one particular date:
08:05 - 09:20 normal
10:45 - 12:05 normal
15:15 - 17:45 critical

How do I turn that into something like this:
07__08__09__10__11__12__13__14__15__16__ (col.header)
....nnnnnn.....nnnnnn............ccccccc (return row)

Here I have divided each hour in 4 * 15 minute blocks but I'm not sure how detailed I need the result table to be...

I guess there is a pretty simple algorithm for doing this but I have not been able to find it. Any help would be much appreciated!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-01-26 : 05:38:46
SQL Server does not support colors...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

graabein
Starting Member

16 Posts
Posted - 2007-01-26 : 05:51:09
I'm going to run the result set through a reporting services report and exchange int/char value for a colour. So if I somehow get this output:

0000000111111000000001111111000000022222

I can map it to a grid and set up functions to set background colour accordingly.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-01-26 : 05:57:51
Try this
declare @sample table (fromtime datetime, totime datetime, status varchar(20))

insert	@sample
select	'08:05', '09:20', 'normal' union all
select	'10:45', '12:05', 'normal' union all
select	'15:15', '17:45', 'critical'

-- show the data
SELECT	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '07:00', '07:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '7a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '07:15', '07:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '7b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '07:30', '07:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '7c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '07:45', '07:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '7d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '08:00', '08:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '8a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '08:15', '08:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '8b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '08:30', '08:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '8c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '08:45', '08:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '8d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '09:00', '09:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '9a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '09:15', '09:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '9b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '09:30', '09:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '9c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '09:45', '09:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '9d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '10:00', '10:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '10a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '10:15', '10:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '10b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '10:30', '10:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '10c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '10:45', '10:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '10d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '11:00', '11:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '11a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '11:15', '11:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '11b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '11:30', '11:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '11c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '11:45', '11:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '11d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '12:00', '12:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '12a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '12:15', '12:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '12b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '12:30', '12:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '12c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '12:45', '12:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '12d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '13:00', '13:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '13a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '13:15', '13:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '13b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '13:30', '13:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '13c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '13:45', '13:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '13d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '14:00', '14:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '14a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '14:15', '14:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '14b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '14:30', '14:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '14c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '14:45', '14:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '14d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '15:00', '15:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '15a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '15:15', '15:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '15b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '15:30', '15:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '15c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '15:45', '15:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '15d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '16:00', '16:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '16a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '16:15', '16:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '16b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '16:30', '16:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '16c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '16:45', '16:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '16d',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '17:00', '17:14') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '17a',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '17:15', '17:29') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '17b',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '17:30', '17:44') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '17c',
	MAX(CASE WHEN dbo.fnTimeOverlap(FromTime, ToTime, '17:45', '17:59') > 0 THEN LEFT(Status, 1) ELSE '' END) AS '17d'
FROM	@Sample

The function dbo.fnTimeOverlap is found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77202


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

graabein
Starting Member

16 Posts
Posted - 2007-01-26 : 06:56:39
That works :) You're my new hero, Peter!

Also thanks from my colleague that has customers bothering him about this report all day!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site