May 8, 2006 at 11:20 am
Good Morning everyone,
I trying to do a FULL JOIN on two tables, called s and p. These two tables contain data past and future data for Forcasting purposes.
What I want to get is the next 4 months data and the historical data for the same months from last year in order for me to compare and then create my forcast analysis.
Since not all data in this table can be found in the other, I am using full outer join and would like to include all the data in the wanted period from both the tables. I used the second where clause below, and it seemed to work fine for me, but not sure if the first one will work. Would any of you please give me some comments on the pros and cons for using these two options? I just want to include all the data for that period for my analysis and a query that performs better in terms of speed.
Thanks!
1)
where
(
dbo.monthstart(isnull(s.startdate,p.startdate))<= dateadd(m,4,dbo.monthstart(getdate()))
AND
dbo.monthstart(isnull(s.startdate,p.startdate))>= dbo.monthstart(getdate())
)
OR
dbo.monthstart(isnull(s.startdate,p.startdate))>= dateadd(m,-12,dbo.monthstart(getdate()))
AND
dbo.monthstart(isnull(s.startdate,p.startdate))<= dateadd(m,-8,dbo.monthstart(getdate()))
2)
(
dbo.monthstart(s.startdate)<= dateadd(m,4,dbo.monthstart(getdate()))
and dbo.monthstart(s.startdate) >= dbo.monthstart(getdate())
OR
(
dbo.monthstart(s.startdate)>= dateadd(m,-12,dbo.monthstart(getdate()))
and dbo.monthstart(s.startdate) <= dateadd(m,-8,dbo.monthstart(getdate()))
))
OR
(
dbo.monthstart(p.startdate) <= dateadd(m,4,dbo.monthstart(getdate()))
and dbo.monthstart(p.startdate) >= dbo.monthstart(getdate())
OR
(
dbo.monthstart(p.startdate)>= dateadd(m,-12,dbo.monthstart(getdate()))
and dbo.monthstart(p.startdate) <= dateadd(m,-8,dbo.monthstart(getdate()))
))
May 8, 2006 at 1:05 pm
I believe the following will work. You'll have to substitue your getmonthstart function where necessary.
declare @Historical table(startdate datetime, account varchar(50), amount money)
declare @Future table(startdate datetime, account varchar(50), amount money)
insert @Historical values('01 Jan 2005', 'SALES', 100)
insert @Historical values('01 Feb 2005', 'SALES', 100)
insert @Historical values('01 Mar 2005', 'SALES', 100)
insert @Historical values('01 Apr 2005', 'SALES', 100)
insert @Historical values('01 May 2005', 'SALES', 100)
insert @Historical values('01 Jun 2005', 'SALES', 100)
insert @Historical values('01 Jul 2005', 'SALES', 100)
insert @Historical values('01 Jul 2005', 'EXPENSES', 125)
insert @Historical values('01 Aug 2005', 'EXPENSES', 125)
insert @Future values('01 May 2006', 'SALES', 150)
insert @Future values('01 Jun 2006', 'SALES', 150)
insert @Future values('01 Jun 2006', 'EXPENSES', 170)
insert @Future values('01 Jul 2006', 'EXPENSES', 170)
insert @Future values('01 Aug 2006', 'SALES', 150)
insert @Future values('01 Sep 2006', 'SALES', 150)
insert @Future values('01 Dec 2006', 'SALES', 150)
declare @now datetime
select @now = getdate()
select *
from @Historical s
full outer join @Future p
on s.startdate = dateadd(m, -12, p.startdate) and p.account = s.account
where coalesce(p.startdate, @now) between @now and dateadd(m, 4, @now)
and coalesce(s.startdate, dateadd(m, -8, @now)) between dateadd(m, -12, @now) and dateadd(m, -8, @now)
May 8, 2006 at 2:53 pm
Thanks JeffB,
The two tables, s and p, both have historical and future data but contain two sets of different data, linked by date and accountid columns, and I FULL JOIN them because I want to get these two sets.
The 2) where clause works fine for me now, but I just wonder if the 1) clause will be more effective in terms of performance.
Thanks!
May 8, 2006 at 3:18 pm
if you are looking at the problem from a performance stand point here are some thoughts:
1. Compute in to variables as many fixed values as you can
2. Try not to use functions surrounding the columns that you are using for searching.
3. If you can afford the extra code length it maybe faster to use several "union all" instead of "OR"
HTH
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply