WITH CTE_MainQuery AS ( SELECT part.Code AS PARTNER_ID, orm.ReceiptDetailId, CONCAT(COALESCE(tit.NAME, ''), ' ', ISNULL(part.FirstName, '') + ISNULL(part.MiddleName, '') + ISNULL(part.LastName, '')) AS FULL_NAME, partconm.Contact AS PRIMARY_MOBILE_NO, partcone.Contact AS EMAIL_ID, patraddr.ApartmentFlatDoorNumber AS ADDRESS_LINE1, patraddr.StreetOrRoadName AS ADDRESS_LINE2, patraddr.LandMark AS ADDRESS_LINE3, patraddr.City AS CITY, PCM.PinCode AS PINCODE, patraddr.District AS DISTRICT, '' AS PROVINCE, st.Name AS STATE_NAME, cnt.NAME AS COUNTRY_NAME, partper.PanId AS pan_id, '' AS ROLES, RCPT.Code AS RECEIPT_DISP_NUM, RCPT.Code AS RECEIPT_ID, RCPT.REMARKS, RCPT.CreatedDateTime AS CREATED_TIME, stfdtls.NAME AS STAFF_NAME, '' AS AMBASSTOR_NAME, CAST(SWITCHOFFSET(orm.ReceivedDateTime, '+05:30') AS Date) AS 'RECEIPT_DATE', eve.Name AS EVENT_NAME, CONCAT(offtypes.Name, ' ', eft.Name) AS OFFERING_NAME, CONCAT(offtypes.Name, ' ', eft.Name) AS offering_types, orm.Amount AS OFFERING, orm.Amount AS TOTAL_AMOUNT, offmode.Name AS RECEIVED_MODE, CASE WHEN offmode.Name = 'Cash' THEN offmed1.Name ELSE NULL END AS CASH_MEDIUM, CASE WHEN offmode.Name <> 'Cash' THEN offmed2.Name ELSE NULL END AS BANK_MEDIUM, GTWAYDETLS.Name AS GATEWAY_NAME, BANKDTLS.Name AS BANK_NAME, BANKDTLS1.Name AS BANK_NAME_RTGS, BANKDTLS2.Name AS BANK_NAME_GTWAY, DBKDETLS2.Name AS DRAWEE_BANK_GTWAY, DBKDETLS1.Name AS DRAWEE_BANK_RTGS, DBKDETLS.Name AS DRAWEE_BANK, GTWAY.TRANSACTION_DETAILS, PAYBANK.CHQ_DD_NUM, PAYBANK.DEPOS_TRANSF_DD__CHQ_ISSUE_DATE, PAYBANKRI.NEFT_RTGS_REF_NUM, PAYBANKRI.RECEIVED_MEDIUM_ID, PAYCASH.MO_TRANS_DATE, GTWAY.PAYMENT_GATE_TRANS_DATE FROM RECEIPT AS RCPT INNER JOIN ReceiptDetail orm ON orm.RECEIPTID = RCPT.RECEIPTID AND orm.IsDeleted = 0 INNER JOIN partner part ON part.PARTNERID = RCPT.PARTNERID LEFT JOIN partneraddressdetail patraddr ON patraddr.PartnerId = RCPT.PARTNERID LEFT JOIN Partnercontactdetail partconm ON partconm.PARTNERID = part.PARTNERID AND partconm.CommunicationModeId = 2 AND partconm.IsPrimary = 1 LEFT JOIN Partnercontactdetail partcone ON partcone.PARTNERID = part.PARTNERID AND partcone.CommunicationModeId = 1 AND partcone.IsPrimary = 1 LEFT JOIN PartnerPersonalDetail partper ON partper.PartnerPersonalDetailId = part.PartnerPersonalDetailId LEFT JOIN title AS tit ON part.TITLEID = tit.TITLEID LEFT JOIN dbo.PinCodeMap PCM ON PCM.PinCodeMapId = patraddr.PinCodeMapId LEFT JOIN state AS st ON PCM.STATEID = st.STATEID LEFT JOIN country AS cnt ON st.COUNTRYID = cnt.COUNTRYID LEFT JOIN [user] AS stfdtls ON stfdtls.Userid = RCPT.CreatedByUserId LEFT JOIN event AS eve ON orm.EventId = eve.EventId LEFT JOIN OfferingType AS offtypes ON orm.OFFERINGTYPEID = offtypes.OFFERINGTYPEID LEFT JOIN EventFeesType eft ON orm.EventFeesTypeId = eft.EventFeesTypeId LEFT JOIN TransactionType AS offmode ON orm.TransactionTypeId = offmode.TransactionTypeId LEFT JOIN TransactionMedium AS offmed1 ON orm.TransactionMediumId = offmed1.TransactionMediumId LEFT JOIN TransactionMedium AS offmed2 ON orm.TransactionMediumId = offmed2.TransactionMediumId LEFT JOIN ( SELECT rbd.PayeeBankId, -- Ensure that PayeeBankId exists in the rbd table CAST(rbd.TransactionDateTime AS Date) AS DEPOS_TRANSF_DD__CHQ_ISSUE_DATE, rbd.TransactionReference AS CHQ_DD_NUM, rbd.AccountId, -- Ensure that AccountId exists in the rbd table r.ReceiptId FROM receiptbankdetail rbd JOIN ReceiptDetail r ON rbd.ReceiptBankDetailId = r.ReceiptBankDetailId AND r.IsDeleted = 0 WHERE r.TransactionTypeId = 2 AND r.TransactionMediumId IN (3, 5) ) AS PAYBANK ON RCPT.ReceiptId = PAYBANK.ReceiptId LEFT JOIN ( SELECT DISTINCT rbd.PayeeBankId, rbd.ReceiptBankDetailId, r.TransactionMediumId AS RECEIVED_MEDIUM_ID, CAST(rbd.TransactionDateTime AS Date) AS DEPOS_TRANSF_DD__CHQ_ISSUE_DATE, rbd.TransactionReference AS NEFT_RTGS_REF_NUM, r.ReceiptId, rbd.AccountId -- Ensure that AccountId exists in the rbd table FROM receiptbankdetail rbd JOIN ReceiptDetail r ON rbd.ReceiptBankDetailId = r.ReceiptBankDetailId WHERE r.TransactionTypeId = 2 AND r.IsDeleted = 0 AND r.TransactionMediumId IN (2, 11, 12, 13) ) AS PAYBANKRI ON RCPT.ReceiptId = PAYBANKRI.ReceiptId LEFT JOIN ( SELECT CAST(rbd.TransactionDateTime AS Date) AS PAYMENT_GATE_TRANS_DATE, rbd.TransactionReference AS TRANSACTION_DETAILS, r.ReceiptId, r.TransactionTypeId, rbd.AccountId, rbd.PayeeBankId FROM receiptbankdetail rbd JOIN ReceiptDetail r ON rbd.ReceiptBankDetailId = r.ReceiptBankDetailId WHERE r.TransactionTypeId = 3 AND r.IsDeleted = 0 AND r.TransactionMediumId IN (7, 8, 10) ) AS GTWAY ON RCPT.ReceiptId = GTWAY.ReceiptId LEFT JOIN dbo.Account AS BANKDTLS ON PAYBANK.AccountId = BANKDTLS.AccountId LEFT JOIN dbo.Account AS BANKDTLS1 ON PAYBANKRI.AccountId = BANKDTLS1.AccountId LEFT JOIN dbo.Account AS BANKDTLS2 ON GTWAY.AccountId = BANKDTLS2.AccountId LEFT JOIN bank AS DBKDETLS ON PAYBANK.PayeeBankId = DBKDETLS.BankId LEFT JOIN bank AS DBKDETLS1 ON PAYBANKRI.PayeeBankId = DBKDETLS1.BankId LEFT JOIN bank AS DBKDETLS2 ON GTWAY.PayeeBankId = DBKDETLS2.BankId LEFT JOIN PaymentGateway AS GTWAYDETLS ON GTWAY.TransactionTypeId = GTWAYDETLS.PaymentGATEWAYID LEFT JOIN ( SELECT ReceivedDateTime AS MO_TRANS_DATE, ReceiptId FROM ReceiptDetail WHERE TransactionTypeId = 1 AND IsDeleted = 0 AND TransactionMediumId IN (6, 9) ) AS PAYCASH ON RCPT.RECEIPTID = PAYCASH.RECEIPTID WHERE RCPT.IsDeleted = 0 ) SELECT TOP 10 * FROM CTE_MainQuery WHERE PARTNER_ID = '18502' ORDER BY CREATED_TIME DESC;