OK, Qbd.dbo.vwAllMemberbyZipCode has 3 columns(client,zipcode,zip) Client and Zipcode need to = the providers client(from customer.customerdesc)through the many links and the providers coverage zips(zips.zip)through again the many links. I commented out the area I feel it goes to..
Red text is the link to the zips needed.(but your final link would be zips.zip)
Blue text is the link to the client.
Any ideas?
SELECT 3c.dbo.customer.customerdesc as Client, Provider_Availability.App_Date as Date, Provider_Availability.MasExamNum as ID,
3c.dbo.Provider_Info.FName, 3c.dbo.Provider_Info.LName, 3c.dbo.Provider_Info.State,3c.dbo.counties.County,
Provider_Availability.Available + Provider_Availability.Scheduled AS Available, Provider_Availability.Available AS Unused
sum(Qbd.dbo.vwAllMemberbyZipCode.zip) as Members
FROM Provider_Availability
INNER JOIN 3c.dbo.Provider_Info with(nolock)ON Provider_Availability.MasExamNum = 3c.dbo.Provider_Info.masExamNum
LEFT OUTER JOIN 3c.dbo.resource with(nolock) ON 3c.dbo.Provider_Info.ResourceId = 3c.dbo.resource.Id
LEFT OUTER JOIN 3c.dbo.zips with(nolock) ON 3c.dbo.zips.zip = 3c.dbo.resource.zip
LEFT OUTER JOIN 3c.dbo.counties with(nolock) ON 3c.dbo.counties.id = 3c.dbo.zips.countyid
LEFT OUTER JOIN 3c.dbo.resource_territory with(nolock) ON 3c.dbo.resource_territory.resourceid = 3c.dbo.Provider_Info.resourceid and 3c.dbo.resource_territory.zipid = 3c.dbo.zips.id
LEFT OUTER JOIN 3c.dbo.customer with (nolock) on 3c.dbo.Provider_Info.customerid =3c.dbo.customer.id
--LEFT OUTER JOIN Qbd.dbo.vwAllMemberbyZipCode with (nolock)
where(3c.dbo.Provider_Info.Status = 'active')
AND (Provider_Availability.App_Date >= GETDATE())
GROUP BY 3c.dbo.customer.customerdesc , Provider_Availability.App_Date, Provider_Availability.MasExamNum ,
3c.dbo.Provider_Info.FName, 3c.dbo.Provider_Info.LName, 3c.dbo.Provider_Info.State,3c.dbo.counties.County,
Provider_Availability.Available + Provider_Availability.Scheduled , Provider_Availability.Available
ORDER BY app_date,Provider_Availability.MasExamNum,client,state
Red text is the link to the zips needed.(but your final link would be zips.zip)
Blue text is the link to the client.
Any ideas?
SELECT 3c.dbo.customer.customerdesc as Client, Provider_Availability.App_Date as Date, Provider_Availability.MasExamNum as ID,
3c.dbo.Provider_Info.FName, 3c.dbo.Provider_Info.LName, 3c.dbo.Provider_Info.State,3c.dbo.counties.County,
Provider_Availability.Available + Provider_Availability.Scheduled AS Available, Provider_Availability.Available AS Unused
sum(Qbd.dbo.vwAllMemberbyZipCode.zip) as Members
FROM Provider_Availability
INNER JOIN 3c.dbo.Provider_Info with(nolock)ON Provider_Availability.MasExamNum = 3c.dbo.Provider_Info.masExamNum
LEFT OUTER JOIN 3c.dbo.resource with(nolock) ON 3c.dbo.Provider_Info.ResourceId = 3c.dbo.resource.Id
LEFT OUTER JOIN 3c.dbo.zips with(nolock) ON 3c.dbo.zips.zip = 3c.dbo.resource.zip
LEFT OUTER JOIN 3c.dbo.counties with(nolock) ON 3c.dbo.counties.id = 3c.dbo.zips.countyid
LEFT OUTER JOIN 3c.dbo.resource_territory with(nolock) ON 3c.dbo.resource_territory.resourceid = 3c.dbo.Provider_Info.resourceid and 3c.dbo.resource_territory.zipid = 3c.dbo.zips.id
LEFT OUTER JOIN 3c.dbo.customer with (nolock) on 3c.dbo.Provider_Info.customerid =3c.dbo.customer.id
--LEFT OUTER JOIN Qbd.dbo.vwAllMemberbyZipCode with (nolock)
where(3c.dbo.Provider_Info.Status = 'active')
AND (Provider_Availability.App_Date >= GETDATE())
GROUP BY 3c.dbo.customer.customerdesc , Provider_Availability.App_Date, Provider_Availability.MasExamNum ,
3c.dbo.Provider_Info.FName, 3c.dbo.Provider_Info.LName, 3c.dbo.Provider_Info.State,3c.dbo.counties.County,
Provider_Availability.Available + Provider_Availability.Scheduled , Provider_Availability.Available
ORDER BY app_date,Provider_Availability.MasExamNum,client,state