February 12, 2009 at 9:42 am
** Sorry peeps - I just realised that I posted this in the SQL 2005 forum and the server I'm working against is SQL 2000 :blush:. I was given a brilliant solution but unfortunately it won't work on SQL 2k as it uses a WITH statement - any ideas? **
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:46 am
Use the same solution, but use a derived table in the From clause instead of a CTE. The final query just changes to:
select distinct Day
from
(select (number-1)/24 + 1 as Day, substring(@String, number, 1) as Avail
from #Numbers
where number between 1 and len(@String)) Available
where Avail = 9;
Edit: You won't be able to use Row_Number to build your Numbers table, if you don't already have one. But there are plenty of ways to get around that.
- 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
February 12, 2009 at 9:47 am
the trick for # of recurring substrings in a string is to use the replace function:
declare @MySchedule varchar(168)
SET @MySchedule = REPLICATE('123456789',40) --overkill
select @MySchedule
/*results:
123456789123456789123456789123456789123456789
123456789123456789123456789123456789123456789
123456789123456789123456789123456789123456789
123456789123456789123456789123456
*/
--how many times is there a 9 in there?
select len(@MySchedule) - Len(Replace(@MySchedule,'9',''))
--results: 18 times
Lowell
February 12, 2009 at 9:49 am
Couldn't you use SUBSTRING to isolate each individual day, then convert it to an integer. If it's not zero, then the day has availability?
Greg
February 12, 2009 at 9:55 am
I just realized that INT or BIGINT are not large enough to hold a 24 digit number. Maybe DECIMAL(24, 0)?
February 12, 2009 at 9:56 am
Hi Lowell,
I think your solution would only tell me how many available hours there were in a week, rather than how many days in the week have availability - that makes it more complicated.
Speaking of complications, I tried to simplify my original example by leaving out the fact that there are actually other codes ("5" = possibly available), so unfortunately G², your idea wouldn't work either, though I guess I could combine yours a Lowell's by getting rid of the 5s first...
February 12, 2009 at 10:01 am
Change the final Where clause to allow for 5s.
The point of the query is that a Numbers table allows you turn the data into rows instead of instances in a string. Once you've done that, you can query it however you like.
- 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
February 12, 2009 at 10:23 am
Sorry GSquared, when I said the solution won't work because of the 5s, I was referring to the solution from G² (what are the chances of such similar names?? ;)).
Having said that, I'm still struggling to get your example to work. By combining your two solutions I have:
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)
select distinct Day
from
(select (schedavail-1)/24 + 1 as Day, substring(@String, schedavail, 1) as Avail
from #Numbers
where schedavail between 1 and len(@String)) Available
where Avail = 9;
Sorry if I'm being slow (this is in the newbie section :)) but this won't work because of the row_number function (SQL2K5 only). I'm not completely following how your example works - if I did I would adapt it to my circumstances. Could you explain it for my feeble brain?
February 13, 2009 at 7:32 am
Here's a version that works in 2000:
if object_id(N'tempdb..#Numbers') is not null
drop table #Numbers
create table #Numbers (
Number int identity primary key,
PlaceHolder bit);
insert into #Numbers (PlaceHolder)
select top 1000 null
from dbo.syscolumns;
declare @String char(168);
select @String = replicate('0',24) + replicate('9',24) + replicate('0',120);
select distinct Day
from
(select (number-1)/24 + 1 as Day, substring(@String, number, 1) as Avail
from #Numbers
where number between 1 and len(@String)) Available
where Avail in (5, 9);
- 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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply