T SQL Script: BACAS Get All Burials

 

SELECT TOP 100 PERCENT dbo.tblMainLocation.Description AS Cemetery, dbo.getSubLocationDesc(dbo.tblDetailedLocation.[Sub Location Id]) AS Area, 

               dbo.getGraveDesc(dbo.tblDetailedLocation.ID) AS GraveNo, dbo.tblAppointmentLink.CBID AS BurialRef, dbo.tblClient.Forenames AS DeceasedForenames, 

               dbo.tblClient.[Surname of Father] AS DeceasedSurname, dbo.tblAppointmentLink.[Appointment Date] AS AppointmentDate, 

               dbo.tblAppointmentLink.[Appointment Time] AS AppointmentTime

FROM  dbo.tblAppointmentLink INNER JOIN

               dbo.tblDetailedLocation INNER JOIN

               dbo.tblMainLocation ON dbo.tblDetailedLocation.[Main Location Id] = dbo.tblMainLocation.ID INNER JOIN

               dbo.tblGraveDeceased ON dbo.tblDetailedLocation.ID = dbo.tblGraveDeceased.[Grave ID] ON 

               dbo.tblAppointmentLink.UniqueID = dbo.tblGraveDeceased.AppLinkID AND dbo.tblAppointmentLink.[Client ID] = dbo.tblGraveDeceased.[Client ID] INNER JOIN

               dbo.tblClient ON dbo.tblGraveDeceased.[Client ID] = dbo.tblClient.ID

WHERE (dbo.tblAppointmentLink.AppointmentType = 1300) 

ORDER BY Cemetery, Area, GraveNo, BurialRef