Consulta sql Clientes antigüedad

FranciscoAn · 13 Julio, 2018 14:24

Buenos días,

Favor grupo si alguien puede orientarme seria de mucha ayuda.
La cuestión es que tengo una consulta donde se muestra el top 15 de los clientes con mayor deuda ,desglosado por rangos de tiempo.

SELECT TOP 15
T0."CardName" AS "Clientes",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" 
AND T1."PaidSys" = 0 AND T1."DocDueDate" >= CURRENT_DATE AND T1."CANCELED" = 'N') AS "No Vencido",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" 
AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 30 AND T1."CANCELED" = 'N') AS " 0 - 30",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=31 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 60 AND T1."CANCELED" = 'N') AS " 31      -      60 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=61 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 90 AND T1."CANCELED" = 'N') AS " 61      -      90 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=91 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 180 AND T1."CANCELED" = 'N') AS " 91      -      180 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=181 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 360  AND T1."CANCELED" = 'N') AS " 181      -      360 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=361 AND T1."CANCELED" = 'N') AS "       360+       ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND T1."CANCELED" = 'N')
 AS "Vencido",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."CANCELED" = 'N') AS "TOTAL"


FROM OINV T0 WHERE T0."PaidSys" = 0  

GROUP BY T0."CardName"

ORDER BY SUM(T0."DocTotal") DESC

Ahora bien, para completar esta consulta necesito en la linea 16 agregar el resto de clientes, pero en esa sola fila, todos los demas clientes agrupados en esa fila, con el nombre que sea. y que de igual forma se muestren los totales en las diferentes columnas correspondiente a este grupo de clientes.

Saludos a tod@s

Gracias

andresramirez · 13 Julio, 2018 14:51

Hola Francisco
Duplica tu consulta actual y aplica los ajustes que necesitas para generar la ultima linea.
La consulta actual(top 15 de clientes) con la nueva consulta(el resto de clientes) la consolidas con un UNION.
Ten en cuenta que HANA no permite utilizar ORDER BY en las consultas que utilizas para los UNION.

Andres Ramirez Jaramillo

FranciscoAn · 13 Julio, 2018 20:56

@andresramirez tengo las dos consultas, (el top15) y (el resto), antes de hacer el union, necesito convertir la consulta (el resto) a una sola fila que sea la sumatoria de todos esos registros. Me pueden orientarcon esto por favor.

la consulta es la siguiente,



SELECT
X."Clientes"
from

(SELECT TOP 15
T0."CardName" AS "Clientes",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" 
AND T1."PaidSys" = 0 AND T1."DocDueDate" >= CURRENT_DATE AND T1."CANCELED" = 'N') AS "No Vencido",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" 
AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 30 AND T1."CANCELED" = 'N') AS " 0 - 30",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=31 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 60 AND T1."CANCELED" = 'N') AS " 31      -      60 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=61 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 90 AND T1."CANCELED" = 'N') AS " 61      -      90 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=91 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 180 AND T1."CANCELED" = 'N') AS " 91      -      180 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=181 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 360  AND T1."CANCELED" = 'N') AS " 181      -      360 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=361 AND T1."CANCELED" = 'N') AS "       360+       ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND T1."CANCELED" = 'N')
AS "Vencido",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."CANCELED" = 'N') AS "TOTAL"

FROM OINV T0 WHERE T0."PaidSys" = 0  

GROUP BY T0."CardName"

ORDER BY SUM(T0."DocTotal") DESC
)X
)

GROUP BY T0."CardName"

ORDER BY SUM(T0."DocTotal") DESC

Gracias de antemano

@businessone

andresramirez · 13 Julio, 2018 21:10

Francisco, lo tenias casi listo.

SELECT
'resto de clientes', SUM(X."No Vencido"), SUM(" 0 - 30")
from

(
SELECT TOP 15
T0."CardName" AS "Clientes",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" 
AND T1."PaidSys" = 0 AND T1."DocDueDate" >= CURRENT_DATE AND T1."CANCELED" = 'N') AS "No Vencido",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" 
AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 30 AND T1."CANCELED" = 'N') AS " 0 - 30",

(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=31 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 60 AND T1."CANCELED" = 'N') AS " 31      -      60 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=61 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 90 AND T1."CANCELED" = 'N') AS " 61      -      90 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=91 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 180 AND T1."CANCELED" = 'N') AS " 91      -      180 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=181 AND DAYS_BETWEEN ( CURRENT_DATE, T1."DocDueDate") <= 360  AND T1."CANCELED" = 'N') AS " 181      -      360 ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND DAYS_BETWEEN (CURRENT_DATE, T1."DocDueDate") >=361 AND T1."CANCELED" = 'N') AS "       360+       ",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."DocDueDate" < CURRENT_DATE AND T1."CANCELED" = 'N')
AS "Vencido",
(SELECT SUM (T1."DocTotal") FROM OINV T1 WHERE T1."CardName" = T0."CardName" AND T1."PaidSys" = 0 AND T1."CANCELED" = 'N') AS "TOTAL"

FROM OINV T0 WHERE T0."PaidSys" = 0 
GROUP BY T0."CardName"
ORDER BY SUM(T0."DocTotal") DESC
)X

Saludos,
Andres Ramirez Jaramillo

FranciscoAn · 13 Julio, 2018 22:13

Si, se me prendió el bombillo a ultima hora. lo hice de esta forma. Pero todavía me faltan algunos detalles. la semana que viene lo termino y les comento.
Muchas gracias.

Finalmente hice dos consultas. Ya que no las pude unir. En la primera genere el listado de los top 15 con su información de antigüedad. Y en la otra consulta hice un SUM, donde agrupe la información de antigüedad del resto de los registros, desde el 16 en adelante.

Luego estas consultas las subi al crystal report y arme los informe que necesitaba.

Gracias por sus aportes.

system · 20 Julio, 2018 22:13

Este tema se cerró automáticamente 7 días después del último post. No se permiten nuevas respuestas.