How to replace compute clause

[wbodger]

I have this query

select rd.insert_date, cost_per_space, deposit_per_space, rd.num_spaces, isnull(processing_fee,0) as processing_fee, Total_Cost, amt_paid, (total_cost+isnull(processing_fee,0)-amt_paid) AS balance_due
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 = 242 and rd.event_id = 461 and r.isactive = 1 and e.isactive = 1
order by rd.event_id
compute sum(rd.num_spaces), sum(isnull(processing_fee,0)), sum(total_cost), sum(amt_paid), sum(total_cost+isnull(processing_fee,0)-amt_paid) by rd.event_id

that returns this

2014-10-30 20:13:59.393	399	100	2	4.00	798	0	802.00

2	4.00	798	0	802.00

So I tried it with rollup

select rd.insert_date, cost_per_space, deposit_per_space, rd.num_spaces, isnull(processing_fee,0) as processing_fee, Total_Cost, amt_paid, (total_cost+isnull(processing_fee,0)-amt_paid) AS balance_due
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 = 242 and rd.event_id = 461 and r.isactive = 1 and e.isactive = 1
group by rd.insert_date, cost_per_space, deposit_per_space, rd.num_spaces, isnull(processing_fee,0) as processing_fee, Total_Cost, amt_paid, (total_cost+isnull(processing_fee,0)-amt_paid) with rollup

I have tried with Rollup and Cube and cannot seem to get the same result

2014-10-30 20:13:59.393	399	100	2	4.00	798	0	802.00
2014-10-30 20:13:59.393	399	100	2	4.00	798	0	802.00
2014-10-30 20:13:59.393	399	100	2	4.00	798	NULL	NULL
2014-10-30 20:13:59.393	399	100	2	4.00	NULL	NULL	NULL
2014-10-30 20:13:59.393	399	100	2	NULL	NULL	NULL	NULL
2014-10-30 20:13:59.393	399	100	NULL	NULL	NULL	NULL	NULL
2014-10-30 20:13:59.393	399	NULL	NULL	NULL	NULL	NULL	NULL
2014-10-30 20:13:59.393	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

What am I missing?

Thanks,
Willie

 

[johnherman]

Note that there is a forum for MS Analysis Services, so maybe try your question there. forum960

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)

 

[wbodger]

I can try over there, but I look at this as more of a programming issue as I have never used this logic in SSAS.

wb

 

[fredericofonseca]

Johnherman this is T-Sql -

http://technet.microsoft.com/en-us/library/ms181708(v=sql.105).aspx

Wbodger - you should now be looking into using rollup instead (as you have tried). how to do it I am not sure as I normally do not use these and no time at the moment to try it out so hopefully someone else will give you a hand here

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

Samples for rollup explain it as an extension to group by of nested groups as year, month, day.
You don't have nested groups as you mainly group by a datetime. Means you have one record per group only. There's nothing to roll up.
A Cube will give you even more group combinations, but your main group criterion is not really grouping data, you have to think about something else, if you want a rollup or cube.

Bye, Olaf.

 

[gk53]

try something like that

; with cte as
(		
select rd.insert_date, 
	cost_per_space, 
	deposit_per_space, 
	rd.num_spaces, 
	isnull(processing_fee,0) as processing_fee, 
	Total_Cost, 
	amt_paid, 
	(total_cost+isnull(processing_fee,0)-amt_paid) AS balance_due
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 = 242 and rd.event_id = 461 and r.isactive = 1 and e.isactive = 1
	group by rd.insert_date, 
		cost_per_space, 
		deposit_per_space, 
		rd.num_spaces, 
		isnull(processing_fee,0) as processing_fee, 
		Total_Cost, amt_paid, 
		(total_cost+isnull(processing_fee,0)-amt_paid) with rollup 
)
select sum(num_spaces), 
		sum(isnull(processing_fee,0)), 
		sum(total_cost), 
		sum(amt_paid), 
		sum(total_cost+isnull(processing_fee,0)- amt_paid) 
		from cte