|
sharona
Yak Posting Veteran
75 Posts |
 Posted - 2011-03-01 : 10:14:06
|
| i have a union query which needs to feed a temp table and use date parameters. i am declaring my parameters, i keep getting an errorMsg 137, Level 15, State 2, Line 5Must declare the scalar variable "@Start_Date".Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@End_Date".DECLARE @startdate datetime, @enddate datetime--use convert to mm/dd/yyyy with default Start_time 12:00:000 and default End_time 11:59:59SELECT @startdate = convert(datetime,convert(varchar(10), @Start_Date, 101)) SELECT @enddate = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @End_Date, 101))))DECLARE @RRTRN2 TABLE( MRN CHAR(20), ENCNUMBER CHAR(20), PATIENT CHAR(50), ADMIT_DATE DATETIME, DISCHARGE_DATE DATETIME, Unit_Note_Written CHAR(50), CURRENT_LOCATION_TIME_RPT CHAR(50), DOC_NAME CHAR(60), OBS_DOC_NAME CHAR(60), Value_text CHAR(2000), AUTHOR_DATE DATETIME, RECORD_DATE DATETIME)INSERT INTO @RRTRN2SELECT DISTINCT CV.IDCode AS MRN, CV.VisitIDCode AS EncNumber, CV.ClientDisplayName AS PATIENT, CV.AdmitDtm AS ADMIT_DATE, CV.DischargeDtm AS DISCHARGE_DATE, Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,OD.RecordedDtm), CV.CurrentLocation AS CURRENT_LOCATION_TIME_RPT, CD.DocumentName AS DOC_NAME, OBC.Name AS OBS_DOC_NAME, ISNULL(OB.ValueText, SCMO.Value) AS Value_text, CD.AuthoredDtm AS AUTHOR_DATE, OD.RecordedDtm AS RECORD_DATE FROM CV3Client AS CL WITH (nolock) INNER JOIN CV3ClientID AS CID WITH (nolock) ON CL.GUID = CID.ClientGUID INNER JOIN CV3ClientDocument AS CD WITH (nolock) ON CD.ClientGUID = CID.ClientGUID and CD.PatCareDocGUID = '9000001709102020' INNER JOIN CV3ClientVisit AS CV WITH (nolock) ON CV.GUID = CD.ClientVisitGUID AND CID.ClientGUID = CV.ClientGUID INNER JOIN CV3Location AS Loc ON CV.CurrentLocationGUID = loc.GUID and loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001') INNER JOIN CV3ObservationDocument AS OD WITH (nolock) ON OD.OwnerGUID = CD.GUID INNER JOIN CV3Observation AS OB WITH (nolock) ON OB.GUID = OD.ObservationGUID INNER JOIN CV3ObsCatalogItem AS OBC WITH (nolock) ON OBC.GUID = OB.ObsItemGUID AND OBC.Name in ( 'note_rapid_resp_recommendations_ft5') INNER JOIN CV3ObsCatalogMasterItem AS OBCI WITH (nolock) ON OBCI.GUID = OBC.MasterObsGUID LEFT OUTER JOIN SCMObsFSListValues AS SCMO WITH (nolock) ON SCMO.ParentGUID = OD.ObservationDocumentGUIDWHERE CD.AuthoredDtm between @START_DATE and @END_DATE union allSELECT DISTINCT CV.IDCode AS MRN, CV.VisitIDCode AS EncNumber, CV.ClientDisplayName AS PATIENT, CV.AdmitDtm AS ADMIT_DATE, CV.DischargeDtm AS DISCHARGE_DATE, Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,OD.RecordedDtm), CV.CurrentLocation AS CURRENT_LOCATION_TIME_RPT, CD.DocumentName AS DOC_NAME, OBC.Name AS OBS_DOC_NAME, ISNULL(OB.ValueText, SCMO.Value) AS Value_text, CD.AuthoredDtm AS AUTHOR_DATE, OD.RecordedDtm AS RECORD_DATE FROM CV3Client AS CL WITH (nolock) INNER JOIN CV3ClientID AS CID WITH (nolock) ON CL.GUID = CID.ClientGUID INNER JOIN CV3ClientDocument AS CD WITH (nolock) ON CD.ClientGUID = CID.ClientGUID and CD.PatCareDocGUID = '9000001709102020' INNER JOIN CV3ClientVisit AS CV WITH (nolock) ON CV.GUID = CD.ClientVisitGUID AND CID.ClientGUID = CV.ClientGUID INNER JOIN CV3Location AS Loc ON CV.CurrentLocationGUID = loc.GUID and loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001') INNER JOIN CV3ObservationDocument AS OD WITH (nolock) ON OD.OwnerGUID = CD.GUID INNER JOIN CV3Observation AS OB WITH (nolock) ON OB.GUID = OD.ObservationGUID INNER JOIN CV3ObsCatalogItem AS OBC WITH (nolock) ON OBC.GUID = OB.ObsItemGUID AND OBC.Name in ('note_rapid_resp_recommendations_ft1') INNER JOIN CV3ObsCatalogMasterItem AS OBCI WITH (nolock) ON OBCI.GUID = OBC.MasterObsGUID LEFT OUTER JOIN SCMObsFSListValues AS SCMO WITH (nolock) ON SCMO.ParentGUID = OD.ObservationDocumentGUIDWHERE CD.AuthoredDtm between @START_DATE and @END_DATE |
|
union query temp table and date parameters