sql report atsdili

 SELECT 0          Batch,
       a.rm_1     RemittanceNo,
       ''         DebtorName,
       ''         DebtorAccount,
       ''         DebtorBIC,
       ''         CreditorName,
       ''         CreditorAccount,
       ''         RMTTR2,
       ''         CreditorBIC,
       a.rmchka_1 Amount,
       NULL       RemittanceInformation,
       a.status_code,
       NULL       UploadTime,
       NULL       UploadBy
FROM   ats_payment a
WHERE  a.ats_format = 3
       AND a.rm_1 LIKE '%'
       AND a.status_code LIKE '%'
UNION
SELECT 0          Batch,
       a.rm_1     RemittanceNo,
       ''         DebtorName,
       ''         DebtorAccount,
       ''         DebtorBIC,
       ''         CreditorName,
       ''         CreditorAccount,
       ''         RMTTR2,
       ''         CreditorBIC,
       a.rmchka_1 Amount,
       NULL       RemittanceInformation,
       a.status_code,
       NULL       UploadTime,
       NULL       UploadBy
FROM   ats_payment_hist a
WHERE  a.ats_format = 3
       AND a.rm_1 LIKE '%'
       AND a.status_code LIKE '%'
UNION
SELECT Isnull(b.batch, 0) batch,
       b.remittanceno,
       b.debtorname,
       b.debtoraccount,
       b.debtorbic,
       b.creditorname,
       b.creditoraccount,
       b.rmttr2,
       b.creditorbic,
       b.amount,
       b.remittanceinformation,
       b.status_code,
       b.uploadtime,
       b.uploadby
FROM   ats_payment a
       INNER JOIN ats_bulk_detail b
               ON a.rm_1 = b.msg_id
WHERE  ats_format = 3
       AND a.rm_1 LIKE '%'
       AND a.status_code LIKE '%'
UNION
SELECT Isnull(b.batch, 0) batch,
       b.remittanceno,
       b.debtorname,
       b.debtoraccount,
       b.debtorbic,
       b.creditorname,
       b.creditoraccount,
       b.rmttr2,
       b.creditorbic,
       b.amount,
       b.remittanceinformation,
       b.status_code,
       b.uploadtime,
       b.uploadby
FROM   ats_payment_hist a
       INNER JOIN ats_bulk_detail_hist b
               ON a.rm_1 = b.remittanceno
WHERE  ats_format = 3
       AND a.rm_1 LIKE '%'
       AND a.status_code LIKE '%'
ORDER  BY remittanceno,
          batch