[ad_1]
Terima kasih kepada Tuan Richard Demming (
@RichardDeeming
) untuk memberi saya solusi untuk jumlah dua tabel.
Namun, saya memiliki tabel dengan kolom minus, di sini saya harus mencoba menyelesaikannya.
Tabel Satu “Agen”
Nama Uang
Ahmed 600
Tabel Dua “Ekspor”
Tidak Ada Tanggal Nama Uang
1 25-1-2023 Ahemd 700
2 30-6-2023 Khalid 800
3 10-7-2023 Ahmed 200
4 30-11-2023 Ahmed 100
Tabel Tiga “Pembayaran”
Tidak Ada Tanggal Nama Uang
1 25-2-2023 Ahemd 300
2 30-7-2023 Khalid 800
3 15-8-2023 Ahmed 300
4 25-12-2023 Ahmed 400
Saya ingin Menampilkan Tabel seperti ini:
|Tidak | Tanggal | Nama | Jumlah | Ekspor | Pembayaran | Sisa Uang
|—|————–|———|——-|——–|— ——|————–
| 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
Apa yang saya coba:
Tuan Rechard Demming Solusi untuk jumlah dua tabel
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 ;
Solusi 1
Terima kasih teman-teman, khususnya
Mr. Rechard Demming
Namun, saya mendapat solusi lain.
Saya memecahkan masalah saya di sini adalah Kode setelah menambahkan beberapa tabel dan kolom lain:
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 Tanggal Nama Lama Ekspor Pembayaran Sisa Uang Catatan
Catatan Sisa Pembayaran Keluar Nama Lama Tanggal T
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 دفع
Adakah yang bisa membantu saya mendapatkan solusi seperti ini:
No Date Name Money 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 دفع
[ad_2]
コメント