Hi,
I'm trying to run a stored proc with Temp tables and when I run the stored proc I'm getting an error as shown below. Can any body help me please? I have permissions for the database and Temp Database also. All help is highly appreciated.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '[dbo].[tempDB.#Result]'.
Below is the code for the stored proc.
USE tempDB
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(U'[dbo].[tempDB.#Result]') AND type in (U'P', U'PC'))
DROP TABLE [dbo].[tempDB.#Result]
GO
USE [DatamartDB2]
GO
/****** Object: StoredProcedure [dbo].[MMC_SP_FoodNutritionOrdersReport1] Script Date: 11/29/2012 10:58:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]
GO
USE [DatamartDB2]
GO
/****** Object: StoredProcedure [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1] Script Date: 06/12/2013 13:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT 'MMC_SP_FoodNutritionOrdersReport_Test1'
DECLARE @Location varchar(255)
--if (ISNULL(@Location,'')='')
begin
Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)
SET @RptTimeFrameout='1' --Weekly
if @RptTimeFrameout='1' -- Daily
Begin
select @FromDate = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,0,getdate()),101))+ '00:00:00 AM')
select @ToDate = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'04:46:00 AM')
End
--DROP TABLE #Result
SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate into #Result
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%Order NPO%'
AND "BLOrdersLog"."Text_str" IS NOT NULL
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")
WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
--AND "MO_Demographics"."MRN" = '41579622'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL
UNION
SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%diet%'
AND "BLOrdersLog"."Text_str" IS NOT NULL
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")
WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
--AND "MO_Demographics"."MRN" = '41579622'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL
UNION
SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%'
AND "BLOrdersLog"."Text_str" IS NOT NULL)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")
WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL
ORDER BY
"bllocation"."location_name" ASC
select rlt1.*
from #result rlt1
where dietorderedtime in
(select top 1 dietorderedtime
from #result rlt2
where rlt1.mrn=rlt2.mrn
order by dietorderedtime desc)
END
END
SET NOCOUNT OFF
SET ANSI_NULLS OFF
GO
GO
GRANT EXECUTE ON [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1] TO [Public]
I'm trying to run a stored proc with Temp tables and when I run the stored proc I'm getting an error as shown below. Can any body help me please? I have permissions for the database and Temp Database also. All help is highly appreciated.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '[dbo].[tempDB.#Result]'.
Below is the code for the stored proc.
USE tempDB
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(U'[dbo].[tempDB.#Result]') AND type in (U'P', U'PC'))
DROP TABLE [dbo].[tempDB.#Result]
GO
USE [DatamartDB2]
GO
/****** Object: StoredProcedure [dbo].[MMC_SP_FoodNutritionOrdersReport1] Script Date: 11/29/2012 10:58:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]
GO
USE [DatamartDB2]
GO
/****** Object: StoredProcedure [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1] Script Date: 06/12/2013 13:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT 'MMC_SP_FoodNutritionOrdersReport_Test1'
DECLARE @Location varchar(255)
--if (ISNULL(@Location,'')='')
begin
Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)
SET @RptTimeFrameout='1' --Weekly
if @RptTimeFrameout='1' -- Daily
Begin
select @FromDate = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,0,getdate()),101))+ '00:00:00 AM')
select @ToDate = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'04:46:00 AM')
End
--DROP TABLE #Result
SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate into #Result
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%Order NPO%'
AND "BLOrdersLog"."Text_str" IS NOT NULL
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")
WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
--AND "MO_Demographics"."MRN" = '41579622'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL
UNION
SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%diet%'
AND "BLOrdersLog"."Text_str" IS NOT NULL
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")
WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
--AND "MO_Demographics"."MRN" = '41579622'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL
UNION
SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%'
AND "BLOrdersLog"."Text_str" IS NOT NULL)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")
WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL
ORDER BY
"bllocation"."location_name" ASC
select rlt1.*
from #result rlt1
where dietorderedtime in
(select top 1 dietorderedtime
from #result rlt2
where rlt1.mrn=rlt2.mrn
order by dietorderedtime desc)
END
END
SET NOCOUNT OFF
SET ANSI_NULLS OFF
GO
GO
GRANT EXECUTE ON [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1] TO [Public]