How to write the query using case type instead of Union

Question

Is there a way to write this part of the query using Case type instead of using Union?

select SalesRepAccId, PreSellingRoute, 0, 0, 0, 0, 0, 1'OrderPlaced',
count(oh.OrderRequestId)OrderCount, 0, 0, 0, 0
from OrderRequestHeader oh inner join SalesRepAcc s on oh.SalesRepAccId=s.id
where convert(varchar,TransactionDate,23)=convert(varchar,@apl ,23) and oh.Deleted=0
group by SalesRepAccId, PreSellingRoute

union

select SalesRepAccId, PreSellingRoute, 0, 0, 0, 0, 0, 0, 0, sum(SalesRepCount)SalesRepCount,
sum(Initial)Initial, sum(Miday)Miday, sum(DayEnd)DayEnd
from
(
select SalesRepAccId, PreSellingRoute, count(distinct SalesRepAccId)SalesRepCount,
0'Initial', 0'Miday', 0'DayEnd'
from @SyncDetails
group by SalesRepAccId, PreSellingRoute

Answer 1

I'm not sure what you have in mind with CASE here. But I don't think that is a good candidate here.

I did start to rewrite your query have the UNION in a CTE, and have all aggregation after the CTE. This is often a great way to write queries like these.

But when I looked closer, I found some issues in your query that made me stop. First, what you posted is not correct syntactically. There should be a closing parenthesis followed by an alias for the derived table. That's a trivial error, but it makes me uncertain if there is more missing.

Also, this:

select SalesRepAccId, PreSellingRoute, count(distinct SalesRepAccId)SalesRepCount
from @SyncDetails

Because you are grouping on SalesRepAccId, COUNT(DISTINCT SalesRepAccId) will always be 1, so you may have something else in mind.

Maybe you should continue to work with your query in its current form and make sure that it returns the correct result before you ask further.

Also:

where convert(varchar,TransactionDate,23)=convert(varchar,@apl ,23)

This is not wrong, but the conversion to varchar precludes the usage of any index on TransactionDate. Here is a better way to write the same thing:

   where  cast(TransactionDate AS date) =  cast(@date as date)