Hey,
I have a matrix where I'm trying to sort results by the total 'Difference' but I'm running into issues.
The Matrix lists companies, how many tests we did last year/ this year and the difference (trying to identify people we have done more/less tests for).
> 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 'Adecco%' THEN 'Adecco'
> WHEN Clients.CompanyName LIKE 'Amazon%' THEN 'Amazon'
> WHEN Clients.CompanyName LIKE 'Mott MacD%' THEN 'Mott MacDonald Group'
> WHEN Clients.CompanyName LIKE 'Ringway Jacobs%' THEN 'Ringway Jacobs'
> WHEN Clients.CompanyName LIKE 'SGS%' THEN 'SGS'
> WHEN Clients.CompanyName LIKE 'Freightliner%' THEN 'Freightliner'
> WHEN Clients.CompanyName LIKE 'Amec Foster Whee%' THEN 'Amec Foster Wheeler'
> WHEN Clients.CompanyName LIKE 'Alstom Transport%' THEN 'Alstom Transport'
> WHEN Clients.CompanyName LIKE 'Gist%' THEN 'Gist'
> WHEN Clients.CompanyName LIKE 'Ainscough%' THEN 'Ainscough'
> WHEN Clients.CompanyName LIKE 'Eurovia%' THEN 'Eurovia'
> WHEN Clients.CompanyName LIKE 'Hitachi%' THEN 'Hitachi'
> WHEN Clients.CompanyName LIKE 'Eaton%' THEN 'Eaton'
> WHEN Clients.CompanyName LIKE 'Murphy%' THEN 'Murphy'
> 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.Description
>
> ORDER BY
>
> SUM(CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END)
I've tried ORDER BY & Interactive sorting. Order By does nothing, interactive sorting looks to do something, but they're not really in order and it then just throws the figures out a little.
Any help welcome, thanks
