select sc#,sum(qty) as qty from (
select sc#,sum(qty) as qty from T1 where sc# in(select left(sc#,3) from T1
where hs='Y') group by sc#
union
select left(sc#,3) as sc#,sum(qty) as qty from t1
where hs='Y' group by a.do#,left(a.sc#,3)
) as a group by do#,sc#
select sc#,sum(qty) as qty from T1 where sc# in(select left(sc#,3) from T1
where hs='Y') group by sc#
union
select left(sc#,3) as sc#,sum(qty) as qty from t1
where hs='Y' group by a.do#,left(a.sc#,3)
) as a group by do#,sc#
from tb
whwere charindex('-', sc#)=0 or hs='Y'
group by left(sc#,4)