Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

manbob

Technical User
Dec 6, 2016
1
0
0
US
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

 
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


 
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:
Code:
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top