need a help with sub query getting group ereor, please correct
need a help with sub query getting group ereor, please correct
(OP)
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
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
Talk To Other Members
RE: need a help with sub query getting group ereor, please correct
==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity
RE: need a help with sub query getting group ereor, please correct
The workaround that seemed to take care of it was to add yet another layer of subquery:
CODE