SQL Server Join two tables to generate a graph, but they don't have common columns

bkkx9g8r 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(156)

I'm trying to build a SQL consult that populates a data table that will give the info that will draw a js graph (on canvasjs library).

The graph will compare the sales table vs. the expenses table, so they don't have a column on which I can make a JOIN, only the dates, that will be shown as yyyy-mm.

In order to group all the sales, I need to Join two tables, the one that stores the main invoices info ([dbo].[Facturas]), and the one that stores the items on each invoice ([dbo].[FacturasItems])

This is the consult that retrieves the info for all the sales, and groups them in months: DECLARE @facTotal numeric(18,2)

SET @facTotal = 
    (
        SELECT
            SUM(([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) + (([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) * [dbo].[Facturas].[ValorIVA]))
        FROM [dbo].[FacturasItems]

        INNER JOIN
            [dbo].[Facturas] 
            ON [dbo].[Facturas].[ID] = [dbo].[FacturasItems].[IdFactura]   
    )

SELECT
    CAST(YEAR([dbo].[Facturas].[FechaCr]) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH([dbo].[Facturas].[FechaCr]) AS VARCHAR(2)), 2) AS [Periodo],
    CONVERT(numeric(18,0), SUM(([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) + (([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) * [dbo].[Facturas].[ValorIVA]))) AS [VentasRaw],
    '$' + CONVERT(varchar, CONVERT(money, SUM(([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) + (([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) * [dbo].[Facturas].[ValorIVA]))), 1) AS [VentasForm],
    CONVERT(varchar(6), CONVERT(numeric(18,2), SUM(([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) + (([dbo].[FacturasItems].[ValorU] * [dbo].[FacturasItems].[Cantidad]) * [dbo].[Facturas].[ValorIVA])) / (@facTotal) * 100)) + '%' AS [Prc100]
FROM [dbo].[Facturas] 

INNER JOIN
    [dbo].[FacturasItems]
    ON [dbo].[FacturasItems].[IdFactura] = [dbo].[Facturas].[ID]   

GROUP BY
    CAST(YEAR([dbo].[Facturas].[FechaCr]) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH([dbo].[Facturas].[FechaCr]) AS VARCHAR(2)), 2)

This consult is working just fine, and it returns this table data:

The second consult, expenses, goes like this:

DECLARE @gasTotal numeric(18,2)

SET @gasTotal = 
    (
        SELECT
            SUM(([dbo].[Gastos].[Valor]) + (([dbo].[Gastos].[Valor]) * [dbo].[Gastos].[IVAVal]))
        FROM [dbo].[Gastos]
    )

SELECT
    CAST(YEAR([dbo].[Gastos].[Fecha]) AS varchar(4)) + '-' + RIGHT('00' + CAST(MONTH([dbo].[Gastos].[Fecha]) AS varchar(2)), 2) AS [Periodo],
    CONVERT(numeric(18,0), SUM([dbo].[Gastos].[Valor] + ([dbo].[Gastos].[Valor] * [dbo].[Gastos].[IVAVal]))) AS [ValorGraph],
    '$' + CONVERT(varchar, CONVERT(money, SUM([dbo].[Gastos].[Valor] + ([dbo].[Gastos].[Valor] * [dbo].[Gastos].[IVAVal]))), 1) AS [ValorForm],
    CONVERT(varchar, CONVERT(money, SUM(([dbo].[Gastos].[Valor] + ([dbo].[Gastos].[Valor] * [dbo].[Gastos].[IVAVal])) / @gasTotal) * 100), 1) + '%' AS [Prc100]
FROM [dbo].[Gastos] 

GROUP BY
    CAST(YEAR([dbo].[Gastos].[Fecha]) AS varchar(4)) + '-' + RIGHT('00' + CAST(MONTH([dbo].[Gastos].[Fecha]) AS varchar(2)), 2)

This will create this table:

So, as you can see, there where no sales on 2017-10, zero invoices, so the table 1 won't start on 2017-10 but on 2017-11, while the expenses table starts on 2017-10.

I need that the table 1 (invoices and sales), starts in 2017-10 with it's values in 0, so that the graph will show correctly.

In other words, I need this kind of tables:

I tried an inner join, a left join and an outer join, but the will add all the records, add the sales + expenses.

Thanks!

sql-server

来源：https://stackoverflow.com/questions/49039350/join-two-tables-to-generate-a-graph-but-they-dont-have-common-columns

2条答案

按热度按时间

8zzbczxx1#

Just add SELECT 2017-10 as Periodo,0 as VentasRow, 0 as VentasForm, 0 as Prc100 UNION Your first query here....

Hope this helps.

赞(0）回复(0）举报 2023-03-07

8mmmxcuj2#

WITH Ventas as (
    SELECT
        CAST(YEAR(f.FechaCr) AS VARCHAR(4)) + '-'
          + right('00' + CAST(MONTH(f.FechaCr) AS VARCHAR(2)), 2) AS Periodo,
        SUM(fi.ValorU * fi.Cantidad * (1 + f.ValorIVA)) AS VentasRaw,
        SUM(fi.ValorU * fi.Cantidad * (1 + f.ValorIVA)) OVER () AS VentasTot
    FROM dbo.Facturas as f INNER JOIN dbo.FacturasItems as fi
        ON fi.IdFactura = f.ID
    GROUP BY datepart(year, f.FechaCr), datepart(month, f.FechaCr)
),
Gastos as ( 
    SELECT
        CAST(YEAR(g.Fecha) AS varchar(4)) + '-'
          + RIGHT('00' + CAST(MONTH(g.Fecha) AS varchar(2)), 2) AS Periodo,
        SUM(g.Valor * (1 * g.IVAVal) AS ValorGraph,
        SUM(g.Valor * (1 * g.IVAVal) OVER () AS GastosTot
    FROM dbo.Gastos as g
    GROUP BY datepart(year, g.Fecha), datepart(month, g.Fecha)
)
SELECT g.Periodo,
    COALESCE(v.VentasRaw, 0), COALESCE(v.VentasTot, 0),
    g.ValorGraph, g.GastosTot
FROM Ventas as v RIGHT OUTER JOIN Gastos g ON g.Periodo = v.Periodo;

You could certainly use a full outer join if it's possible to have sales but no expenses.

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Join two tables to generate a graph, but they don't have common columns

2条答案

相关问题

热门标签

最新问答