Not sure what Toad is, nor a windowed function, but that doesn't sound like something that CR can use. CR uses pretty much plain vanilla SQL. If you want to do something special on the server for the data, I would use a stored proc that has a better chance of being understood by the server, and just return the dataset to CR.

HTH

"CR uses pretty much plain vanilla SQL" thi means complex queries I'm using will never work?!

"I would use a stored proc that has a better chance of being understood by the server, and just return the dataset to CR"

Totally agree whith this, I'll consider your reply and present the situation to my leaders!

Thanks a lot HTH!

Here we go:

SELECT * FROM (
select
ROW_NUMBER() OVER (ORDER BY thedate ASC) AS rownumber,
sum(delay) as delay, label, thedate as start_date, DATEADD(hour, {?StepPara},thedate) AS stop_date from (

SELECT (

SELECT DiffDateA + DiffDateB + DiffDateC + DiffDateD
FROM

(
-- A case
select
case
(
select COUNT(*)
from dly_delay
--FORMAT (GETDATE (), 'yyyy/M/dd HH:00:00', 'en-US')
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
)
when 0 then 0
else
(
select DATEDIFF(MI,FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') ,end_delay) AS DiffDateA --start date
from dly_delay
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
)
end AS DiffDateA
) A

,

(
-- B case
select
case
(
select COUNT(*)
from dly_delay
where start_delay > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
when 0 then 0
else
(
select SUM(DATEDIFF(MI,start_delay, end_delay)) AS DiffDateB
from dly_delay
where start_delay > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
end AS DiffDateB
) B

,

(
-- C case
select
case
(
select COUNT(*)
from dly_delay
where start_delay < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
when 0 then 0
else
(
select DATEDIFF(MI,start_delay, FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US')) AS DiffDateC --end date
from dly_delay
where start_delay < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
end AS DiffDateC


) C

,

(
-- D case
select
case
(
select COUNT(*)
from dly_delay
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
when 0 then 0
else
(
select DATEDIFF(MI,FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') , FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US')) AS DiffDateD
from dly_delay
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
end AS DiffDateD
) D



) AS delay
     , (DATEPART(HOUR, START_DELAY)) AS label
     , START_DELAY AS thedate
     FROM (

SELECT thedate AS start_delay
      , DATEADD(hour, {?StepPara}, thedate) AS end_delay
FROM dbo.ExplodeDates2({?StopPara},DATEADD(hour, -{?StepPara}, {?StartPara}))

) mainDelay
     WHERE START_DELAY>={?StopPara} AND END_DELAY<={?StartPara}
     GROUP BY DATEPART(MONTH, START_DELAY)
               , DATEPART(DAY, START_DELAY)
               , (DATEPART(HOUR, START_DELAY) / {?StepPara})
               , START_DELAY
      , END_DELAY



UNION

-- add the gaps


(
SELECT 0 AS delay
     , (DATEPART(HOUR, thedate)) AS label
     , thedate AS thedate
FROM dbo.ExplodeDates2({?StopPara},{?StartPara})

EXCEPT

SELECT (

SELECT DiffDateA + DiffDateB + DiffDateC + DiffDateD
FROM

(
-- A case
select
case
(
select COUNT(*)
from dly_delay
--FORMAT (GETDATE (), 'yyyy/M/dd HH:00:00', 'en-US')
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
)
when 0 then 0
else
(
select DATEDIFF(MI,FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') ,end_delay) AS DiffDateA --start date
from dly_delay
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
)
end AS DiffDateA
) A

,

(
-- B case
select
case
(
select COUNT(*)
from dly_delay
where start_delay > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
when 0 then 0
else
(
select SUM(DATEDIFF(MI,start_delay, end_delay)) AS DiffDateB
from dly_delay
where start_delay > FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
end AS DiffDateB
) B

,

(
-- C case
select
case
(
select COUNT(*)
from dly_delay
where start_delay < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
when 0 then 0
else
(
select DATEDIFF(MI,start_delay, FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US')) AS DiffDateC --end date
from dly_delay
where start_delay < FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
end AS DiffDateC


) C

,

(
-- D case
select
case
(
select COUNT(*)
from dly_delay
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
when 0 then 0
else
(
select DATEDIFF(MI,FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') , FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US')) AS DiffDateD
from dly_delay
where start_delay < FORMAT(mainDelay.START_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --start date
    and end_delay   > FORMAT(mainDelay.END_DELAY, 'yyyy/M/dd HH:00:00', 'en-US') --end date
)
end AS DiffDateD
) D




) AS delay
     , (DATEPART(HOUR, START_DELAY)) AS label
     , START_DELAY AS thedate
     FROM (
SELECT thedate AS start_delay
      , DATEADD(hour, {?StepPara}, thedate) AS end_delay
FROM dbo.ExplodeDates2({?StopPara},DATEADD(hour, -{?StepPara}, {?StartPara}))
) mainDelay
     WHERE START_DELAY>={?StopPara} AND END_DELAY<={?StartPara}
     GROUP BY DATEPART(MONTH, START_DELAY)
               , DATEPART(DAY, START_DELAY)
               , (DATEPART(HOUR, START_DELAY) / {?StepPara})
               , START_DELAY
      , END_DELAY
               )
) a
GROUP BY label, thedate
) AS foo
WHERE rownumber % {?StepPara} = 0

ok, that doesn't seem too bad, though it is long...
I don't know if CR understands Row_number function, and I don't know if there is a limit to how long the select can be (again, I basically only use stored procs to drive reports) but either of those issues could be causing the issue that you are seeing (ie, nothing selected in the report)

HTH

When mentioning SP - "stored procedures" you mean to add a SP in the SQL engine?!
So a query in Crystal will be shorter. Also a view could solve my issue I guess, but as I am not the DB administrator things get wrong. But anyway guys thanks for replies, I'll suggest the solution to our DB administrator.