|
va lo solicitado….
es una sentencia
DECLARE @hoy AS DATETIME
DECLARE @tna AS FLOAT
SET @tna=20
SET @hoy=GETDATE()
SELECT cast(0 as bit) as generaMail, cast(0 as bit) as generaTratativa,
cod_tango,crm_clientes.id_cliente, razonSocial, CAST(saldo_cc AS DECIMAL(18,2))
AS deudaTotal,
coalesce((select top 1 crm_Vendedores.nombre from CRM_ClienteVendedor
inner join crm_vendedores on
crm_clienteVendedor.id_vendedor=crm_vendedores.id_vendedor where
id_cliente=crm_clientes.id_cliente and nombre like 'COBRANZA%'),'Sin Asignar')
as vendedor,
dbo.fngetdeudaCliente(Cod_Tango,{d'1900-01-01'},@hoy)
AS vencida,
dbo.fngetdeudaCliente(Cod_Tango,DATEADD(d,1,@hoy),{d'2200-01-01'}) AS
aVencer, CAST(CASE WHEN dbo.fngetdeudaCliente(Cod_Tango,{d'1900-01-01'},@hoy)=0
THEN 1 ELSE
dbo.fngetdeudaCliente(Cod_Tango,DATEADD(d,1,@hoy),{d'2200-01-01'})/dbo.fngetdeudaCliente(Cod_Tango,{d'1900-01-01'},@hoy)
END * 100 AS INT) AS porc,
dbo.fngetdeudaCliente(Cod_Tango,dateadd(d,-15,@hoy),@hoy)
AS vencida15,
dbo.fngetdeudaCliente(Cod_Tango,dateadd(d,-30,@hoy),dateadd(d,-16,@hoy))
AS vencida30,
dbo.fngetdeudaCliente(Cod_Tango,dateadd(d,-60,@hoy),dateadd(d,-31,@hoy))
AS vencida60,
dbo.fngetdeudaCliente(Cod_Tango,dateadd(d,-90,@hoy),dateadd(d,-61,@hoy))
AS vencida90,
dbo.fngetdeudaCliente(Cod_Tango,dateadd(d,-120,@hoy),dateadd(d,-91,@hoy))
AS vencida120,
dbo.fngetdeudaCliente(Cod_Tango,{d'1900-01-01'},dateadd(d,-121,@hoy))
AS vencida120oMas,
dbo.fnGetCantidadFacturasImpagas(Cod_Tango,{d'1900-01-01'},@hoy)
AS cantFactVencidas,
dbo.fnGetPromedioDias(Cod_Tango,{d'1900-01-01'},@hoy)
AS promedioDias,
dbo.fnGetFacturaVencidaMasAntigua(Cod_Tango)
AS FechaMasVieja,
DATEDIFF(d,@hoy,dbo.fnGetFacturaVencidaMasAntigua(Cod_Tango))
AS DiasMasVieja,
CAST(dbo.fngetdeudaCliente(Cod_Tango,{d'1900-01-01'},@hoy)*(CAST(dbo.fnGetPromedioDias(Cod_Tango,{d'1900-01-01'},@hoy)
AS FLOAT)/365)*@tna/100 AS DECIMAL(18,2)) AS CostoFinanciero,
CAST(dbo.fnGetChequesEnCartera(Cod_Tango,@hoy,{d'2200-01-01'})+saldo_cc AS
DECIMAL(18,2)) AS RiesgoTotal,
dbo.fnGetChequesEnCartera(Cod_Tango,@hoy,{d'2200-01-01'}) AS
ChequesEnCartera, gva14.COND_VTA, DESC_COND, CAST(gva14.CUPO_CREDI AS INT) AS
LimiteCredito, CAST(gva14.CUPO_CREDI
-(dbo.fnGetChequesEnCartera(Cod_Tango,@hoy,{d'2200-01-01'})+saldo_cc) AS
DECIMAL(18,2) ) AS ExcesoCredito FROM dbo.CRM_CLIENTES INNER JOIN gva14 ON
crm_clientes.Cod_Tango=gva14.COD_CLIENT LEFT JOIN dbo.V_CondicVenta_GVA01 ON
gva14.COND_VTA=dbo.V_CondicVenta_GVA01.COND_VTA
-------------
Ecosan S.A.
|