Crystal report help

[reacha]

SELECT COUNT(CASE WHEN (ID IS NOT NULL AND DISP IN ('ANS','ABAN')) THEN 1 ELSE NULL END)off,
COUNT(CASE WHEN (ID IS NOT NULL AND DISP IN ('ANS')) THEN 1 ELSE NULL END) HAN,
COUNT(CASE WHEN (ID IS NOT NULL AND DISP IN ('ABAN')) THEN 1 ELSE NULL END)ABAN
FROM table1
WHERE CALLSTARTTIME >= :CALLSTART and CALLSTARTTIME <= :CALLEND


I need a report which shows data for each and every one hour for day so that if i need the report to run for every one hr.

Suppose i want to run the report from morning 9am to 9pm

morning 9am to 10am data i need to get at 10:15
morning 10am to 11am data i need to get at 11:15
..

Please help me out with the query and the report


Thanks,
reacha

 

[Madawc]

You seem to be writing stuff for Cystal that Cyrstal will do for you. That applies for counts, see FAQ767-6524 if you're not already familiar with them.

No need to add things to the SELECT statement, there are better ways.

You can also group by time, this is done using Datepart and Dateadd, which includes time functions.

If you want things to run at a particular time, Crystal Reports can't do that. It might be possible in Crystal Enterprise, I don't have access to it.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[IanWaterman]

As Madawc says you can just bring the table into Crystal and perform the counts in the report footer.

YOu can replace CALLSTART and CALLend with datetime parameters and then filter data as required.

Ian