have multiple date parameters and not sure syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

have multiple date parameters and not sure syntax

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-02-04 : 12:37:56

i have the following sp. it uses 3 date parameters,
1 is associated with a function.
the other 2 are the parameters for the report
if i have the where statement set up as is i dont get my records.
what am i doing wrong.

this is my parameters
D.AdmitDtm between @start_date and @end_date
this is my function
convert(datetime,convert(varchar(10),E.RecordedDtm, 101))= @DATE

if you see they are referencing 2 seperate fields, how do i word it so it will pick up the date range on teh admit date
and the recorded date for the @date paramter, at this point it is one or the other.

alter PROCEDURE [dbo].[NYP_SHARON_rpt]
(@STARTDATE DATETIME,
@ENDDATE DATETIME)

DECLARE
@start_date datetime,
@end_date datetime,
@Date datetime,
@LocDate datetime,
@datetime datetime

--use convert to mm/dd/yyyy with default Start_time 12:00:000 and default End_time 11:59:59
SELECT @start_date = convert(datetime,convert(varchar(10), @StartDate, 101))
SELECT @end_date = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @EndDate, 101))))
select @Date = convert(datetime,convert(varchar(10), @Datetime, 101))
select @LocDate = dateadd(dd, 1,@Date)

--Drop table #LVAD
SELECT DISTINCT
D.ClientGUID,
D.GUID ClientVisitGUID,
D.IDCode MRN,
D.VisitIDCode AS EncNumber,
D.ClientDisplayName,
D.AdmitDtm,
D.DischargeDtm,
D.CurrentLocation,
loc.NAME as Unit_Name,
Unit = LEFT([dbo].[nyp_TF_GetLocationForEvent] (D.GUID, @LocDate), 4),
C.GUID AS ClientDocGUID,
E.ObservationDocumentGUID,
B.ClientIDCode,
C.DocumentName,
H.Name,
ISNULL(F.ValueText, I.Value) AS Value_text,
E.RecordedDtm,
ISNULL(I.GUID, E.ObservationDocumentGUID) AS SCMObsFSListValuesGUID

INTO #LVADST
FROM CV3Client AS A WITH (nolock) INNER JOIN
CV3ClientID AS B WITH (nolock) ON A.GUID = B.ClientGUID INNER JOIN
CV3ClientDocument AS C WITH (nolock) ON C.ClientGUID = B.ClientGUID INNER JOIN
CV3ClientVisit AS D WITH (nolock) ON D.GUID = C.ClientVisitGUID AND B.ClientGUID = D.ClientGUID INNER JOIN
CV3Location AS Loc ON D.CurrentLocationGUID = loc.GUID INNER JOIN
CV3ObservationDocument AS E WITH (nolock) ON E.OwnerGUID = C.GUID INNER JOIN
CV3Observation AS F WITH (nolock) ON F.GUID = E.ObservationGUID INNER JOIN
CV3ObsCatalogItem AS G WITH (nolock) ON G.GUID = F.ObsItemGUID INNER JOIN
CV3ObsCatalogMasterItem AS H WITH (nolock) ON H.GUID = G.MasterObsGUID LEFT OUTER JOIN
SCMObsFSListValues AS I WITH (nolock) ON I.ParentGUID = E.ObservationDocumentGUID

WHERE (C.PatCareDocGUID = 9000001709102020)
AND D.AdmitDtm between @start_date and @end_date
AND convert(datetime,convert(varchar(10),E.RecordedDtm, 101))= @DATE
AND G.Name in (
'note_rapid_resp_recommendations_ft5',
'note_rapid_resp_recommendations_ft1',
'note_rapid_resp_recommendations_ft3' ,
'note_rapid_resp_recommendations_ft7',
'note_rapid_resp_recommendations_ft8',
'note_rapid_resp_outcomes_ft3' ,
'note_rapid_resp_recommendations_ft2',
'note_rapid_resp_recommendations_ft4' ,
'note_rapid_resp_recommendations_ft6' ,
'note_rapid_resp_pt_loc')

ORDER BY E.ObservationDocumentGUID

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-02-04 : 12:39:12

i dont want to have to enter the @date value, i would like it to be picked up from the parameters, the dates may not be the same

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-04 : 12:48:57

If you take out the the date predicates from your WHERE clause do you get records back?

Your code seems reasonable, although you could miss one seconds worth of data. At first glance it appears that it should work.

I'd also suggest you use date math to manipulate your dates and to NOT use string converstion.

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-02-04 : 13:00:20

yes i get records, the problem is i dont want the user to have to enter 3 date values, do i have to reference back to my start date, even though they may not be the same dates, or if i want a specific date to feed this function, i gather i have to enter the date as a parameter.
i cant use different conversions, i have to go with the format here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources