quote:

---

Originally posted by flamblaster

Hi Vis,

I would think that having an actual calendar table rather than a table variable or CTE that you run at the time of the query would be more efficient. My actual calendar table has multiple date attributes because it is for an archive or data warehouse (not a fully fledged data warehouse, but it's not a transactional database)
The full table looks something like this:

create table ServiceCalendar
(
Sdate date primary key not null,
SMonthNum int not null,
SMonth char(3) not null,
SQuarter tinyint not null,
SYear smallint not null,
SDayofWeek tinyint not null
)

---

quote:

---

Originally posted by flamblaster

To clarify, is there a better way about cross joining/applying the routenumbers to the dates?

---

quote:

---

Originally posted by flamblaster

I have a Calendar table for reporting. Typically, I need to be able to see ridership on bus routes over 2 year periods to see percent increase/decrease. However, some months will not have the routes active but we still want those routes to show up for reporting even though they didn't run.

What I've been doing is using an outer apply between the calendar table and the route and route status tables, and scanning all routes that at least existed for the two year period as such:


CREATE TABLE [dbo].[Calendar](
[sdate] [date] NOT NULL,
[SYear] [int] NOT NULL,
[SMonth] [int] NOT NULL
) ON [PRIMARY]

declare @counter int
set @counter=0

while @counter<731
begin
insert into calendar (sdate, syear, smonth)
(
select DATEADD(dd, @counter, '2011-01-01'), YEAR(DATEADD(dd, @counter, '2011-01-01')), MONTH(DATEADD(dd, @counter, '2011-01-01'))
)
set @counter=@counter+1
end

declare @Year int, @Month int, @Start date, @End date
select @Year=2012, @Month=3,
@Start=convert(date, convert(char(8), ((@Year-1)*10000)+101)),
@End =convert(date, convert(char(8), ((@Year )*10000)+1231))

declare @Routes table (RouteId int primary key identity not null, RouteNum int not null)
declare @RouteStatus table (RouteStatusId int primary key identity not null, RouteId int not null, FromDate date not null, ToDate date null)

insert into @Routes (RouteNum)
values
(101),
(102),
(103),
(104)

insert into @RouteStatus (RouteId, FromDate, ToDate)
values
(1, '2010-01-01', '2011-02-10'),
(1, '2011-02-11', null),
(2, '2011-01-01', '2012-01-01'),
(3, '2012-03-01', null),
(4, '2012-03-01', '2012-04-01')

select C.SMonth, RR.RouteNum
from Calendar C
outer apply (select R.RouteNum
from @Routes R
join @RouteStatus RS on RS.RouteId=R.RouteId
where RS.FromDate<=@End and (RS.ToDate>=@Start or RS.ToDate is null)

) RR

where C.SYear>=@Year-1 and C.SYear<=@Year and C.SMonth<=@Month
group by C.SMonth, RR.RouteNum

My question is, this gives me the desired results (each route matched to each day for the whole period if the routenum at least existed from the @Start to the @End) but it seems like it's not very efficient from reading the execution plan. When I start joining ridership tables to this query, it begins to produce the Nested Loop No Join Predicate error message and runs very slow.

Any suggestions on how to tune this so that it's running more efficiently?

Thanks much!

---