Extra Row Needed

Here is the basic sql I am trying to implement:

select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;

Here is my result set:

A1 3 2006-07-01 00:00:00.000
A1 10 2006-08-15 00:00:00.000
A1 11 2006-09-15 00:00:00.000
A1 10 2006-10-15 00:00:00.000
A1 10 2006-11-01 00:00:00.000
A1 10 2006-11-30 00:00:00.000

If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:

A1 0 (today's date)

Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.Try this:

select classid, count(*) as [COUNT], dtmready
into #tempunit
from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;

if ((select min(dtmready) from #tempunit)>getdate())
insert into #tempunit values ('a1','0',getdate())

select * from #tempunit

This should work?|||Thanks for the response.

I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem. Do you have any other ideas?

Thanks, Mike|||I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem.No temp tables? That is bogus. I can see them not wanting you to create permanent tables "temporarily", but there should be nothing wrong with creating true "temp" tables.
Regardless, here is another method:select classid,
count(*) as [COUNT],
dtmready
from unit
where rmpropid = '123'
and classid = 'A1'
group by rmpropid,
classid,
dtmready
UNION
select classid,
0 as [COUNT],
getdate() as dtmready
from unit
group by classid
having min(dtmready) > getdate()
order by dtmready;|||That did it! Thanks so much for your help!

Mike