I have the below query, wish I want to expand. Currently it returns
LEN({Patient_Letters}.[Binary_File]), {Patient_Letters}.[Id],

Previously there’s only been one entry in the Patient_Letters table, per referral.
There’s an entity called LetterType, all of which have the value 1, though, that’s not referenced here because it’s not previously been relevant.

There’s now two letters in this table, per referral. One with LetterType = 1, and the other with LetterType =2.

I want to expand this query, so it returns

LEN({Patient_Letters}.[Binary_File]), {Patient_Letters}.[Id],

For both letters, but marks one as “Incoming” and one as “Outcome”.

I would be very grateful if you could help me with this.

Thank you!

SELECT {Patient_Referrals}.Id,{Patient_Referrals}.[Referral_DateTime],{Pathways}.[Name],
    LEN({Patient_Letters}.[Binary_File]), {Patient_Letters}.[Id],
    {Patient_Referrals}.[Awaiting_Sec_Intervention],
    {Patient_Referrals}.[Receiver_Read_DateTime]
FROM {Patient_Referrals}
JOIN {Patient_Attendances} on {Patient_Referrals}.[Attendance_ID] = {Patient_Attendances}.Id
JOIN {Pathways} on {Pathways}.[Id] = {Patient_Referrals}.[Service_ID]
LEFT JOIN {Patient_Letters} on {Patient_Letters}.[Referral_ID] = {Patient_Referrals}.Id
JOIN {Patients} ON {Patients}.[id] = {Patient_Attendances}.[Patient_ID]
WHERE {Patients}.[id] = @PatientId 
AND 
    ---------------------------------------------------------------
    ( 
        -- Same Tenant
    {Patient_Referrals}.[Tenant_Id] = @TenantId 
        -- Same organization 
 OR {Patient_Referrals}.[Tenant_Id] in 
     (select {Tenant_Config}.[Tenant_Id] 
      FROM {Tenant_Config} 
      where {Tenant_Config}.[Org_Id] = @OrganizationId)
        -- Same DSG
 OR {Patient_Referrals}.[Tenant_Id] in 
     (SELECT {Tenant_Config}.[Tenant_Id] 
      FROM {Tenant_Config}  
      JOIN {DSG_Organisations} ON {Tenant_Config}.[Org_Id] = {DSG_Organisations}.[OrganisationId]
      WHERE {DSG_Organisations}.[DSGId] in 
          (select {DSG_Organisations}.[DSGId]
           FROM {DSG_Organisations} 
           WHERE {DSG_Organisations}.[OrganisationId] = @OrganizationId)
          )
    )
ORDER BY {Patient_Referrals}.[Referral_DateTime] DESC
OFFSET @StartIndex ROWS FETCH NEXT @MaxRecords ROWS ONLY

I’ve tried many variations of this query. None of which I’ve kept because none of them would execute, let along return a valid set of results.

This is actually a query within the Outsystems platform.