Need Extra Rows

Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What
I
need is to return an additional row when the earliest dtmready is after toda
y.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query ar
e
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 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> 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 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:[vbcol=seagreen]
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>
>[quoted text clipped - 52 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via droptable.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:[vbcol=seagreen]
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1