IF OBJECT_ID('dbo.years_months', 'U') IS NOT NULL
DROP TABLE dbo.years_months;
IF OBJECT_ID('dbo.pagos', 'U') IS NOT NULL
DROP TABLE dbo.pagos;
IF OBJECT_ID('dbo.cuentas', 'U') IS NOT NULL
DROP TABLE dbo.cuentas;
IF OBJECT_ID('dbo.clientes', 'U') IS NOT NULL
DROP TABLE dbo.clientes;
IF OBJECT_ID('dbo.meses', 'U') IS NOT NULL
DROP TABLE dbo.meses;
IF OBJECT_ID('dbo.years', 'U') IS NOT NULL
DROP TABLE dbo.years;
CREATE TABLE clientes(
id INT IDENTITY(1,1),
nombre Nvarchar(20)
)
INSERT INTO clientes VALUES ('Libras')
INSERT INTO clientes VALUES ('Libras1')
INSERT INTO clientes VALUES ('Libras2')
CREATE TABLE cuentas(
id INT IDENTITY(1,1),
id_cliente INT,
years_months INT,
monto INT
)
INSERT INTO cuentas VALUES (1,1,50)
INSERT INTO cuentas VALUES (1,2,100)
INSERT INTO cuentas VALUES (1,3,50)
INSERT INTO cuentas VALUES (1,4,50)
INSERT INTO cuentas VALUES (1,5,100)
INSERT INTO cuentas VALUES (1,6,50)
INSERT INTO cuentas VALUES (1,7,50)
INSERT INTO cuentas VALUES (1,8,100)
INSERT INTO cuentas VALUES (1,9,50)
INSERT INTO cuentas VALUES (1,10,50)
INSERT INTO cuentas VALUES (1,11,100)
INSERT INTO cuentas VALUES (1,12,50)
INSERT INTO cuentas VALUES (2,1,50)
INSERT INTO cuentas VALUES (2,2,100)
INSERT INTO cuentas VALUES (2,3,50)
INSERT INTO cuentas VALUES (2,4,50)
INSERT INTO cuentas VALUES (2,5,100)
INSERT INTO cuentas VALUES (2,6,50)
INSERT INTO cuentas VALUES (2,7,50)
INSERT INTO cuentas VALUES (2,8,100)
INSERT INTO cuentas VALUES (2,9,50)
INSERT INTO cuentas VALUES (2,10,50)
INSERT INTO cuentas VALUES (2,11,100)
INSERT INTO cuentas VALUES (2,12,50)
INSERT INTO cuentas VALUES (3,1,50)
INSERT INTO cuentas VALUES (3,2,100)
INSERT INTO cuentas VALUES (3,3,50)
INSERT INTO cuentas VALUES (3,4,50)
INSERT INTO cuentas VALUES (3,5,100)
INSERT INTO cuentas VALUES (3,6,50)
INSERT INTO cuentas VALUES (3,7,50)
INSERT INTO cuentas VALUES (3,8,100)
INSERT INTO cuentas VALUES (3,9,50)
INSERT INTO cuentas VALUES (3,10,50)
INSERT INTO cuentas VALUES (3,11,100)
INSERT INTO cuentas VALUES (3,12,50)
CREATE TABLE meses(
id INT IDENTITY(1,1),
mes Nvarchar(20)
)
INSERT INTO meses VALUES ('Enero')
INSERT INTO meses VALUES ('Febrero')
INSERT INTO meses VALUES ('Marzo')
INSERT INTO meses VALUES ('Abril')
INSERT INTO meses VALUES ('Mayo')
INSERT INTO meses VALUES ('junio')
INSERT INTO meses VALUES ('julio')
INSERT INTO meses VALUES ('Agosto')
INSERT INTO meses VALUES ('Septiembre')
INSERT INTO meses VALUES ('Octubre')
INSERT INTO meses VALUES ('noviembre')
INSERT INTO meses VALUES ('diciembre')
CREATE TABLE years(
id INT IDENTITY(1,1),
years INT
)
INSERT INTO years VALUES (2016)
INSERT INTO years VALUES (2017)
CREATE TABLE years_months(
id INT IDENTITY(1,1),
id_year INT,
id_mont INT
)
INSERT INTO years_months VALUES (1,1)
INSERT INTO years_months VALUES (1,2)
INSERT INTO years_months VALUES (1,3)
INSERT INTO years_months VALUES (1,4)
INSERT INTO years_months VALUES (1,5)
INSERT INTO years_months VALUES (1,6)
INSERT INTO years_months VALUES (1,7)
INSERT INTO years_months VALUES (1,8)
INSERT INTO years_months VALUES (1,9)
INSERT INTO years_months VALUES (1,10)
INSERT INTO years_months VALUES (1,11)
INSERT INTO years_months VALUES (1,12)
CREATE TABLE pagos(
id INT IDENTITY(1,1),
id_cliente INT,
id_year_month INT,
cantidad INT
)
INSERT INTO pagos VALUES (1,1,50)
INSERT INTO pagos VALUES (2,1,100)
INSERT INTO pagos VALUES (1,2,100)
------------------------------------
SELECT * FROM(
SELECT nombre,mes, total_mes, pagado_mes, total_mes - pagado_mes AS faltante FROM(
SELECT t3.nombre,t5.mes, t1.monto total_mes ,isnull(t2.cantidad,0) pagado_mes FROM cuentas AS t1
LEFT JOIN pagos AS t2 ON (t1.years_months=t2.id_year_month AND t2.id_cliente=t1.id_cliente)
LEFT JOIN clientes AS t3 ON (t1.id_cliente=t3.id)
LEFT JOIN years_months AS t4 ON (t1.years_months=t4.id)
LEFT JOIN meses AS t5 ON (t4.id_mont=t5.id)
) AS parcial
) AS completa WHERE faltante>=total_mes
----------------------
SELECT * FROM(
SELECT nombre,mes, total_mes, pagado_mes, total_mes - pagado_mes AS faltante, years FROM(
SELECT t3.nombre,t5.mes, t1.monto total_mes ,isnull(t2.cantidad,0) pagado_mes, t6.years FROM cuentas AS t1
LEFT JOIN pagos AS t2 ON (t1.years_months=t2.id_year_month AND t2.id_cliente=t1.id_cliente)
LEFT JOIN clientes AS t3 ON (t1.id_cliente=t3.id)
LEFT JOIN years_months AS t4 ON (t1.years_months=t4.id)
LEFT JOIN meses AS t5 ON (t4.id_mont=t5.id)
LEFT JOIN years AS t6 ON (t4.id_year=t6.id)
) AS parcial
) AS completa WHERE faltante<=0 AND mes='Enero' AND years=2016
------------------------------
SELECT * FROM(
SELECT nombre,mes, total_mes, pagado_mes, total_mes - pagado_mes AS faltante, years FROM(
SELECT t3.nombre,t5.mes, t1.monto total_mes ,isnull(t2.cantidad,0) pagado_mes, t6.years FROM cuentas AS t1
LEFT JOIN pagos AS t2 ON (t1.years_months=t2.id_year_month AND t2.id_cliente=t1.id_cliente)
LEFT JOIN clientes AS t3 ON (t1.id_cliente=t3.id)
LEFT JOIN years_months AS t4 ON (t1.years_months=t4.id)
LEFT JOIN meses AS t5 ON (t4.id_mont=t5.id)
LEFT JOIN years AS t6 ON (t4.id_year=t6.id)
) AS parcial
) AS completa WHERE faltante>=total_mes AND mes='Enero' AND years=2016