SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER proc up_CustomerDirectoryReport_Select
/* Queries the database to build the Custom Directory Report */
@State nvarchar(500)=null,
@Counties nvarchar(1000)=null,
@Specialty nvarchar(200)=null,
@Zip1 nvarchar(3)=null,
@Zip2 nvarchar(3)=null
as
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER OFF
Declare @SQL1 nvarchar(4000)
Declare @SQL2 nvarchar(4000)
Declare @SQL3 nvarchar(4000)
Declare @SQLWHERE nvarchar(4000)
Declare @SQLOrderBY nvarchar(2000)
if @State IS NOT NULL AND Len(@State) <> 0
if @State <> "'ALL'"
Set @SQLWHERE = @SQLWHERE + N'and l.LState in (' + @State + ') '
if @Counties IS NOT NULL AND Len(@Counties) <> 0
if @Counties <> "'ALLCOUNTIES'"
Set @SQLWHERE = @SQLWHERE + N'and l.LCounty in (' + @Counties + ') '
if @Specialty IS NOT NULL
Set @SQLWHERE = @SQLWHERE + N'and s.SpecCode in (' + @Specialty + ') '
if @Zip1 IS NOT NULL
Set @SQLWHERE = @SQLWHERE + N'and left(l.LZip,3) between ' + @Zip1 + ' and ' + @Zip2 + ' '
Set @SQLWHERE = @SQLWHERE + N'and plsl.ListInDirectory = 1 and plsl.EffDate <= getdate() and (plsl.DelDate = null or plsl.DelDate > getdate()) '
Set @SQLOrderBY = N'order by 13,11,1,3,12,4,6,8,9,10,2 '
set @SQL1 = N'Select Distinct
s.Description,
s.Description2,
l.LCounty,
p.PLast + space(1) + p.PFirst + space(1) + p.PTitle AS Name,
OfficeName=case
when lt.directoryname is not null then lt.directoryname
when lt.SuppressOfficeName = 0 AND lt.DirectoryName Is NULL then l.LOfficeName
when lt.SuppressOfficeName = 1 AND lt.DirectoryName Is NULL then '''' end,
lt.SuppressOfficeName,
lt.DirectoryName,
l.LAdd1 + space(1) +
l.LAdd2 AS Add1,
l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3,
lt.Phone,
s.DisplayOrder AS Sortorder,
l.LCity,
tblStateAbb.State
FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls
ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN
(tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID)
ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
where 1=1 ' + @SQLWHERE
/*use a case statement instead of a union of three different queries...
Set @SQL1 = N'Select Distinct s.Description, s.Description2, l.LCounty, p.PLast + space(1) + p.PFirst + space(1) +
p.PTitle AS Name, lt.DirectoryName AS OfficeName, lt.SuppressOfficeName, lt.DirectoryName,
l.LAdd1 + space(1) + l.LAdd2 AS Add1, l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3,
lt.Phone, s.DisplayOrder AS Sortorder,l.LCity,tblStateAbb.State
FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls
ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN
(tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID)
ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
where (lt.DirectoryName Is Not NULL) ' + @SQLWHERE
Set @SQL2 = N'UNION Select Distinct s.Description, s.Description2, l.LCounty, p.PLast + space(1) + p.PFirst + space(1) +
p.PTitle AS Name, l.LOfficeName AS OfficeName, lt.SuppressOfficeName, lt.DirectoryName,
l.LAdd1 + space(1) + l.LAdd2 AS Add1, l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3,
lt.Phone, s.DisplayOrder AS Sortorder,l.LCity,tblStateAbb.State
FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls
ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN
(tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID)
ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
where lt.SuppressOfficeName = 0 AND lt.DirectoryName Is NULL ' + @SQLWHERE
Set @SQL3 = N'UNION Select Distinct s.Description, s.Description2, l.LCounty, p.PLast + space(1) + p.PFirst + space(1) +
p.PTitle AS Name, "" AS OfficeName, lt.SuppressOfficeName, lt.DirectoryName,
l.LAdd1 + space(1) + l.LAdd2 AS Add1, l.LCity + "," + space(1) + l.LState + space(1) + (l.LZip) + (l.LZip4) as Address3,
lt.Phone, s.DisplayOrder AS Sortorder,l.LCity,tblStateAbb.State
FROM (tblLocation AS l INNER JOIN (tblProviders AS p INNER JOIN (((tblProvLicense AS pl INNER JOIN tblProvLicSpec AS pls
ON pl.PLID = pls.PLID) INNER JOIN tblSpecCode AS s ON pls.SID = s.SID) INNER JOIN
(tblLocationTIN AS lt INNER JOIN tblProvLicSpecLoc AS plsl ON lt.TLID = plsl.TLID) ON pls.PLSID = plsl.PLSID)
ON p.PID = pl.PID) ON l.LID = lt.LID) INNER JOIN tblStateAbb ON l.LState = tblStateAbb.ab
where lt.SuppressOfficeName = 1 AND lt.DirectoryName Is NULL ' + @SQLWHERE
*/
--print @SQL1 + @SQL2 + @SQL3 + @SQLOrderBY
--EXECUTE(@SQL1 + @SQL2 + @SQL3 + @SQLOrderBY)
--print @SQL1 + @SQLOrderBY
EXECUTE(@SQL1 + @SQLOrderBY)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO