February 22, 2006 at 3:04 pm
If anyone can help me with this-
suppose I have 2/1/06, 2/2/06, 2/8/06, 2/9/06
2/3/06 is weekday but is missing here
2/4/06 and 2/5/06 is weekend, we don't care about this
2/6,2/7 are weekdays but also missing here
question- how can we write t-sql to find and fill the gap of missing date?
the final result should include 2/1,2/2,2/3,2/6,2/7,2/8,2/9
February 22, 2006 at 3:55 pm
This may work for you:
--Build a table of numbers, you may want to make it a real table because it is a good tool to use
declare @Seq Table(ID int)
insert into @Seq
select top 8001 0
from master.dbo.syscomments a
cross join master.dbo.syscomments b
declare @i int
set @i = -1
update @Seq
set @i = ID = @i + 1
--Execute a query against the table with 2 dates
declare @StartDate datetime
declare @EndDate datetime
select @StartDate = '01 Feb 2005',
@EndDate = '09 Feb 2005'
select dateadd(dd, ID, @StartDate)
from @seq
where dateadd(dd, ID, @StartDate) <= @EndDate
February 24, 2006 at 6:49 am
Jeff
At first I was just going to ask what
set @i = ID = @i + 1
does, but I'd actually be curious to know what your entire process does. Why 8001? Syscomments because it pretty reliably has at least 8001 entries, or will with the cross-join?
What other situations do you use the number table for?
Mattie
February 24, 2006 at 9:11 am
set @i = ID = @i + 1, read from right to left increments the value of the local variable @1, assigns this value to the ID column of the current row in the record set and assigns the new value to @i. Procedurely it is the same as the following pseudo-code:
for each row in the table
set @i = @i + 1
row.ID = @i
next
Why 8001? Well it is because this table is frequently useful in doing things with strings that usually require some looping or cursor method and since the maximum length of a string is 8000 characters, there are 8001 numbers (0 to 8000) needed to address each position in a character string.
Another example is to take a deliminated string and return a table that can be joined against:
create function fnNonTerminalDelimPositions (@List varchar(8000), @Delim char(1))
returns table
as return
select ID as DelimPos from Seq
where substring(@List,ID,1) = @Delim
and ID < len(@List)
GO
February 24, 2006 at 9:20 am
That is too much to think about on a Friday (or Monday, for that matter). That's what I love about this forum: not only do you usually get an answer, but you get a different way to look at and do things.
Thanks for the explanation, it's very helpful.
Mattie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply