need a help with sub query getting group ereor, please correct

[manbob]

I am using sql server report builder. I am getting group error , please help

select
opencloseid
, max(datetimestamp) as open_date
, prev_edisposition
, closed_date
from (


select
a.opencloseid,
a.datetimestamp,
a.edisposition as prev_edisposition,
min(b.datetimestamp) as closed_date

from
enrollmentshistory a
left join enrollmentshistory b
on a.opencloseid = b.opencloseid
and a.datetimestamp < b.datetimestamp
where
a.EDISPOSITION = 'Final Approval Ready'
and b.datetimestamp >=(CURRENT_TIMESTAMP-35)
group by a.opencloseid, a.datetimestamp,a.edisposition
UNION
select
a.opencloseid,
a.datetimestamp,
a.edisposition as prev_edisposition,
NULL as closed_date

from
enrollmentshistory a
left join enrollmentshistory b
on a.opencloseid = b.opencloseid
and a.datetimestamp < b.datetimestamp
where
a.EDISPOSITION = 'Final Approval Ready'
and b.datetimestamp is NULL
group by a.opencloseid, a.datetimestamp,a.edisposition


)
group by opencloseid, prev_edisposition, closed_date

 

[johnherman]

I'm thinking you may need an alias for the query within the parentheses.


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[carp]

I don't know about SQLServer, but I have run into problems in the past when I try to do grouping on a UNION in a subquery in Oracle.
The workaround that seemed to take care of it was to add yet another layer of subquery:

select
opencloseid
, max(datetimestamp) as open_date
, prev_edisposition
, closed_date
from 
SELECT * FROM 
(
select
a.opencloseid,
a.datetimestamp,
a.edisposition as prev_edisposition,
min(b.datetimestamp) as closed_date

from
enrollmentshistory a
left join enrollmentshistory b
on a.opencloseid = b.opencloseid
and a.datetimestamp < b.datetimestamp
where
a.EDISPOSITION = 'Final Approval Ready'
and b.datetimestamp >=(CURRENT_TIMESTAMP-35)
group by a.opencloseid, a.datetimestamp,a.edisposition
UNION
select
a.opencloseid,
a.datetimestamp,
a.edisposition as prev_edisposition,
NULL as closed_date

from
enrollmentshistory a
left join enrollmentshistory b
on a.opencloseid = b.opencloseid
and a.datetimestamp < b.datetimestamp
where
a.EDISPOSITION = 'Final Approval Ready'
and b.datetimestamp is NULL
group by a.opencloseid, a.datetimestamp,a.edisposition
)
)
group by opencloseid, prev_edisposition, closed_date

This may or may not work for you, and if it does, I'm afraid I can't tell you why - I was grasping at straws when I figured out this approach. But sometimes dumb luck is better than no luck!