SELECT [p].[Id], [p].[ActualProcedureCodeId], [p].[ActualProcedureDescription], [p].[AdmissionId], [p].[AnestheticId], [p].[AnestheticsDescription], [p].[CancelationTypeId], [p].[CancellationDescription], [p].[CreationTime], [p].[IsCancelled],
[p].[IsEmergency], [p].[IsSmartScheduled], [p].[IsWli], [p].[LastModificationTime], [p].[Order], [p].[OriginalSessionId], [p].[PatientId], [p].[ProcedureTimesId], [p].[RecoveryBayId], [p].[SessionId], [p].[SpecialityId], [p].[WaitingListId], [p.Patient].[Id],
[p.Patient].[Age], [p.Patient].[CreationTime], [p.Patient].[HasDiabetes], [p.Patient].[HasMrsa], [p.Patient].[HospitalNumber], [p.Patient].[LastModificationTime], [p.Patient].[Name], [p.Patient].[Surname], [p.Admission].[Id], [p.Admission].[CreationTime],
[p.Admission].[LastModificationTime], [p.Admission].[Name], [p.WaitingList].[Id], [p.WaitingList].[AdmissionDate], [p.WaitingList].[AdmissionId], [p.WaitingList].[AppointmentDate], [p.WaitingList].[BreachDate], [p.WaitingList].[ConsultantId],
[p.WaitingList].[CreationTime], [p.WaitingList].[CurrentStatusCode], [p.WaitingList].[CurrentStatusDate], [p.WaitingList].[DaysWaiting], [p.WaitingList].[IPListDate], [p.WaitingList].[IntendedProcedureCodeId], [p.WaitingList].[IsPreassessed], [p.WaitingList].[LastModificationTime], [p.WaitingList].[PathwayEndDate], [p.WaitingList].[PathwayId], [p.WaitingList].[PathwayStartDate], [p.WaitingList].[PatientId], [p.WaitingList].[ProcedureId], [p.WaitingList].[ReferalDate], [p.WaitingList].[SharedBreach], [p.WaitingList].[SiteId], [p.WaitingList].[SpecialityId], [p.WaitingList].[SuggestedTime], [p.WaitingList].[SuggestedTimeCalculationType], [p.WaitingList].[WeeksWaiting], [p.WaitingList].[WorkflowStatusId], [p.WaitingList.Consultant].[Id], [p.WaitingList.Consultant].[CreationTime], [p.WaitingList.Consultant].[LastModificationTime], [p.WaitingList.Consultant].[Name], [p.WaitingList.Consultant].[SourceId], [p.WaitingList.Consultant].[UserId], [p.WaitingList.IntendedProcedureCode].[Id], [p.WaitingList.IntendedProcedureCode].[CreationTime], [p.WaitingList.IntendedProcedureCode].[DaysOfStay], [p.WaitingList.IntendedProcedureCode].[Description], [p.WaitingList.IntendedProcedureCode].[Income], [p.WaitingList.IntendedProcedureCode].[LastModificationTime], [p.WaitingList.IntendedProcedureCode].[Name], [p.WaitingList.Pathway].[Id], [p.WaitingList.Pathway].[CreationTime], [p.WaitingList.Pathway].[LastModificationTime], [p.WaitingList.Pathway].[Name], [p.WaitingList.Patient].[Id], [p.WaitingList.Patient].[Age], [p.WaitingList.Patient].[CreationTime], [p.WaitingList.Patient].[HasDiabetes], [p.WaitingList.Patient].[HasMrsa], [p.WaitingList.Patient].[HospitalNumber], [p.WaitingList.Patient].[LastModificationTime], [p.WaitingList.Patient].[Name], [p.WaitingList.Patient].[Surname], [p.WaitingList.Site].[Id]
FROM [Procedures] AS [p]
LEFT JOIN [Patients] AS [p.Patient] ON [p].[PatientId] = [p.Patient].[Id]
LEFT JOIN [Admissions] AS [p.Admission] ON [p].[AdmissionId] = [p.Admission].[Id]
LEFT JOIN [WaitingLists] AS [p.WaitingList] ON [p].[WaitingListId] = [p.WaitingList].[Id]
LEFT JOIN [Employees] AS [p.WaitingList.Consultant] ON [p.WaitingList].[ConsultantId] = [p.WaitingList.Consultant].[Id]
LEFT JOIN [Sites] AS [p.WaitingList.Site] ON [p.WaitingList].[SiteId] = [p.WaitingList.Site].[Id]
LEFT JOIN [OpcsCodes] AS [p.WaitingList.IntendedProcedureCode] ON [p.WaitingList].[IntendedProcedureCodeId] = [p.WaitingList.IntendedProcedureCode].[Id]
LEFT JOIN [Pathways] AS [p.WaitingList.Pathway] ON [p.WaitingList].[PathwayId] = [p.WaitingList.Pathway].[Id]
LEFT JOIN [Patients] AS [p.WaitingList.Patient] ON [p.WaitingList].[PatientId] = [p.WaitingList.Patient].[Id]
WHERE [p].[SessionId] IS NULL
AND [p].[WaitingListId] IS NOT NULL
AND ([p.WaitingList].[ConsultantId] IS NULL OR [p.WaitingList.Consultant].[UserId] IS NOT NULL)
AND (1=0 OR [p.WaitingList].SiteId IN (3)) AND (1=0 OR [p].[SpecialityId] IN (23))
AND (1=0 OR [p.WaitingList.Consultant].[UserId] IN (698))
AND ([p].[SpecialityId] = NULL or 1=1)
ORDER BY [p.WaitingList.Pathway].[Id], [p.WaitingList.Patient].[Id], [p.Patient].[Id]