SQl Stored Procedure/ Parameter Help

[FlyerNut]

I'm running Crystal 9.0, SQL 2k DB.

I am using 2 parameters (start and end) to retrieve data based on date for two separate tables. The query basically says give me jobs that have a first insertion at the time provided, as well as the work that was done on any jobs during that same time period. Using query analyzer I can pass in the dates and the query runs fine.

I'm not sure how to build the paramters/record selection criteria/formula correctly in Crystal,

The following is the stored procedure in question:

CREATE procedure dbo.RPTDateTracking
@start datetime,
@end datetime
as

select jbtb.jbid, sitb.sina,
case when datepart(dw,altb.alts)= 4 then altb.alts else null end as Wed,
case when datepart(dw,altb.alts)= 5 then altb.alts else null end as Thurs,
case when datepart(dw,altb.alts)= 6 then altb.alts else null end as Fri,
case when datepart(dw,altb.alts)= 7 then altb.alts else null end as Sat,
case when datepart(dw,altb.alts)= 1 then altb.alts else null end as Sun,
case when datepart(dw,altb.alts)= 2 then altb.alts else null end as Mon,
case when datepart(dw,altb.alts)= 3 then altb.alts else null end as Tues,
intb.pbid, pbtb.pbna, intb.zgid, zgtb.zgna,
altb.alts,intb.indl, intb.indt as mindate, jbna from jbtb
join jptb on jptb.jpid = jbtb.jbid
JOIN intb ON intb.InId = (select min(intb.inid) from intb where intb.JpId=jptb.JpId
and intb.InDt = (select min(intb.InDt) from intb where
intb.jpid=Jptb.jpid))


join altb on altb.alid = (select min(altb.alid) from altb where altb.jpid=jbtb.JbId
and altb.alts = (select min(altb.alts) from altb where
altb.jpid=jbtb.jbid and stid = 10020 and altb.alts >= @start and altb.alts <= @end))


join pbtb on pbtb.pbid = intb.pbid
join zgtb on zgtb.zgid = intb.zgid
join fstb on fstb.fsid = jptb.fsid
join smtb on smtb.smid = fstb.smid
join sitb on sitb.siid = smtb.siid


where InDt >= @start and Indt <= @end

 

[MColeman]

When you setup the stored procedure as the data source for the report, Crystal sets up the stored procedure parameters in the report as report parameters for you.

 

[FlyerNut]

Thanks, I have one more question.

If the value that I'm passing is a datetime, can I construct something so that a user only needs to place in the date, instead of both the date and time?

 

[vidru]

You'd have to handle it in the procedure. I do it like this:
[TT]
CREATE PROCEDURE tstProc
@BeginDate DATETIME,
@EndDate DATETIME
AS

/* "Chop off" the times passed from Crystal */
SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@BeginDate, 101))
SELECT @EndDate = CONVERT(DATETIME, CONVERT(CHAR(10),@EndDate, 101))
/* go the last second on the last date */
SELECT @EndDate = DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate))

SELECT @BeginDate,@EndDate[/TT]

-dave

 

[FlyerNut]

Dave,

Thanks for the reply. The date variables I pass are part of the where criteria in the stored procedure.

I'm not sure how to roll in your idea to the stored procedure I have posted.

Could you give me an idea where to start based on your idea and my stored procedure? I'd just like a starting point so that I might be able to figure it out and understand it.

Thanks!

Tony

 

[vidru]

Tony,

This is how you would incorporate the above example into your posted procedure:

CREATE procedure dbo.RPTDateTracking
@start datetime,
@end datetime
as
declare
@BeginDate DATETIME,
@EndDate DATETIME

/* "Chop off" the times passed from Crystal */
SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@start, 101))
SELECT @EndDate = CONVERT(DATETIME, CONVERT(CHAR(10),@end, 101))
/* go the last second on the last date */
SELECT @EndDate = DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate))

select   jbtb.jbid, sitb.sina,
case when datepart(dw,altb.alts)= 4  then altb.alts else null end as Wed,
case when datepart(dw,altb.alts)= 5  then altb.alts else null end as Thurs,
case when datepart(dw,altb.alts)= 6  then altb.alts else null end as Fri,
case when datepart(dw,altb.alts)= 7  then altb.alts else null end as Sat,
case when datepart(dw,altb.alts)= 1  then altb.alts else null end as Sun,
case when datepart(dw,altb.alts)= 2  then altb.alts else null end as Mon,
case when datepart(dw,altb.alts)= 3  then altb.alts else null end as Tues,
intb.pbid, pbtb.pbna,  intb.zgid, zgtb.zgna, 
altb.alts,intb.indl, intb.indt as mindate,  jbna from jbtb
join jptb on jptb.jpid = jbtb.jbid
JOIN intb ON intb.InId = (select min(intb.inid) from intb where intb.JpId=jptb.JpId
and intb.InDt =  (select min(intb.InDt) from intb where 
intb.jpid=Jptb.jpid))


join altb on altb.alid = (select min(altb.alid) from altb where altb.jpid=jbtb.JbId
and altb.alts =  (select min(altb.alts) from altb where 
altb.jpid=jbtb.jbid and stid = 10020 and altb.alts >= @BeginDate and altb.alts <= @EndDate))

join pbtb on pbtb.pbid = intb.pbid 
join zgtb on zgtb.zgid = intb.zgid
join fstb on fstb.fsid = jptb.fsid
join smtb on smtb.smid = fstb.smid
join sitb on sitb.siid = smtb.siid

where InDt >= @BeginDate and Indt <= @EndDate

If you plan to use this kind of routine often (as I do), you can create a procedure or function that can be called from any procedure to set the dates/times accordingly.

-dave