Query Estado de Cuenta Cliente Estoy elaborando query de 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,GET DATE()-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,GET DATE()-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,GE TDATE()-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,GE TDATE()-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,GE TDATE()-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,GE TDATE()-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,GET DATE()-1), RECEIVABLES_RECEIVABLE.INVOICE_TYPE,RECEIVABLES_RE CEIVABLE.CUSTOMER_ID
Gracias
