How to display employee details according design

Question

I have problem in

 

1) retrieve employee details based on referenceNo where by the the details duplicate in other employee details column if referenceNo store only 1 data or 2 data

2) retrieve date of payment if 'Mode of Payment' is "ENETS/VISA/MASTERCARD", get booking_date in Booking table, if not, put "" where by 'Mode of Payment' need to retrieved from column in query

 

 

Here I attached Report.zip which is contain design of report and requirement of database 

 

Below here is my SQL query that I have done: 

SELECT b.[REFNO] as 'Appointment ID', ISNULL(r.[OriginalRefNo], '') as'Original Appointment ID',

CASE

WHEN b.[STATUS]= 0 THEN 'Cancelled'

WHEN b.[STATUS]= 1 THEN 'Confirmed'

WHEN b.[STATUS]= 2 THEN 'Pending (IBT Payment)'

WHEN b.[STATUS]= 3 THEN 'cancelled (Rescheduled)'

ELSE 'Refund'

END as 'Booking Status',

b.[CompanyName] as 'Name of Company', b.[ACRA] as 'ACRA/UEN', s.[NAME] as 'Sector', b.[Workforce] as 'Workforce', b.[NatureOfConsultation] as 'Nature of Consultation',

ISNULL(e.[Name],'') as 'Rep_1', ISNULL(e.[Designation],'') as 'Designation_1', ISNULL(e.[Email],'') as 'Email_1', ISNULL(e.[ContactNo],'') as 'HP_1',

ISNULL(e.[Name],'') as 'Rep_2', ISNULL(e.[Designation],'') as 'Designation_2', ISNULL(e.[Email],'') as 'Email_2', ISNULL(e.[ContactNo],'') as 'HP_2',

ISNULL(e.[Name],'') as 'Rep_3', ISNULL(e.[Designation],'') as 'Designation_3', ISNULL(e.[Email],'') as 'Email_3', ISNULL(e.[ContactNo],'') as 'HP_3',

CASE

WHEN b.[STATUS]= 2 THEN ''

WHEN b.[ReceiptFilePath] IS NOT NULL THEN 'InterBank Transfer'

ELSE 'ENETS/VISA/MASTERCARD'

END as 'Mode of Payment',

CASE

WHEN b.[STATUS]= 2 THEN ''

ELSE b.[Amount]

END as 'Amount Paid', b.[BOOKING_DATE] as 'Date of Booking',

/*CASE

WHEN = 'ENETS/VISA/MASTERCARD' THEN b.[BOOKING_DATE]

ELSE ''

END as 'Date of Payment',*/

[DATE] as 'Date of Consultation'

FROM

[BOOKING_DTLS] b

left JOIN [RescheduledAppointment] r ON b.[REFNO] = r.[REFNO]

left JOIN [Sector] s ON b.[SectorId] = s.[Value]

left JOIN [EmployerRepresentativeInfo] e ON b.[REFNO] = e.[REFNO]

WHERE b.REFNO IS NOT NULL

ORDER BY b.[REFNO];

GO