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.
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:
count(*) as [COUNT],
where rmpropid = '123'
and classid = 'A1'
group by rmpropid,
0 as [COUNT],
getdate() as dtmready
group by classid
having min(dtmready) > getdate()
order by dtmready;
If it's not practically useful, then it's practically useless.