I have a stored procedure that creates several temporary tables. When I call
this procedure from the Query Analyzer, it works just fine.
When I call the stored procedure through the DTS or from a query from the
reporting services, I get the error: Invalid object name '#NSLP'
#NSLP is the first temporary table. Any suggestions will be highly
appreciated.
Code for the stored procedure follows
CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
@.iMonth AS integer
AS
SET NOCOUNT ON
CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
EligBkfstSevere Integer,
EligBkfst Integer,
EligLunch Integer,
EligSnack Integer
)
INSERT INTO #NSLP
SELECT
B.Sponsor,
Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
EligBkfstSevere,
Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
FROM tblSLPAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSLPAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
INSERT INTO #DCCenter
SELECT
B.Sponsor,
Count(*) AS DCCenters
FROM tblDCAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblDCAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
INSERT INTO #ACCenter
SELECT
B.Sponsor,
Count(*) AS ACCenters
FROM tblACAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblACAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
INSERT INTO #SMCenter
SELECT
B.Sponsor,
Count(*) AS SMCenters
FROM tblSMAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSMAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
INSERT INTO #SFCenter
SELECT
B.Sponsor,
Count(*) AS SFCenters
FROM tblSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
INSERT INTO #SSFCenter
SELECT
B.Sponsor,
Count(*) AS SSFCenters
FROM tblSSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
SELECT
A.AgreementNo,
A.SponsorName,
CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
isnull((SELECT 'Y'
FROM dbo.fnAdminSLP('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_') AS [SLP Sponsor],
isnull((SELECT EligLunch
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligLunch > 0 ),0) AS NSLP,
isnull((SELECT EligBkfst
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfst > 0 ),0) AS Brk,
isnull((SELECT EligBkfstSevere
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfstSevere > 0 ),0) AS SevereBrk,
isnull((SELECT EligSnack
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligSnack > 0 ),0) AS ASSnk,
isnull((SELECT 'Y'
FROM dbo.fnAdminDC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_')
AS [DC Sponsor],
isnull((SELECT DCCenters
FROM #DCCenter
WHERE Sponsor = A.AgreementNo AND
DCCenters > 0 ),0) AS [DC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminAC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
isnull((SELECT ACCenters
FROM #ACCenter
WHERE Sponsor = A.AgreementNo AND
ACCenters > 0 ),0) AS [AC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminFH('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
isnull((SELECT 'Y'
FROM dbo.fnAdminSM('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
isnull((SELECT SMCenters
FROM #SMCenter
WHERE Sponsor = A.AgreementNo AND
SMCenters > 0 ),0) AS [SM Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
isnull((SELECT SFCenters
FROM #SFCenter
WHERE Sponsor = A.AgreementNo AND
SFCenters > 0 ),0) AS [SF Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
isnull((SELECT SSFCenters
FROM #SSFCenter
WHERE Sponsor = A.AgreementNo AND
SSFCenters > 0 ),0) AS [SSF Centers],
isnull(Type, '') as Type
FROM tblAgreeData A
WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
ORDER BY AgreementNo
GOTry to press the refresh button next to the data source. This will populate
all the fields for you. There are also some other replies to this question
just search for Invalid object in the newsgroup and you should see other
people making suggestions to this. Hope this helps. Let me know if this is
what you are looking for or if you have a different question.
Brendon Schwartz
http://spaces.msn.com/members/brendon
"Ron Sellers" wrote:
> I have a stored procedure that creates several temporary tables. When I call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>|||Have you tried using table variables instead of temp tables? On the surface,
they provide the same functionality, but may be treated differently by RS.
"Ron Sellers" <RonSellers@.discussions.microsoft.com> wrote in message
news:73374365-09D9-41B8-8B35-FD7E7B382264@.microsoft.com...
>I have a stored procedure that creates several temporary tables. When I
>call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS
> EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS
> EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>
sql
jmhooten: