Display data in SQL server with three tables?

Question

1.00/5 (2 votes)

See more:

Thanks to Mr. Richard Demming (

@RichardDeeming

) to give me a solution for the sum two tables.

However, I have a table with that to minus a column, here I have to try it solve it.

Table One "Agent"
Name Money
Ahmed 600

Table Two "Export"
No Date Name Money
1 25-1-2023 Ahemd 700
2 30-6-2023 Khalid 800
3 10-7-2023 Ahmed 200
4 30-11-2023 Ahmed 100

Table Three "Payment"
No Date Name Money
1 25-2-2023 Ahemd 300
2 30-7-2023 Khalid 800
3 15-8-2023 Ahmed 300
4 25-12-2023 Ahmed 400

I want to Display Table like this:
|No | Date | Name | Total | Export | Payment | RemaindMoney
|---|--------------|---------|-------|--------|---------|--------------
| 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 | 10-7-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

What I have tried:

Mr. Rechard Demming Solution for sum two tables

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
;

Posted 1-Mar-24 23:09pm

Karam Ibrahim

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2024-03-04T03:38:00

This is indeed an interesting problem. I have spent some time over it, read some articles, and learned a few things.

Here is a working example for your scenario.

SQL

DECLARE @Agent TABLE 
(
	NAME  VARCHAR(100),
	MONEY	INT
);

DECLARE @EXPORT TABLE
(
	SNO INT,
	[DATE]  DATETIME,
	NAME	VARCHAR(100),
	MONEY	INT
);


DECLARE @PAYMENT TABLE
(
	SNO INT,
	[DATE]  DATETIME,
	NAME	VARCHAR(100),
	MONEY	INT
);

INSERT INTO @Agent
SELECT 'Ahmed', 600;


INSERT INTO @EXPORT
SELECT 1, '25-JAN-2023', 'Ahmed', 700
UNION ALL
SELECT 2, '30-JUN-2023', 'Khalid', 800
UNION ALL
SELECT 3, '10-JUL-2023', 'Ahmed', 200
UNION ALL
SELECT 4, '30-NOV-2023', 'Ahmed', 100
;

INSERT INTO @PAYMENT
SELECT 1, '25-FEB-2023', 'Ahmed', 300
UNION ALL
SELECT 2, '30-JUL-2023', 'Khalid', 800
UNION ALL
SELECT 3, '15-AUG-2023', 'Ahmed', 300
UNION ALL
SELECT 4, '25-DEC-2023', 'Ahmed', 400
;


SELECT SNO, DATE, NAME, ISNULL(LAG(REMAINEDMONEY, 1) OVER (ORDER BY DATE ASC), REMAINEDMONEY) AS TOTAL, EXPORT, PAYMENT, REMAINEDMONEY
FROM
(
	SELECT SNO, DATE, NAME, SUM(CASE WHEN PAYMENT > 0 THEN -1*REMAINEDMONEY ELSE REMAINEDMONEY END) OVER( ORDER BY Date ASC) AS TOTAL, EXPORT, PAYMENT, SUM(CASE WHEN PAYMENT > 0 THEN -1*REMAINEDMONEY ELSE REMAINEDMONEY END) OVER( ORDER BY Date ASC) AS REMAINEDMONEY
	FROM
	(

			SELECT SNO, DATE, NAME, MONEY AS EXPORT, 0 AS PAYMENT, MONEY AS REMAINEDMONEY
			FROM @EXPORT
			UNION ALL
			SELECT 1, NULL, NAME, 0, 0, MONEY
			FROM @AGENT
			UNION ALL
			SELECT SNO, DATE, NAME, 0 AS EXPORT, MONEY AS PAYMENT, MONEY AS REMAINEDMONEY
			FROM @PAYMENT
	) T
	WHERE NAME = 'Ahmed'
) P

You need to read what LAG function do in order to understand how the solution works, assuming that you understand already how PARTION BY and OVER works as its already present in your solution

Karam Ibrahim · Accepted Answer · 2024-03-02T00:07:00

Thanks guys, especially

Mr. Rechard Demming

, However, I got another solution.

I solved my problem here is the Code after added some another table and columns:

WITH NEWTABLE AS
(
    SELECT
        0 As No,
		D AS DATE,
        N,
        OD AS MONEY,
		0  AS Export,
		0  AS Payment,
		'' AS NT
    FROM
        TBL_A
    
    UNION ALL
    
    SELECT
        INo As No,
		D 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,
		D 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 'ت',
	CAST(DATE AS DATE) AS 'التاريخ',
    N AS 'اسم',
    FORMAT(Money, 'G29') AS 'القديم',
	FORMAT(Export, 'G29') AS 'الصادر',
	FORMAT(Payment, 'G29') AS 'المدفوع',
    SUM((MONEY + Export) - Payment) OVER (PARTITION BY N ORDER BY DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As 'المتبقي',
	NT AS 'ملاحظات'
FROM
    NEWTABLE
WHERE N = N'كريمينيا' ORDER BY DATE ASC
;

No Date Name Old Export Payment RemaindMoney Note
ملاحظات المتبقي المدفوع الصادر القديم الاسم التاريخ ت

No	Date	Name	Total	Export	Payment	RemaindMoney
ملاحظات     المتبقي      المدفوع    الصادر   القديم     الاسم    التاريخ    ت
0	25-1-2023	Ahmed	600	    0	    0	    600     قديم
1	25-1-2023	Ahmed	0	    700	    0	    1300    صادر
1	25-2-2023	Ahmed	0       0   	300 	1000    دفع
3	10-7-2023	Ahmed	0       200 	0   	1200    صادر
3	15-8-2023	Ahmed	0       0	    300 	900     دفع
4	30-11-2023	Ahmed	0       100 	0   	1000    صادر
4	25-12-2023	Ahmed	0       0   	400 	600     دفع

Could Anybody help me to get a solution like this:

No	Date	Name    Money   Old  Export	  Payment	RemaindMoney
0	25-1-2023	Ahmed	0	    600	    0	    0       600     قديم
1	25-1-2023	Ahmed	600	    0       700	    0	    1300    صادر
1	25-2-2023	Ahmed	1300    0       0   	300 	1000    دفع
3	10-7-2023	Ahmed	1000    0    	200   	0       1200    صادر
3	15-8-2023	Ahmed	1200    0	    0       300 	900     دفع
4	30-11-2023	Ahmed	900     0       100 	0   	1000    صادر
4	25-12-2023	Ahmed	1000    0   	0       400 	600     دفع

Display data in SQL server with three tables?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0