Joins not beeing recognised - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joins not beeing recognised

Author

Topic

amitesh9
Starting Member

3 Posts

Posted - 2011-06-30 : 17:46:19

Hi,

i am facing a problem when trying to use joins in a query the query is mentioned below:

select b.F_Quarter,
b.Fiscal_Week,
a.[Customer Number],
a.Orderno,
a.Revenue,
a.[Agent Badge #],
c.NTId,
c.MgrName,
c.AMgrName,
CONVERT(varchar,a.[Callback Date],101) as 'Call Back Date',
datename(dw,a.[Callback Date])as [Week Day],
a.[Issue resolved (Y/N)],
a.[Same Issue -Y/N],
a.[Different Issue - Capture the issue - DOC or HDW],
a.[Refund Processed (Y/N)],
a.[OwNership Miss (Y/N)],
a.[Need to call (Y/N)],
a.APN,
a.BTTR,
a.[Comments (If Any)],
a.[Non-voice Disposition ],
a.[Voice Disposition ]
from dbo.ORR as a

left outer join (select distinct Actual_Date, F_Quarter, fiscal_week, Begin_Date_Week, End_Date_Week from dbo.F_weeks) as b
on a.[Callback Date] = b.[Actual_Date] where a.[Callback Date] BETWEEN b.[Begin_Date_Week] AND b.[End_Date_Week]

left outer join (select distinct Badge, NTId, MgrName, AMgrName, StartDate, StopDate from dbo.HC) as c
on a.[Agent Badge #] = c.Badge where a.[Callback Date] BETWEEN c.StartDate AND isnull (c.StopDate, '12/31/2150')

the problem i have are as follows

1) When i try to run this i get the error on the second join stating that there is a syntex error (left outer join)

2) when i run only the second join i do not get all the records and there are approx 250 records missing from a total of approx 82000 records.

Please help and thanks to all who have tried to help in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-30 : 18:01:56

Your WHERE clause defeats the purpose of having an outer join.

SELECT		b.F_Quarter, 
		b.Fiscal_Week,
		a.[Customer Number],
		a.Orderno, 
		a.Revenue,
		a.[Agent Badge #],
		c.NTId,
		c.MgrName,
		c.AMgrName,
		CONVERT(varchar(10), a.[Callback Date], 101) as [Call Back Date],
		DATENAME(dw, a.[Callback Date]) as [Week Day],
		a.[Issue resolved (Y/N)],
		a.[Same Issue -Y/N],
		a.[Different Issue - Capture the issue - DOC or HDW],
		a.[Refund Processed (Y/N)],
		a.[OwNership Miss (Y/N)],
		a.[Need to call (Y/N)],
		a.APN,
		a.BTTR,
		a.[Comments (If Any)],
		a.[Non-voice Disposition ],
		a.[Voice Disposition ]
FROM		dbo.ORR as a
left join	(
			select distinct	Actual_Date,
					F_Quarter,
					fiscal_week,
					Begin_Date_Week,
					End_Date_Week
			from		dbo.F_weeks
		) as b ON b.[Actual_Date] = a.[Callback Date]
			AND a.[Callback Date] BETWEEN b.[Begin_Date_Week] AND b.[End_Date_Week]
left join	(
			select distinct	Badge,
					NTId,
					MgrName,
					AMgrName,
					StartDate,
					StopDate
			from		dbo.HC
		) as c on c.Badge = a.[Agent Badge #]
			and a.[Callback Date] BETWEEN c.StartDate AND isnull (c.StopDate, '12/31/2150')

The join conditions seem bloated to me.

N 56°04'39.26"
E 12°55'05.63"

amitesh9
Starting Member

3 Posts

Posted - 2011-07-01 : 13:58:55

thanks Peso.. the query is working great. Just 1 more question. when i run the query it is now pulling in 20 extra records ie the total records are 50100 and it is displaying 50120 records. any suggestions on this..

Also you said the joins seemed to be bloated.. could you please be a little more descriptive on this.. Thanks in advance..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 22:31:21

I mean, why have BOTH "[Callback Date] = [Actual Date]" AND "[Callback Date] BETWEEN [Begin Date Week] AND [End Date Week]"?
The two conditions are joined between the same tables.

N 56°04'39.26"
E 12°55'05.63"

amitesh9
Starting Member

3 Posts

Posted - 2011-07-04 : 12:01:42

the "[Callback Date] = [Actual Date]" would be the join condition and the "[Callback Date] BETWEEN [Begin Date Week] AND [End Date Week]" is the condition i need to check the call back date.

basically the call back date is what i use for the fiscal week in my table. and the between is the condition i use to have the fiscal week to be between. the call back date can be on a running basis and the fiscal week table i have has columns which show the start and end dates of the week.

Hope this clarifies the question.. but am also open for fine tuning in the query..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources