CODEWITHSUNDEEP
sqlsql-server
AnubisVolga
AnubisVolga

Reputation: 311

SQL query to show customers those accounts have 10 deposits or more in his account

I have a huge doubt where I have to make a query to show customers made more than 10 deposits greater than or equal to 50,000 in the period, but does not leave me and I'm sure my script is very wrong:

The consult i need is: Make a query to get the accounts with the client name. Those accounts that have had more than 10 deposit with greater than or equal to $ 50,000.00 from January 2016 to date amount.

Here my script:

DECLARE @NombreCliente VARCHAR(100)
DECLARE @ApellidoCliente VARCHAR(100)
DECLARE @CantidadCuenta INT
DECLARE @SumaMonto DECIMAL
DECLARE @FechaInicio VARCHAR(10)


SET @NombreCliente = 'Name'
SET @ApellidoCliente = 'LastName'

SET @CantidadCuenta =
(
    SELECT COUNT(b.ClienteId) FROM [dbo].[Clientes] a
    INNER JOIN [dbo].[CuentasBancarias] b
    ON a.ClienteId = b.ClienteId
    WHERE a.Nombre = @NombreCliente and a.Apellidos = @ApellidoCliente 
)
SELECT @CantidadCuenta

IF @CantidadCuenta >= 10
BEGIN
    SET @SumaMonto = 
    (
        SELECT SUM(a.Monto) FROM [dbo].[Depositos] a
        INNER JOIN [dbo].[CuentasBancarias] b
        ON a.CuentaId = b.CuentaId
        INNER JOIN [dbo].[Clientes] c
        ON b.ClienteId = c.ClienteId
        WHERE c.Nombre = @NombreCliente and c.Apellidos = @ApellidoCliente
        and a.FechaMovimiento >= '2016-02-01' and a.FechaMovimiento <=      GETDATE()
    )
    SELECT @SumaMonto
    SELECT @CantidadCuenta
    IF @SumaMonto >= 50000
    BEGIN
        SELECT * FROM [dbo].[Clientes] a
        INNER JOIN [dbo].[CuentasBancarias] b
        ON a.ClienteId = b.ClienteId
        WHERE a.Nombre = @NombreCliente and a.Apellidos = @ApellidoCliente
    END
    IF @SumaMonto < 50000
    BEGIN
        RAISERROR('No hay clientes con un monto superior a $50,000.00',16,1)
    END
END
IF @CantidadCuenta < 10
BEGIN
    RAISERROR('No hay clientes con más de 10 cuentas',16,1)
END

There is a way to solve this case ?? Here the tables in order:

1) [dbo].[Clientes]
2) [dbo].[CuentasBancarias]
3) [dbo].[Depositos]

bank account

tablesaccount

Upvotes: 1

Views: 890

Answers (1)

IVNSTN
IVNSTN

Reputation: 9318

this?

    SELECT c.ClienteID, SUM(a.Monto) FROM [dbo].[Depositos] a
    INNER JOIN [dbo].[CuentasBancarias] b
    ON a.CuentaId = b.CuentaId
    INNER JOIN [dbo].[Clientes] c
    ON b.ClienteId = c.ClienteId
    WHERE a.FechaMovimiento >= '2016-02-01' and a.FechaMovimiento <=      GETDATE()
      and a.Monto >= 50000
    GROUP BY c.ClienteID
    HAVING COUNT(a.ID) > 10

Upvotes: 1

Related Questions