Aşağıdaki sql sorgusundaki 2. SELECT sorgusunun sonucunu, 1. SELECT sorgusunun sonucunun…
- User
Aşağıdaki sql sorgusundaki 2. SELECT sorgusunun sonucunu, 1. SELECT sorgusunun sonucunun ardına ekleyerek 1 sheet içerisinde multi excell de kullanabilmek için düzenle. SELECT @Date2 Date, ra.BankAccountsId, ra.SWIFTCode, ra.AccountNumber, ra.AccountSuffix, LEFT(c.CustomerName,100) CustomerName, f.FecCode, ISNULL(ra.SWIFTBalance,0) SWIFTBalance, ISNULL(ra.AccountingBalance,0) AccountingBalance, ISNULL(ra.SWIFTBalance,0)-ISNULL(ra.AccountingBalance,0) Difference, ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.SwiftDebit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) + ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.AccountCredit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) ‘Total’,
(ISNULL(ra.SWIFTBalance,0)-ISNULL(ra.AccountingBalance,0) + ( ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.SwiftDebit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) + ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.AccountCredit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) ) ) ‘Reconciliation Status’ FROM BOA.RCC.BankAccounts ra WITH (NOLOCK) JOIN BOA.CUS.Customer c WITH (NOLOCK) ON c.Customerid=ra.AccountNumber JOIN BOA.COR.FEC f WITH (NOLOCK) ON f.FecId=ra.FEC WHERE f.FecGroup IN (0,1) AND ra.IsActive = 1 --döviz, kıymetli maden, kıymetsiz maden çıkarıldı AND ra.BankAccountsId = (SELECT BankAccountsId FROM #BankAccounts WHERE Id = @Incrementer)
SELECT (CASE WHEN sd.EntryDate IS NULL THEN (Format(sd.ValueDate, N’MMddyy’)) ELSE sd.EntryDate + Format(sd.ValueDate, N’yy’) END) EntryDate, sd.ValueDate, ra.BankAccountsId, ra.SWIFTCode, ra.AccountNumber, ra.AccountSuffix, LEFT(c.CustomerName,100) CustomerName, f.FecCode, (CASE WHEN DebitCreditCode IN (‘D’,‘RC’) THEN sd.Amount ELSE 0 END) StatementDebit, (CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN sd.Amount ELSE 0 END) StatementCredit, 0.00 AccountingDebit, 0.00 AccountingCredit, sd.AccountsReference + ’ ’ + sd.InstitutionsReference + ’ ’ + sd.SupplementaryDetails SharedReferanceDetails, sd.InformationToAccountOwner, sd.IdentificationCode, NULL AccSystemDate, NULL AccUserName, NULL ResourceName, NULL BusinessKey FROM BOA.RCC.BankAccounts ra WITH (NOLOCK) JOIN BOA.CUS.Customer c WITH (NOLOCK) ON c.Customerid=ra.AccountNumber JOIN BOA.COR.FEC f WITH (NOLOCK) ON f.FecId=ra.FEC JOIN BOA.RCC.SwiftDebit sd WITH (NOLOCK) ON sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’ WHERE ra.BankAccountsId = (SELECT BankAccountsId FROM #BankAccounts WHERE Id = @Incrementer) AND f.FecGroup IN (0,1) AND ra.IsActive = 1
- Assistant
--1. sorgu SELECT @Date2 Date, ra.BankAccountsId, ra.SWIFTCode, ra.AccountNumber, ra.AccountSuffix, LEFT(c.CustomerName,100) CustomerName, f.FecCode, ISNULL(ra.SWIFTBalance,0) SWIFTBalance, ISNULL(ra.AccountingBalance,0) AccountingBalance, ISNULL(ra.SWIFTBalance,0)-ISNULL(ra.AccountingBalance,0) Difference, ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.SwiftDebit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) + ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.AccountCredit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) ‘Total’,
(ISNULL(ra.SWIFTBalance,0)-ISNULL(ra.AccountingBalance,0) + ( ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.SwiftDebit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) + ISNULL((SELECT SUM(Amount*(CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN -1 ELSE 1 END)) FROM BOA.RCC.AccountCredit sd WITH (NOLOCK) WHERE sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’),0) ) ) ‘Reconciliation Status’ FROM BOA.RCC.BankAccounts ra WITH (NOLOCK) JOIN BOA.CUS.Customer c WITH (NOLOCK) ON c.Customerid=ra.AccountNumber JOIN BOA.COR.FEC f WITH (NOLOCK) ON f.FecId=ra.FEC WHERE f.FecGroup IN (0,1) AND ra.IsActive = 1 --döviz, kıymetli maden, kıymetsiz maden çıkarıldı AND ra.BankAccountsId = (SELECT BankAccountsId FROM #BankAccounts WHERE Id = @Incrementer)
UNION ALL
--2. sorgu SELECT (CASE WHEN sd.EntryDate IS NULL THEN (Format(sd.ValueDate, N’MMddyy’)) ELSE sd.EntryDate + Format(sd.ValueDate, N’yy’) END) EntryDate, sd.ValueDate, ra.BankAccountsId, ra.SWIFTCode, ra.AccountNumber, ra.AccountSuffix, LEFT(c.CustomerName,100) CustomerName, f.FecCode, (CASE WHEN DebitCreditCode IN (‘D’,‘RC’) THEN sd.Amount ELSE 0 END) StatementDebit, (CASE WHEN DebitCreditCode IN (‘C’,‘RD’) THEN sd.Amount ELSE 0 END) StatementCredit, 0.00 AccountingDebit, 0.00 AccountingCredit, sd.AccountsReference + ’ ’ + sd.InstitutionsReference + ’ ’ + sd.SupplementaryDetails SharedReferanceDetails, sd.InformationToAccountOwner, sd.IdentificationCode, NULL AccSystemDate, NULL AccUserName, NULL ResourceName, NULL BusinessKey FROM BOA.RCC.BankAccounts ra WITH (NOLOCK) JOIN BOA.CUS.Customer c WITH (NOLOCK) ON c.Customerid=ra.AccountNumber JOIN BOA.COR.FEC f WITH (NOLOCK) ON f.FecId=ra.FEC JOIN BOA.RCC.SwiftDebit sd WITH (NOLOCK) ON sd.BankAccountsId=ra.BankAccountsId AND sd.Status=‘U’ WHERE ra.BankAccountsId = (SELECT BankAccountsId FROM #BankAccounts WHERE Id = @Incrementer) AND f.FecGroup IN (0,1) AND ra.IsActive = 1