Need Help - prompt for beg date/end date and only pull in the range

[Chumley40]

I really need help in modifying this program to only pull results within a specified date range, any help would be appreciated.

DROP PROGRAM vccl_clinical_intervention_tfh GO
CREATE PROGRAM vccl_clinical_intervention_tfh

prompt
"Output to File/Printer/MINE" = "MINE" ;* Enter or select the printer or file name to send this report to.

with OUTDEV


;Request HNAM sign-on when executed from CCL on host
IF (VALIDATE(IsOdbc, 0) = 0) EXECUTE CCLSECLOGIN ENDIF

SET MaxSecs = 0
IF (VALIDATE(IsOdbc, 0) = 1) SET MaxSecs = 15 ENDIF
SELECT INTO $OUTDEV
P.NAME_FULL_FORMATTED,
E.LOC_FACILITY_CD,
E_LOC_FACILITY_DISP = UAR_GET_CODE_DISPLAY( E.LOC_FACILITY_CD ),
E.LOC_NURSE_UNIT_CD,
E_LOC_NURSE_UNIT_DISP = UAR_GET_CODE_DISPLAY( E.LOC_NURSE_UNIT_CD ),
E.LOC_ROOM_CD,
E_LOC_ROOM_DISP = UAR_GET_CODE_DISPLAY( E.LOC_ROOM_CD ),
E.LOC_BED_CD,
E_LOC_BED_DISP = UAR_GET_CODE_DISPLAY( E.LOC_BED_CD ),
PA.ALIAS_POOL_CD,
PA_ALIAS_POOL_DISP = UAR_GET_CODE_DISPLAY( PA.ALIAS_POOL_CD ),
PA.ALIAS,
EA.ALIAS_POOL_CD,
EA_ALIAS_POOL_DISP = UAR_GET_CODE_DISPLAY( EA.ALIAS_POOL_CD ),
EA.ALIAS,
O.UPDT_DT_TM "@MEDIUMDATETIME",
E.ENCNTR_ID,
O.EVENT_CD,
O_EVENT_DISP = UAR_GET_CODE_DISPLAY( O.EVENT_CD ),
O.EVENT_ID,
O.RESULT_VAL

FROM
OMF_CLINICAL_EVENT_ST O,
ENCOUNTER E,
PERSON P,
ENCNTR_ALIAS EA,
PERSON_ALIAS PA

PLAN O
;WHERE O.EVENT_CD =3129623
Where O.EVENT_CD in (3129593, 3129599, 3129602, 3129605, 3129608, 3129614, 3129611,
3129596, 3129620)

JOIN E
WHERE O.ENCNTR_ID = E.ENCNTR_ID

JOIN P
WHERE P.PERSON_ID = E.PERSON_ID



JOIN EA
WHERE EA.ENCNTR_ID = E.ENCNTR_ID
AND EA.ALIAS_POOL_CD = 3559679


JOIN PA
WHERE PA.PERSON_ID = P.PERSON_ID
AND PA.ALIAS_POOL_CD = 3559682



ORDER BY P.NAME_FULL_FORMATTED,
O.UPDT_DT_TM DESC

WITH MAXCOL = 300, MAXROW = 500 , DIO = 08

END
GO

 

[gmmastros]

There is no such thing as a program in SQL Server. I suspect you posted this in the wrong forum. Near the top left corner of this page, you will find a link for 'Forum List'. Click this link and then find the forum the corresponds to your database engine. Then, re-post this question in the proper forum.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

I don't know what your date values are but in you WHERE clause you would put

WHERE date_column BETWEEN @beginDate AND @endDate

I'm assuming you are going to pass a begin and end date in.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

and of course George is correct. there are no programs in SQL server.

- Paul
- Database performance looks fine, it must be the Network!

 

[bborissov]

Are you sure this is T-SQL Syntax?



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Chumley40]

Sorry, this is actually CCL. I work for a company called Cerner and it is what they use, but i have heard its just like SQL. I am actually borrowing this account from someone.. I really have a very very basic understanding of this stuff. I assume that what I have to do is prompt and ask for the start and end dates, and then stick a where statement in there that says between those...

does SQL automatically understand the january 1st is before january 2nd, or do I have to convert it into some number before it will recognize it?

thanks again for the help

 

[ptheriault]

SQL Server does understand that Jan 1 is before Jan 2.

- Paul
- Database performance looks fine, it must be the Network!

 

[gmmastros]

I am actually borrowing this account from someone/

Why are you borrowing someone elses account? Joining tek-tips is free. I encourage you to create your own account.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Not all SQL is created equal. SQL Server uses TSQL, Oracle uses SQL/Plus, Access uses Jet-SQL, etc. And all handle dates differently.

But for the most part, if dates are stored as a string then it will sort in dictionary order. If dates are stored in their date format (for SQL Server it's datetime and smalldatetime data types), then it should sort by the proper date order.

So, first you need to find out which database you are using. That will let you know which version of SQL it is and also where to post your question.

-SQLBill

Posting advice: FAQ481-4875