Trying to Update SQL 2000 compute query to SQL 2005 using CTEs

[wbodger]

I have a few queries of this type that I think I would like to update to take advantage of CTE, but I really could use some help in getting started. Here is the code:

select rd.event_id,rd.insert_date, rd.num_spaces, cost_per_space, deposit_per_space, Total_Cost, amt_paid, (total_cost-amt_paid) AS balance_due, r.registration_id, e.ministry_id, r.contact_id 
from ycmhome.contactinfo ci join ycmhome.registration r on ci.contact_id = r.contact_id
join ycmhome.reg_details rd on r.registration_id = rd.registration_id
join ycmhome.events e on rd.event_id = e.event_id 
where ci.church_id = " & cid
and rd.event_id = " & eid
and r.isactive = 1
order by rd.event_id 
compute sum(rd.num_spaces), sum(total_cost), sum(amt_paid), sum(total_cost-amt_paid) by rd.event_id

I seem to remember that CTEs can be much better for aggregates than the compute sum and then rs.NextRecordset that I have always done with SQL 2000 and ASP. And following those lines, is it fairly straightforward how to access the resulting values from ASP when using CTEs?

Thanks for any help you can give me.

wb

 

[markros]

What is the current input and desired output?

Try

;with cte as (select rd.event_id,rd.insert_date, rd.num_spaces, cost_per_space, deposit_per_space, Total_Cost, amt_paid, (total_cost-amt_paid) AS balance_due, r.registration_id, e.ministry_id, r.contact_id 
from ycmhome.contactinfo ci join ycmhome.registration r on ci.contact_id = r.contact_id
join ycmhome.reg_details rd on r.registration_id = rd.registration_id
join ycmhome.events e on rd.event_id = e.event_id 
where ci.church_id = " & cid
and rd.event_id = " & eid
and r.isactive = 1)

select sum(Num_spaces) as [Total Spaces], SUM(Total_Cost) as [Total Cost], SUM(amt_paid) as [Total Paid],
SUM(Total_Cost -amt_paid) as [Total Profit], Event_ID from cte

PluralSight Learning Library

 

[wbodger]

Well, the current output looks like

event_id	insert_date	num_spaces	cost_per_space	deposit_per_space	Total_Cost	amt_paid	balance_due	registration_id	ministry_id	contact_id
315	2010-09-23 13:15:36.980	5	370	90	2000	0	2000	3800	1	2130
315	2010-10-05 12:52:08.517	6	370	90	2220	0	2220	3800	1	2130
315	2010-10-05 13:01:39.877	6	370	90	2220	0	2220	3800	1	2130
315	2011-04-01 10:47:21.283	-5	400	90	-2000	0	-2000	3800	1	2130
315	2011-04-06 09:31:49.497	-6	400	90	-2400	0	-2400	3800	1	2130
315	2011-04-06 10:19:54.377	-2	370	90	-740	0	-740	3800	1	2130

sum	sum	sum	sum
4	1300	0	1300

So, this works (after I removed Event_ID from the second Select), but only gives me the sums

4	1300	0	1300

Sorry that I was not more informative as to the full nature of the query and the data. I have a solution that works, but it does not seem like the best solution to me. I will put together a more complete view of the code and the data as soon as I can. In as much of a nutshell as I can, what I have is a recordset with multiple rows representing registration and drops and adds for a conference, by organization. I then want to total those four columns that are summed (as in the first code block above). I was hoping to be able to do this in one recordset because I have an issue at another point where there may or may not be records in another table corresponding to these registrations. I have already run the query to pull that data, including the computed sums, but I cannot do an EOF check on that recordset because with the compute clause in there it creates another empty recordset, so EOF is false and then it tries to display the data that does not exist. To get around this, I have checked for a recordcount>1, but I was hoping I could grab the full recordset including the sums in one complete query.

If I try just adding those fields into the select, I get

event_id	insert_date	num_spaces	cost_per_space	deposit_per_space	total_cost	amt_paid	balance_due	registration_id	ministry_id	contact_id	Total Spaces	Total Cost	Total Paid	Amount Due
315	2010-09-23 13:15:36.980	5	370	90	2000	0	2000	3800	1	2130	5	2000	0	2000
315	2010-10-05 12:52:08.517	6	370	90	2220	0	2220	3800	1	2130	6	2220	0	2220
315	2010-10-05 13:01:39.877	6	370	90	2220	0	2220	3800	1	2130	6	2220	0	2220
315	2011-04-01 10:47:21.283	-5	400	90	-2000	0	-2000	3800	1	2130	-5	-2000	0	-2000
315	2011-04-06 09:31:49.497	-6	400	90	-2400	0	-2400	3800	1	2130	-6	-2400	0	-2400
315	2011-04-06 10:19:54.377	-2	370	90	-740	0	-740	3800	1	2130	-2	-740	0	-740

which is not correct. I was perhaps incorrect in my lofty views of what can be done with a cte?

Thank you for your help, I really do appreciate it.

wb

 

[markros]

No, since you want to return two result sets back, I think you need to use a temp table for your first select, e.g.

select ...
into #TempResult
from ...
...

select * from #TempResult -- this is your first output

select sum()...
from #TempResult -- this is your second output

PluralSight Learning Library

 

[wbodger]

Hmm... I will play around with that and see what I come up with, thanks for the input!

wb