¿Mostrar datos en el servidor SQL con tres tablas?

[ad_1]

Gracias al Sr. Richard Demming (

@RichardDeeming

) para darme una solución para la suma de dos tablas.

Sin embargo, tengo una tabla con eso a menos una columna, aquí tengo que intentar resolverlo.

Tabla uno “Agente”
Nombre Dinero
Ahmed 600

Tabla dos “Exportación”
Sin fecha Nombre Dinero
1 25-1-2023 Ejemd 700
2 30-6-2023 Jalid 800
3 10-7-2023 Ahmed 200
4 30-11-2023 Ahmed 100

Tabla tres “Pago”
Sin fecha Nombre Dinero
1 25-2-2023 Ejemd 300
2 30-7-2023 Jalid 800
3 15-8-2023 Ahmed 300
4 25-12-2023 Ahmed 400

Quiero mostrar una tabla como esta:
|No | Fecha | Nombre | Totales | Exportar | Pago | Resto de dinero
|—|————–|———|——-|——–|— ——|————–
| 0 | — | Ahmed | 600 | 0 | 0 | 600
| 1 | 25-1-2023 | Ahmed | 600 | 700 | 0 | 1300
| 1 | 25-2-2023 | Ahmed | 1300 | 0 | 300 | 1000
| 3 | 7-10-2023 | Ahmed | 1000 | 200 | 0 | 1200
| 3 | 15-8-2023 | Ahmed | 1200 | 0 | 300 | 900
| 4 | 30-11-2023 | Ahmed | 900 | 100 | 0 | 1000
| 4 | 25-12-2023 | Ahmed | 1000 | 0 | 400 | 600

Lo que he probado:

Sr. Rechard Demming Solución para la suma de dos tablas

WITH cteSource As
(
    SELECT
        0 As No,
        Name,
        Money
    FROM
        TableOne
    
    UNION ALL
    
    SELECT
        No,
        Name,
        Money
    FROM
        TableTwo As T2
    WHERE
        Exists
        (
            SELECT 1
            FROM TableOne As T1
            WHERE T1.Name = T2.Name
        )
)
SELECT
    No,
    Name,
    Money,
    SUM(Money) OVER (PARTITION BY Name ORDER BY No ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As TotalMoney
FROM
    cteSource
;

Solución 1

Gracias chicos, especialmente

Mr. Rechard Demming

Resolví mi problema aquí está el Código después de agregar otra tabla y columnas:

WITH NEWTABLE AS
(
    SELECT
        0 As No,
		CAST(D AS DATE) as DATE,
        N,
        FORMAT(OD, 'G29') AS MONEY,
		FORMAT(0, 'N2') AS Export,
		FORMAT(0, 'N2') AS Payment,
		'' AS NT
    FROM
        TBL_A
    
    UNION ALL
    
    SELECT
        INo As No,
		CAST(D AS DATE) as DATE,
        N,
		0 AS MONEY,
        T AS Export,
		0 AS Payment,
		'صادر' AS NT
    FROM
        TBL_E_I As T2
	WHERE
        Exists
        (
            SELECT 1
            FROM TBL_A As T1
            WHERE T1.N = T2.N
        )

	UNION ALL

	SELECT
		No AS No,
		CAST(D AS DATE) as DATE,
		N,
		0 AS MONEY,
		0 AS Export,
		PD AS Payment,
		'دفع' AS NT
	FROM
		TBL_A_P AS T3

    WHERE
        Exists
        (
            SELECT 1
            FROM TBL_A As T1
            WHERE T1.N = T3.N
        )
)
SELECT
    No AS 'ت',
	DATE AS 'التاريخ',
    N AS 'اسم',
    Money AS 'القديم',
	Export AS 'الصادر',
	Payment AS 'المدفوع',
    SUM((Money + Export) - Payment) OVER (PARTITION BY N ORDER BY No ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As 'المتبقي',
	NT AS 'ملاحظات'
FROM
    NEWTABLE
WHERE N = N'AnyName' ORDER BY DATE ASC
;

[ad_2]