I'm creating an application that will manage a client's budget over the year. The client will create incomes, costs, and expenses for a particular month.
I'm trying to build a query that will bring all items according to each client's account.
This are the main table's relationships:
Let's say the client with ID 1 (at dbo.Clientes_Info_W1) is consulting his budget.
Some table data examples for each table:
dbo.Ppto_Master:
ID IdCliente FechaPpto
-----------------------------------------------
4 1 2019-01-01
5 1 2019-02-01
7 1 2019-03-01
dbo.Ppto_IngresosRubros
ID IdPpto Rubro Estado
---------------------------------------------------------
3 4 Ventas desarrollo tecnológico 1
4 4 Ventas diseño estratégico 1
5 5 Ventas desarrollo tecnológico 1
6 5 Ventas diseño estratégico 1
9 7 Ventas desarrollo tecnológico 1
10 7 Ventas diseño estratégico 1
11 7 Consultorías y asesorías 1
dbo.IngresosSubRubros
ID IdIngresosRubro Subrubro Valor Estado
-----------------------------------------------------------------
1 3 Renting tecnológico 8358312.00 1
2 3 Diseño web WP 6318950.00 1
3 3 Diseño web ecommerce 8996500.00 1
4 3 Alquiler de licencias 13750000.00 1
5 4 Brand strategy 2850000.00 1
6 4 Comuniaciones y redes 1350000.00 1
7 4 Mercadeo y ventas 3000000.00 1
8 3 Desarrollo modelo 2000000.00 1
9 5 Renting tecnológico 10358312.00 1
10 5 Diseño web WP 3212320.00 1
11 5 Diseño web ecommerce 8996500.00 1
12 5 Alquiler de licencias 13750000.00 1
13 6 Comuniaciones y redes 135000000.00 1
14 7 Ropa 500000.00 1
15 8 Zapatos 1000000.00 1
16 8 Relojes 300000.00 1
17 9 Desarrollo modelo 18000000.00 1
18 11 Diseño de marca 3000000.00 1
The query I need to build should return this:
Rubro ene 2019 feb 2019 mar 2019
---------------------------------------------------------------------------
Consultorías y asesorías $0 $0 $3,000,000
Ventas desarrollo tecnológico $37,423,762 $38,317,132 $18,000,000
Ventas diseño estratégico $7,200,000 $135,000,000 $0
Total $44,623,762 $173,317,132 $21,000,000
So the "Rubro" field are all the distinct values of dbo.IngresosRubros.Rubro that the client has stored in the data table in the year we are looking at.
The months columns come from dbo.Ppto_Master.FechaPpto, and the values come from dbo.Ppto_IngresosSubRubros, summing the value according to the month and the "Rubro". If in one specific month the client didn't store any budget on a specific "Rubro" it'll return 0.
I've tried different approaches: creating child tables and relating them in the code behind (ASP.NET C# or VB), or creating iterations through logic code.
So far this is my closest approach:
SELECT DISTINCT([I].[Rubro]) AS [Rubro]
FROM
[dbo].[Ppto_IngresosRubros] [I]
INNER JOIN
[dbo].[Ppto_Master] [M] ON [I].[IdPpto] = [M].[ID]
WHERE
([M].[IdCliente] = @idcliente)
SELECT DISTINCT
([M].[ID]) AS [PptoId],
FORMAT(DATEADD(MONTH, 0, [M].[FechaPpto]), 'MMMM', 'es-CO') + ' ' + CONVERT(VARCHAR(10), YEAR([M].[FechaPpto]), 100) AS [Fecha]
FROM
[dbo].[Ppto_Master] [M]
WHERE
([M].[IdCliente] = @idcliente)
SELECT
CASE
WHEN (SUM([S].[Valor])) > 0
THEN SUM([S].[Valor])
ELSE '0'
END AS [Valor]
FROM [dbo].[Ppto_IngresosSubRubros] [S]
INNER JOIN
[dbo].[Ppto_IngresosRubros] [I]
ON
[S].[IdIngresosRubro] = [I].[ID]
INNER JOIN
[dbo].[Ppto_Master] [M]
ON
[I].[IdPpto] = [M].[ID]
WHERE
([I].[Rubro] = 'Consultorías y asesorías')
AND
([M].[ID] = 7)
Each select clause will populate a table, and I'm trying to relate them through iterations:
Dim conn_rp1 As SqlConnection = New SqlConnection(enchufe)
Dim cmd_rp1 As SqlCommand = New SqlCommand("SEL_Ppto_IngresosRubros_Distinct_ByIdCliente", conn_rp1)
cmd_rp1.CommandType = CommandType.StoredProcedure
Dim prm_a1_rp1 As New SqlParameter("@idcliente", SqlDbType.Int)
prm_a1_rp1.Direction = ParameterDirection.Input
prm_a1_rp1.Value = str_idempresa
cmd_rp1.Parameters.Add(prm_a1_rp1)
Dim da_rp1 As SqlDataAdapter = New SqlDataAdapter()
da_rp1.SelectCommand = cmd_rp1
Dim dt_rp1 As DataTable = New DataTable()
da_rp1.Fill(dt_rp1)
If dt_rp1.Rows.Count > 0 Then
For Each dr_rp1 As DataRow In dt_rp1.Rows
l_rubro_ingresos.Text += dr_rp1("Rubro") & "<br/><br/>"
Next
End If
But I'm not being able to relate the date field.
