FTP | CCD | Buscar | Trucos | Trabajo | Foros |
#1
|
|||
|
|||
Estado de Cuenta
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 |
#2
|
||||
|
||||
Herramientas | Buscar en Tema |
Desplegado | |
|
|
Temas Similares | ||||
Tema | Autor | Foro | Respuestas | Último mensaje |
Error en cuenta/Login/cuenta robada | coso___ | La Taberna | 17 | 24-02-2009 17:30:14 |
no me doy cuenta.... | Patricio | Varios | 4 | 21-04-2008 21:24:20 |
Cuenta bien! | roman | Humor | 9 | 25-04-2006 16:19:14 |
Que paso con mi cuenta? | =D-MO | Varios | 5 | 05-12-2005 07:02:03 |
|