Double Link through a few tables?

[Khanson82]

The sum feature is coming out all wrong..Where QA.dbo.vwAllMemberbyZipCode with (nolock) ON (IC.dbo.zips.zip = QA.dbo.vwAllMemberbyZipCode.zipcode) and (QA.dbo.vwAllMemberbyZipCode.client=IC.dbo.vwrptProvider_Info.customerdesc)
is where it is happening. The zipcode has to match the zips.zip but it has to match the zips.zip for a certain person and link back through a few tables.Any ideas?






Select IC.dbo.vwrptProvider_Info.customerdesc as Client,
ISC.dbo.PA.App_Date as Date,
IC.dbo.vwrptProvider_Info.masExamNum as ID,
IC.dbo.vwrptProvider_Info.FName,
IC.dbo.vwrptProvider_Info.LName,
IC.dbo.vwrptProvider_Info.county as County,
IC.dbo.vwrptProvider_Info.State,
PA.Available + PA.Scheduled AS Available,
PA.Available AS Unused,
sum(QA.dbo.vwAllMemberbyZipCode.zip) as Members


FROM IC.dbo.vwrptProvider_Info
INNER JOIN PA with(NOLOCK) ON PA.masexamnum = IC.dbo.vwrptProvider_Info.masexamnum
LEFT OUTER JOIN IC.dbo.resource_territory with(nolock) ON IC.dbo.resource_territory.resourceid = IC.dbo.vwrptProvider_Info.resourceid
LEFT OUTER JOIN IC.dbo.zips with(nolock) ON IC.dbo.resource_territory.zipid = IC.dbo.zips.id and IC.dbo.resource_territory.resourceid = IC.dbo.vwrptProvider_Info.resourceid
LEFT OUTER JOIN QA.dbo.vwAllMemberbyZipCode with (nolock) ON (IC.dbo.zips.zip = QA.dbo.vwAllMemberbyZipCode.zipcode) and (QA.dbo.vwAllMemberbyZipCode.client=IC.dbo.vwrptProvider_Info.customerdesc)

WHERE ISC.dbo.PA.App_Date >= getdate()
AND PA.MasExamNum = '3208'



group by IC.dbo.vwrptProvider_Info.masExamNum,
ISC.dbo.PA.App_Date,
IC.dbo.vwrptProvider_Info.customerdesc,
IC.dbo.vwrptProvider_Info.LName,
IC.dbo.vwrptProvider_Info.FName,
IC.dbo.vwrptProvider_Info.county,
IC.dbo.vwrptProvider_Info.State,
PA.Available + PA.Scheduled,
PA.Available

 

[markros]

What is the type of Zip column and are you sure you want to SUM it and not COUNT ?

I have other suggestions. Which SQL Server version you're using?

PluralSight Learning Library

 

[Khanson82]

2005 sql server Management Studio Express..

And:
(QA.dbo.vwAllMemberbyZipCode.zip)
is already a count field of all the zips for
(QA.dbo.vwAllMemberbyZipCode.zipcode)

The view lays out like this:

Client Zipcode Zip
WC 75067 5
WC 43094 1
WC 76281 10
LP 36890 8
etc..etc.etc..

And (QA.dbo.vwAllMemberbyZipCode.zip) is an int.

 

[markros]

I see. I'd split the select into logical subgroups, e.g.

;with cte as (select M.MainField, sum(T.Zip) as ZipTotal from MainTable M LEFT JOIN AnotherTable T on ... group by M.MainField)

select C.*, Other Fields from cte C LEFT JOIN the second table inner join MainTable M to get the rest of the fields.


PluralSight Learning Library

 

[Khanson82]

I'll mess wth this.. Thanks again..