Without loops or cursors, how do you take a list of date intervals and turn them into a string of 1s and 0s such that:
So for example, if the intervals were:
Then the SQL you write should output 11011. Here is a setup script you could use:
declare @TimeSpan table
(
start datetime
,finish datetime
)
-- this is a good data set, with overlapping and non-overlapping time spans
insert into @TimeSpan values ('02/02/2010', '02/02/2010')
insert into @TimeSpan values ('02/03/2010', '02/03/2010')
insert into @TimeSpan values ('02/04/2010', '02/05/2010')
insert into @TimeSpan values ('02/05/2010', '02/06/2010')
insert into @TimeSpan values ('02/07/2010', '02/09/2010')
insert into @TimeSpan values ('02/08/2010', '02/08/2010')
insert into @TimeSpan values ('02/08/2010', '02/10/2010')
insert into @TimeSpan values ('02/14/2010', '02/16/2010')
-- for this set of data, the output string would be 111111111000111
DECLARE @Result VARCHAR(MAX), @start DATETIME
SELECT @start= MIN(start) ,
@Result =REPLICATE('0',1+DATEDIFF(DAY,MIN(start),MAX(finish)))
FROM @TimeSpan
SELECT @Result = STUFF(@Result,
DATEDIFF(DAY,@start,start)+1,
DATEDIFF(DAY,start,finish)+1,
REPLICATE('1',1+DATEDIFF(DAY,start,finish)))
FROM @TimeSpan
SELECT @Result
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With