th CTE_MainQuery As ( select distinct 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, --orm.Amount as OFFERING, offmode.Name as RECEIVED_MODE, case when (offmode.Name = 'Cash') then offmed1.Name else null end CASH_MEDIUM, case when (offmode.Name <> 'Cash') then offmed2.Name else null end 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, --RCPT.REMARKS, PAYBANK.CHQ_DD_NUM, PAYBANK.DEPOS_TRANSF_DD__CHQ_ISSUE_DATE, (select Distinct rbd.TransactionReference 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) and r.ReceiptId=PAYBANKRI.ReceiptId and rbd.ReceiptBankDetailId=PAYBANKRI.ReceiptBankDetailId) As NEFT_RTGS_REF_NUM, PAYBANKRI.RECEIVED_MEDIUM_ID, PAYCASH.ReceivedDateTime as 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 ( select ReceivedDateTime, ReceiptId from ReceiptDetail where TransactionTypeId = 1 and IsDeleted = 0 and TransactionMediumId in (6, 9)) as PAYCASH on RCPT.RECEIPTID = PAYCASH.RECEIPTID left join ( select rbd.PayeeBankId, Cast (rbd.TransactionDateTime As Date) as DEPOS_TRANSF_DD__CHQ_ISSUE_DATE, rbd.TransactionReference as CHQ_DD_NUM, rbd.AccountId, 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 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 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 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 where RCPT.IsDeleted = 0 ) Select TOP 10 * from CTE_MainQuery where PARTNER_ID ='81712' Order by CREATED_TIME Desc