Use the ORDER BY in a select with ROLLUP

Question

Use the ORDER BY in a select with ROLLUP

Navigation

#1 by (1 votes)

0

I have a Stored procedure that brings me the following information:

Zone (used as a grouper in the rollup)
Store
Received Orders
Accepted Orders
Denied Orders
Percentage of acceptance

This information, my user wants to consult it in an aspx. My only problem, is that it requests that it be ordered by Total of orders BY ZONE ordering by zone next to their respective stores the closest thing to the last image. (That was done with reporting services and the report builder so it was easier to do the ordering that I require, but the user necessarily wants it in an aspx)

Annex the query that I am using for the query

set dateformat dmy
select id_tienda,SUBSTRING (ZONA, 6,15) as ZONA, a.PREFERENCIA,nombre_boutique,COUNT(id_tienda) as total_pedidos,isnull(SUM(aceptado),0) as aceptados,isnull(SUM(negado),'0') as negados,  
case when sum(aceptado)>0   then convert(numeric,isnull(SUM(aceptado),0)*100/(isnull(sum(aceptado),0)+isnull(sum(negado),0)),2)     else '0'  end as Porcentaje_Aceptacion 
into #Surtimento
from vw_estatus p inner join ALMACEN a on p.id_tienda = a.ID_TIENDA_EXTERNO where 1=1  and fecha_peticion >= @FechaI
and fecha_peticion <= @FechaF and comentario<>'CANCELACION AUTOMATICA-NO SE CUENTA CON STOCK EN EL SISTEMA' 
group by ZONA,nombre_boutique,id_tienda, a.PREFERENCIA 
ORDER BY ZONA,a.PREFERENCIA asc, COUNT(id_Tienda) desc


SELECT ZONA as Zona, 
id_tienda + ' - ' + Nombre_Boutique as Tienda, 
--PREFERENCIA as [Nivel Top], 
isnull(sum(total_pedidos),0) as [Total de Pedidos],
isnull(sum(aceptados),0) as [Pedidos Aceptados],
isnull(sum(negados),0) as [Pedidos Negados],
sum(Porcentaje_Aceptacion) as [% de Aceptación] FROM #Surtimento WHERE Porcentaje_Aceptacion Between @PorcentajeI and @PorcentajeF
group by rollup(zona,id_tienda + ' - ' + Nombre_Boutique)
order by sum(total_pedidos) desc

sql asp.net sql-server-2005

asked by Rodrigo Jimenez 06.02.2018 в 19:44

source

1 answer

Php for / switch Login with Value Objects without using all the fields in the table

score 1 · Accepted Answer

Without accessing your data it is a bit difficult to test code. Anyway, I would try to use SUM() OVER(PARTITION BY) :

SELECT  Zona, 
        id_tienda + ' - ' + Nombre_Boutique as Tienda, 
        ISNULL(SUM(total_pedidos),0) as [Total de Pedidos],
        ISNULL(SUM(aceptados),0) as [Pedidos Aceptados],
        ISNULL(SUM(negados),0) as [Pedidos Negados],
        SUM(Porcentaje_Aceptacion) as [% de Aceptación] 
FROM (  SELECT *, SUM(total_pedidos) OVER(PARTITION BY Zona) Total_Zona
        FROM #Surtimento) T 
WHERE Porcentaje_Aceptacion BETWEEN @PorcentajeI AND @PorcentajeF
GROUP BY ROLLUP(zona, id_tienda + ' - ' + Nombre_Boutique)
ORDER BY MIN(Total_Zona) DESC, SUM(total_pedidos) DESC
;