SQL Server Performance Forum – Threads Archive

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:
Select classid, count(*) as [COUNT], dtmready
From unit
where rmpropid = ‘123’ and classid = ‘A1’
group by rmpropid, classid, dtmready
UNION
SELECT classid=’A1′,[Count]=0,dtmready=Getdate()
order by dtmready;
Nathan H. Omukwenyi
MVP [SQL Server]

---

Also, there is no need to group by rmpropid.

---

Thanks for the idea. I think we are on the right track, but I made my example simpler than reality (isn’t this always true???). The reality has 2 additional items to consider: 1. There can be multiple classid’s, so hardcoding the ‘A1’ will not really work.
2. I only need the extra row if the minimum dtmready is > today Here is my actual sql: declare @now datetime;
set @now=getdate();
select
classid,
count(*) as [count],
(case when ALTVCODE != ‘LUP’ then @now else dtmready end) as READYDATE,
(case when altvcode = ‘LUP’ then ‘LUP’ else ‘OTHER’ END) as VCODE from unit where rmpropid = ‘REA’ group by classid, (case when ALTVCODE != ‘LUP’ then @now else dtmready end), (case when altvcode = ‘LUP’ then ‘LUP’ else ‘OTHER’ END) Results:
A1 32006-07-01 00:00:00.000LUP
A1 102006-08-15 00:00:00.000LUP
A1 112006-09-15 00:00:00.000LUP
A1 102006-10-15 00:00:00.000LUP
A1 102006-11-01 00:00:00.000LUP
A1 102006-11-30 00:00:00.000LUP
A1$ 22006-11-01 00:00:00.000LUP
A1$ 22006-11-30 00:00:00.000LUP
A2$ 32006-07-01 00:00:00.000LUP
A3$ 32006-08-15 00:00:00.000LUP
A3$ 22006-09-15 00:00:00.000LUP
A3$ 22006-10-15 00:00:00.000LUP
B1 12006-04-14 15:50:15.103OTHER
B1 52006-07-01 00:00:00.000LUP
B1 262006-08-15 00:00:00.000LUP
B1 242006-09-15 00:00:00.000LUP
B1 252006-10-15 00:00:00.000LUP
B1 102006-11-01 00:00:00.000LUP
B1 82006-11-30 00:00:00.000LUP
B1$ 32006-09-15 00:00:00.000LUP
B1$ 42006-10-15 00:00:00.000LUP
B1$ 22006-11-01 00:00:00.000LUP
B1$ 42006-11-30 00:00:00.000LUP
B2$ 52006-08-15 00:00:00.000LUP
B2$ 32006-09-15 00:00:00.000LUP
B2$ 12006-10-15 00:00:00.000LUP
B3$ 12006-09-15 00:00:00.000LUP
B3$ 22006-10-15 00:00:00.000LUP
T1 32006-05-19 00:00:00.000LUP
T1 72006-06-30 00:00:00.000LUP
T1$ 22006-06-30 00:00:00.000LUP

---

Then let us try to simply the answer so we can communicate it to you clearly.
I suggest to use this principle, using the initial simplified proposed solution: SELECT
classid,
[Count],
dtmready
FROM (
select
DISTINCT
rmpropid,
classid,
0 [Count],
getdate() dtmready,
getdate() readydate,
altvcode vcode
from unit
UNION
select
rmpropid,
classid,
count(*) as [count],
(case when ALTVCODE != ‘LUP’ then @now else dtmready end) as READYDATE,
(case when altvcode = ‘LUP’ then ‘LUP’ else ‘OTHER’ END) as VCODE
from unit
GROUP BY
rmpropid,
classid,
(case when ALTVCODE != ‘LUP’ then @now else dtmready end),
(case when altvcode = ‘LUP’ then ‘LUP’ else ‘OTHER’ END)
) UnitView
WHERE
rmpropid = ‘REA’ Please check the syntax but I think you get the idea.
Hope this helps.

---

by the way, add the distinct to remove duplicate in the final select.

---

I ended up doing something very similar to your suggestion. Thanks very much for your help. Mike

---

]]>