February 12, 2009 at 9:04 am
I have a 168 character varchar field which represents a week broken down into hour long segements (24*7). Each hour can either be available ("9") or unavailable ("0"). So a week where the whole of Tuesday was available, but the rest of the week was unavailable would be represented by 24 zeroes, followed by 24 nines, followed by 120 zeroes.
I am trying to write a query which will tell me how many days in a week have at least some availability. So if any of the 24 character blocks include a 9, then that day would count as having availability. Is there a simple way to do this? Thanks,
Tom
February 12, 2009 at 9:15 am
If you have a Numbers table (some people call it a Tally table), you can query it using something like:
create table #Numbers (
Number int primary key);
insert into #Numbers (Number)
select top 1000 row_number() over (order by object_id)
from sys.all_objects;
declare @String char(168);
select @String = replicate('0',24) + replicate('9',24) + replicate('0',120)
;with Available (Day, Avail) as
(select (number-1)/24 + 1, substring(@String, number, 1)
from #Numbers
where number between 1 and len(@String))
select distinct Day
from Available
where Avail = 9;
That query would give you the days that have times available.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply