Estado de Cuenta

[HectorMM]

Estoy elaborando el estado de cuenta de cliente pero por antiguedad, pero resulta que me sale el detalle y no el consolidado que deseo (en una sola fila) así:

CUSTOMER_ID 0 - 30 Días 31 - 60 Días 61 - 90 Días
SVSSDILOPDAVI01
SVSSDILOPDAVI01 (348.21) 480.00 50.00
SVSSDILOPDAVI01 864.29 100.00 200.00
SVSSDILOPDAVI01 3,448.69
SVSSDILOPDAVI01 4,243.96
SVSSDILOPDAVI01 5,684.25

REQUERIDO:

CUSTOMER_ID 0 - 30 Días 31 - 60 Días 61 - 90 Días
SVSSDILOPDAVI01 13,892.98 580.00 250.00

Código:

SELECT DISTINCT
RECEIVABLES_RECEIVABLE.CUSTOMER_ID,
'1 - 30' = CASE
WHEN
DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1) < 31 AND
RECEIVABLES_RECEIVABLE.INVOICE_TYPE='INVOICE'
THEN
SUM(RECEIVABLES_RECEIVABLE.TOTAL_AMOUNT - RECEIVABLES_RECEIVABLE.TOTAL_PAID_AMOUNT)
WHEN
DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1) < 31 AND
RECEIVABLES_RECEIVABLE.INVOICE_TYPE='MEMO'
THEN
SUM(RECEIVABLES_RECEIVABLE.TOTAL_AMOUNT - RECEIVABLES_RECEIVABLE.TOTAL_PAID_AMOUNT)*-1
ELSE NULL
END,

'31 - 60' = CASE
WHEN
(DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1) BETWEEN 31 AND 60) AND
RECEIVABLES_RECEIVABLE.INVOICE_TYPE='INVOICE'
THEN
SUM(RECEIVABLES_RECEIVABLE.TOTAL_AMOUNT - RECEIVABLES_RECEIVABLE.TOTAL_PAID_AMOUNT)
WHEN
(DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1) BETWEEN 31 AND 60) AND
RECEIVABLES_RECEIVABLE.INVOICE_TYPE='MEMO'
THEN
SUM(RECEIVABLES_RECEIVABLE.TOTAL_AMOUNT - RECEIVABLES_RECEIVABLE.TOTAL_PAID_AMOUNT)*-1
ELSE NULL
END,

'61 - 90' = CASE
WHEN
(DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1) BETWEEN 61 AND 90) AND
RECEIVABLES_RECEIVABLE.INVOICE_TYPE='INVOICE'
THEN
SUM(RECEIVABLES_RECEIVABLE.TOTAL_AMOUNT - RECEIVABLES_RECEIVABLE.TOTAL_PAID_AMOUNT)
WHEN
(DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1) BETWEEN 61 AND 90) AND
RECEIVABLES_RECEIVABLE.INVOICE_TYPE='MEMO'
THEN
SUM(RECEIVABLES_RECEIVABLE.TOTAL_AMOUNT - RECEIVABLES_RECEIVABLE.TOTAL_PAID_AMOUNT)*-1
ELSE NULL
END

FROM LLFI.dbo.RECEIVABLES_RECEIVABLE RECEIVABLES_RECEIVABLE
WHERE (RECEIVABLES_RECEIVABLE.INVOICE_STATUS='OPEN') AND (RECEIVABLES_RECEIVABLE.CUSTOMER_ID = 'SVSSDILOPDAVI01')
GROUP BY
DATEDIFF(d,RECEIVABLES_RECEIVABLE.INVOICE_DATE,GETDATE()-1), RECEIVABLES_RECEIVABLE.INVOICE_TYPE,RECEIVABLES_RECEIVABLE.CUSTOMER_ID


Gracias

[Casimiro Notevi]

Te digo lo mismo que en el otro tema que has abierto

Saludos y bienvenido a clubdelphi