March 15, 2010 at 10:25 am
I've tables and rows as follow,
declare @timerH table
(idx smallint,troutehidx smallint,seq tinyint,dprtweekday char(7));
/*idx is a primary key*/
/*Combination of troutehidx, and seq is a unique*/
insert into @timerH values(2,1,1,'1011101');
insert into @timerH values(7,2,1,'1111101');
insert into @timerH values(8,2,2,'0000011');
/*dprtweekday is a weekday*/
/*if 1011101, means Sunday, Tuesday, Wednesday, Thursday, and Saturday*/
/*if 1111100, means Sunday, Monday, Tuesday, Wednesday, Thursday, and Friday*/
declare @timerD table
(idx smallint,ttimerHidx smallint,dprttime smalldatetime);
/*idx is a primary key*/
/*ttimerHidx is a FK, and refer to @timerH(idx)*/
insert into @timerD values(2,2,'19000101 10:30');
insert into @timerD values(9,7,'19000101 09:00');
insert into @timerD values(10,7,'19000101 14:30');
insert into @timerD values(11,8,'19000101 11:00');
insert into @timerD values(12,8,'19000101 16:30');
I need to generate trip schedule from: 18 Mar 2010 To: 21 Apr 2010
So far, i've as follow,
declare @sDte smalldatetime
set @sDte='3/18/2010'
declare @eDte smalldatetime
set @eDte='4/21/2010'
select idx = row_number() over (order by t1.troutehidx, t1.seq, t1.dprtdte),
t1.timerHidx,t1.troutehidx,t1.seq,t1.dprtdte,
t2.dprttime
from
(select idx as timerHidx,troutehidx, seq, dprtdte
from
(
select idx,troutehidx, seq, dprtweekday,
dprtdte = dateadd(day, n, @sDte)
from @timerH h
cross join
(
select n = 0 union all select n = 1 union all select n = 2 union all
select n = 3 union all select n = 4 union all select n = 5 union all
select n = 6
) n
where dateadd(day, n, @sDte) <= @eDte
) h
where substring(dprtweekday, datepart(weekday, dprtdte), 1) = '1')t1
inner join
(select t1.idx,troutehidx,seq,dprtweekday,dprttime
from @timerH t1 inner join @timerD t2
on t1.idx=t2.ttimerHidx)t2
on t1.timerHidx=t2.idx
order by t1.troutehidx, t1.seq, dprtdte
The problem is, the result is not complete.
For timerHidx=2, there's no 25 Mar 2010, 27 Mar 2010, and so on till @eDte
For timerHidx=7, there's no 25 Mar 2010, 27 Mar 2010, and so on till @eDte
For timerHidx=8, there's no 26 Mar 2010, 27 Mar 2010, and so on till @eDte
Hopefully, someone can show me where need to fix
March 15, 2010 at 11:24 am
I think the issue is your inner N table. Since it only goes from 0 to 6, you end the series with @ste+6, which is 3/24/2010 (which happens to be my birthday. :-P) But I digress, that would explain why there is no 3/25-4/21.
Using the Tally table constructed from this article: http://qa.sqlservercentral.com/articles/T-SQL/62867/[/url], I kept your 1st block of code, and add this block as a test declare @sDte smalldatetime
set @sDte='3/18/2010'
declare @eDte smalldatetime
set @eDte='4/21/2010'
select datediff(d, @sDte, @eDte)
select idx,troutehidx, seq, dprtweekday, Tally.N,
dprtdte = dateadd(day, Tally.N-1, @sDte)
from @timerH h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1
and it seems to generate all the dates from @sDte to @eDte. So if you integrate that into your 2nd block of code, you should be good to go.
In summary, I replaced your block select idx,troutehidx, seq, dprtweekday,
dprtdte = dateadd(day, n, @sDte)
from @timerH h
cross join
(
select n = 0 union all select n = 1 union all select n = 2 union all
select n = 3 union all select n = 4 union all select n = 5 union all
select n = 6
) n
where dateadd(day, n, @sDte) <= @eDte
with select idx,troutehidx, seq, dprtweekday, Tally.N,
dprtdte = dateadd(day, Tally.N-1, @sDte)
from @timerH h
cross join
Tally
where Tally.N <=datediff(d, @sDte, @eDte)+1, provided you implemented the Tally table using the link mentioned above.
Please let me know if you have any questions.
Ray
March 15, 2010 at 12:53 pm
Hi sir,
It's work. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply