Hey,
I'm using the following code to display the number of tests we do for different companies each year, I have placed this into a matrix so that it pulls through for each month, comparing 2018 with 2019. U then have another column at the end of my Matrix which gives me the difference. How can I order the matrix by the final column (i.e. to show all customers in order of who we have the biggest difference YOY).
SELECT
CAST(Jobs.ID AS INT) AS [Jobs ID]
,Tests.ID AS [Tests ID]
,Clients.ID
,Clients.CompanyName
,Clients.Active
,CASE
WHEN Clients.CompanyName LIKE 'ghi%' THEN 'ghi'
WHEN Clients.CompanyName LIKE 'def%' THEN 'def'
WHEN Clients.CompanyName LIKE 'abc%' THEN 'abc'
ELSE Clients.CompanyName END AS [CompanyNameCombined]
,Clients.PurchaseOrderNumberIsMandatory
,JobStatuses.Description AS [JobStatuses Description]
,Appointments.StartDateTime
,YEAR(Appointments.StartDateTime) AS [Year]
,MONTH(Appointments.StartDateTime) AS [Month]
,CASE WHEN Clients.CompanyName IS NOT NULL THEN 1 ELSE 0 END AS [NumberofTests]
,SUM(CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END) AS [2018v2017]
,Appointments.EndDateTime
,TestStatuses.Description AS [TestStatuses Description]
,ServiceTypes.Description AS [ServiceTypes Description]
,JobCandidates.CandidateName
,TestIdentifiers.Description AS [TestIdentifiers Description]
,ClientContacts.Fullname
,MedicalCentres.Name
,Jobs.OnSiteAddress1
,Jobs.OnSiteAddress2
,Jobs.OnSiteAddress3
,Jobs.OnSiteAddress4
,Jobs.OnSiteTown
,Jobs.OnSiteCounty
,Jobs.OnSitePostcode
,Jobs.CancellationReason
,Industries.Description AS [Industry]
,Users.FirstnameAndSurname
,ClientContacts.Email
,MedicalProfessionals.Fullname AS [Medical Professional]
,Tests.PriceToClient
,MedicalProfessionalQualifications.Description AS [Qualification]
,TestClinicalReports.IsCaseClosed
,Clients.AccountNumber
,Certificates.DatePostRoomPrinted
,MedicalCentreTypes.Description
,Tests.DateReferred
,FinalResults.Description AS [Final Result]
,Candidates.DateOfBirth
,Certificates.ExpiryDate
,Certificates.DateClientPrinted
,Jobs.PurchaseOrderNumber
,ServiceTypes.AppointmentMinutes
,MIN(JobInvoices.InvoiceNumber) AS [Invoice Number]
,MedicalCentres.Postcode
,ReferralStatuses.Description AS [ReferralStatus]
,MIN(Adjustments.ID) AS [Adjustment]
,Jobs.PreviousJobNotes
,JobCandidates.IndustryReference
,CONVERT(varchar(12),Appointments.StartDateTime,103) + MedicalCentres.Name + MedicalCentreRooms.Description AS Concat
FROM
Jobs
FULL OUTER JOIN Clients
ON Jobs.ClientID = Clients.ID
FULL OUTER JOIN JobStatuses
ON Jobs.JobStatusID = JobStatuses.ID
FULL OUTER JOIN JobCandidates
ON Jobs.ID = JobCandidates.JobID
FULL OUTER JOIN Tests
ON JobCandidates.ID = Tests.JobCandidateID
FULL OUTER JOIN TestStatuses
ON Tests.TestStatusID = TestStatuses.ID
FULL OUTER JOIN Appointments
ON Tests.AppointmentID = Appointments.ID
FULL OUTER JOIN ServiceTypes
ON Tests.ServiceTypeID = ServiceTypes.ID
FULL OUTER JOIN TestIdentifiers
ON Tests.TestIdentifierID = TestIdentifiers.ID
FULL OUTER JOIN ClientContacts
ON Jobs.ClientContactID = ClientContacts.ID
FULL OUTER JOIN MedicalCentreRooms
ON MedicalCentreRooms.ID = Appointments.OffSite_MedicalCentreRoomID
FULL OUTER JOIN MedicalCentres
ON MedicalCentreRooms.MedicalCentreID = MedicalCentres.ID
FULL OUTER JOIN Industries
ON Clients.IndustryID = Industries.ID
FULL OUTER JOIN Users
ON Jobs.CreatedByUserID = Users.ID
FULL OUTER JOIN MedicalProfessionals
ON Tests.TestPerformedByMedicalProfessionalID = MedicalProfessionals.ID OR Appointments.OnSite_MedicalProfessionalID = MedicalProfessionals.ID
FULL OUTER JOIN MedicalProfessionalQualifications
ON MedicalProfessionals.MedicalProfessionalQualificationID = MedicalProfessionalQualifications.ID
FULL OUTER JOIN TestClinicalReports
ON Tests.ID = TestClinicalReports.TestID
FULL OUTER JOIN Certificates
ON Tests.CurrentCertificateID = Certificates.ID
FULL OUTER JOIN MedicalCentreTypes
ON MedicalCentres.MedicalCentreTypeID = MedicalCentreTypes.ID
FULL OUTER JOIN FinalResults
ON Certificates.FinalResultID = FinalResults.ID
FULL OUTER JOIN Candidates
ON JobCandidates.CandidateID = Candidates.ID
FULL OUTER JOIN JobInvoices
ON Jobs.ID = JobInvoices.JobID
FULL OUTER JOIN ReferralStatuses
ON Tests.ReferralStatusID = ReferralStatuses.ID
FULL OUTER JOIN Adjustments
ON Jobs.ID = Adjustments.JobID
WHERE
JobStatuses.Description NOT LIKE N'Cancelled'
AND (YEAR(Appointments.StartDateTime) = YEAR(getdate()) OR YEAR(Appointments.StartDateTime) = YEAR(getdate())-1)
AND (MONTH(Appointments.StartDateTime) = MONTH(getdate()) OR MONTH(Appointments.StartDateTime) = MONTH(getdate())-1 OR MONTH(Appointments.StartDateTime) = MONTH(getdate())-2 or MONTH(Appointments.StartDateTime) = MONTH(getdate())+1 )
AND Clients.CompanyName LIKE @Company
AND TestStatuses.Description NOT LIKE 'Cancelled'
AND Clients.CompanyName NOT LIKE '%test%'
AND Clients.CompanyName NOT LIKE 'DCSL'
AND Clients.Active = 'True'
GROUP BY
Jobs.ID
,Tests.ID
,Clients.ID
,Clients.CompanyName
,Clients.Active
,Clients.PurchaseOrderNumberIsMandatory
,JobStatuses.Description
,Appointments.StartDateTime
,Appointments.EndDateTime
,TestStatuses.Description
,ServiceTypes.Description
,JobCandidates.CandidateName
,TestIdentifiers.Description
,ClientContacts.Fullname
,MedicalCentres.Name
,Jobs.OnSiteAddress1
,Jobs.OnSiteAddress2
,Jobs.OnSiteAddress3
,Jobs.OnSiteAddress4
,Jobs.OnSiteTown
,Jobs.OnSiteCounty
,Jobs.OnSitePostcode
,Industries.Description
,Users.FirstnameAndSurname
,ClientContacts.Email
,MedicalProfessionals.Fullname
,Tests.PriceToClient
,MedicalProfessionalQualifications.Description
,TestClinicalReports.IsCaseClosed
,Clients.AccountNumber
,Certificates.DatePostRoomPrinted
,MedicalCentreTypes.Description
,Tests.DateReferred
,FinalResults.Description
,Candidates.DateOfBirth
,Certificates.ExpiryDate
,Certificates.DateClientPrinted
,Jobs.PurchaseOrderNumber
,Jobs.CancellationReason
,ServiceTypes.AppointmentMinutes
,MedicalCentres.Postcode
,ReferralStatuses.Description
,Jobs.PreviousJobNotes
,JobCandidates.IndustryReference
,MedicalCentreRooms.DescriptionORDER BY
SUM(CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END)
The below gives the output
Thank you
Callum