Problem with creating a SQL view with multiple joins-CodePudding

I am trying to create a view which will be a base for Excel export in API. Basically, what it contains is information about particular projects. To said projects calculations can be added (it all happens on the form on frontend). Those calculations are called EBIT, EBIT and OVI. User can add either one, two or all of them, so for example there will be projects with only EBIT, but also projects with only for example EBIT, but also only with EBIT and OVI. View needs to return the project information with all chosen calculations in one row, so because some type of calculations wont be chosen by user there needs to be a type safety as well.

Code of my view:

CREATE VIEW [Signoff].[ExecelReport_uvw]
    AS SELECT 
    project.ProjectName,
    project.CreatedOn,
    
    project.ProjectId,
    subCategory.SubCategoryName,

    projectStatus.StatusName,
    overallCategory.CategoryName,
    projectUserResponsible.UserName,
    valueImprovementType.ValueImprovementTypeName,
    OVI.OverallImprovementTypeName,
    project.NameOfSuplier,
    improvementCalculation.Baseline,
    improvementCalculation.ImpactValue,
    project.ContractStartDate,
    project.ContractEndDate,
    userBussinessController.UserName as BussinessControllerName,
    bussinessControllerStatus.ApprovalStatusName BussinessControllerStatus,
    userBussinesOwner.UserName as BussinessOwnerName,
    bussinesOwnerStatus.ApprovalStatusName as BussinessOwnerStatus,
    userBussinessCFO.UserName as BussinessCFOName,
    bussinessCFOStatus.ApprovalStatusName as BussinessCFOStatus,
    project.IsEbitda,
    improvementCalculation.EBITDA

    FROM [Signoff].[Project] as project
    LEFT JOIN [Signoff].[OverallImprovementType] as OVI on project.OverallImprovementTypeId = OVI.OverallImprovementTypeId
    LEFT JOIN [Signoff].[SubCategory] as subCategory on project.GPSubCategory = subCategory.SubCategoryId
    LEFT JOIN [Signoff].[Category] as overallCategory on  project.GPCategory = overallCategory.CategoryId
    LEFT JOIN [Signoff].[ValueImprovementType] as valueImprovementType on project.ValueImprovementTypeId = valueImprovementType.ValueImprovementTypeId
    
    LEFT JOIN [Signoff].[Status] as projectStatus on project.ProjectStatus = projectStatus.StatusId
    LEFT JOIN [Signoff].[User] as projectUserResponsible on project.ProjectResponsible = projectUserResponsible.UserId

    LEFT JOIN [Signoff].[ProjectUser] as projectUserBussinessControler on project.ProjectId = projectUserBussinessControler.ProjectId AND projectUserBussinessControler.ProjectRoleId = 'A36FC6CD-9ED7-4AA8-B1BE-355E48BDE25A'
    LEFT JOIN [Signoff].[User] as userBussinessController on projectUserBussinessControler.ApproverId = userBussinessController.UserId
    LEFT JOIN [Signoff].[ApprovalStatus] as bussinessControllerStatus on projectUserBussinessControler.ApprovalStatusId = bussinessControllerStatus.ApprovalStatusId 

    LEFT JOIN [Signoff].[ProjectUser] as projectUserBussinessOwner on project.ProjectId = projectUserBussinessOwner.ProjectId AND projectUserBussinessOwner.ProjectRoleId = 'E1E23E4F-1CA4-4869-9387-43CEDAEBBBB0'
    LEFT JOIN [Signoff].[User] as userBussinesOwner on projectUserBussinessOwner.ApproverId = userBussinesOwner.UserId
    LEFT JOIN [Signoff].[ApprovalStatus] as bussinesOwnerStatus on projectUserBussinessOwner.ApprovalStatusId = bussinesOwnerStatus.ApprovalStatusId 

    LEFT JOIN [Signoff].[ProjectUser] as projectUserBussinessCFO on project.ProjectId = projectUserBussinessCFO.ProjectId AND projectUserBussinessCFO.ProjectRoleId = 'DA17CF66-1D61-460E-BF87-5D86744DF22A'
    LEFT JOIN [Signoff].[User] as userBussinessCFO on projectUserBussinessCFO.ApproverId = userBussinessCFO.UserId
    LEFT JOIN [Signoff].[ApprovalStatus] as bussinessCFOStatus on projectUserBussinessCFO.ApprovalStatusId = bussinessCFOStatus.ApprovalStatusId 

    LEFT JOIN [Signoff].[ProjectImprovementCalculation] as projectImprovementCalculation on project.ProjectId = projectImprovementCalculation.ProjectId
    LEFT JOIN [Signoff].[ImprovementCalculation] as improvementCalculation on projectImprovementCalculation.ImprovementCalculationId = improvementCalculation.ImprovementCalculationId

Improvement calculation table:

CREATE TABLE [Signoff].[ImprovementCalculation]
(
    [ImprovementCalculationId] INT NOT NULL IDENTITY,
    [Baseline] INT NOT NULL,
    [TotalSpend] INT NOT NULL,
    [ImpactValue] INT NOT NULL,
    [ImpactPercentage] INT NOT NULL,
    [EBITDA] INT NOT NULL,
    [CalculationType] VARCHAR (255) NOT NULL
)
GO

ALTER TABLE [Signoff].[ImprovementCalculation]
ADD CONSTRAINT [PK_ImprovemntCalculation] PRIMARY KEY([ImprovementCalculationId]);
GO

Project Improvement Calculation table:

CREATE TABLE [Signoff].[ProjectImprovementCalculation]
(
    [ProjectImprovementCalculationId] INT NOT NULL IDENTITY,
    [ProjectId] UNIQUEIDENTIFIER NOT NULL,
    [ImprovementCalculationId] INT NOT NULL,
)
GO

ALTER TABLE [Signoff].[ProjectImprovementCalculation]
ADD CONSTRAINT [PK_ProjectImprovementCalculation] PRIMARY KEY([ProjectImprovementCalculationId]);
GO

ALTER TABLE [Signoff].[ProjectImprovementCalculation]
ADD CONSTRAINT FK_ProjectProjectImprovementCalculation
FOREIGN KEY (ProjectId) REFERENCES [Signoff].[Project](ProjectId);
GO

ALTER TABLE [Signoff].[ProjectImprovementCalculation]
ADD CONSTRAINT FK_ImprovementCalculationProjectImprovementCalculation
FOREIGN KEY (ImprovementCalculationId) REFERENCES [Signoff].[ImprovementCalculation](ImprovementCalculationId);
GO

Just in case, although I don't think it's needed, the project table:

CREATE TABLE [Signoff].[Project]
(
    [ProjectId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), 
    [ProjectName] NVARCHAR(50) NOT NULL, 
    [LegalEntity] UNIQUEIDENTIFIER NOT NULL,
    [ValueImprovementTypeId] INT NOT NULL, 
    [OverallImprovementTypeId] INT NOT NULL, 
    [NameOfSuplier] NVARCHAR(50) NOT NULL, 
    [ContractStartDate] DATE NOT NULL, 
    [ContractEndDate] DATE NOT NULL,
    [GPCategory] UNIQUEIDENTIFIER NOT NULL,
    [GPSubCategory] UNIQUEIDENTIFIER NOT NULL,
    [ProjectResponsible] UNIQUEIDENTIFIER NOT NULL,
    [ProjectNumber] INT,
    [FullProjectNumber] VARCHAR(55),
    [ProjectStatus] UNIQUEIDENTIFIER NOT NULL DEFAULT '05c2f392-8b69-4915-a166-c4418889f9e8', 
    [IsCanceled] BIT NULL DEFAULT 0,
    [IsEbitda] BIT NOT NULL DEFAULT 0, 
    [CreatedOn] DATETIME NOT NULL DEFAULT SYSDATETIME()
)
GO

ALTER TABLE [Signoff].[Project]
ADD CONSTRAINT [PK_Project] PRIMARY KEY([ProjectId]);
GO

ALTER TABLE [Signoff].[Project]
ADD CONSTRAINT [FK_ProjectStatus] FOREIGN KEY ([ProjectStatus]) REFERENCES [Signoff].[Status]([StatusId]);
GO

I have so came up with this solution, but it returns every single calculation in a different row in a table, and I want all calculations be in a single row with a project, so not what I am looking for:

LEFT JOIN [Signoff].[ProjectImprovementCalculation] as projectImprovementCalculation on project.ProjectId = projectImprovementCalculation.ProjectId
LEFT JOIN [Signoff].[ImprovementCalculation] as improvementCalculation on projectImprovementCalculation.ImprovementCalculationId = improvementCalculation.ImprovementCalculationId

Does anyone knows how to do it? Or I am approaching a problem from completely wrong way? If the information I have written is a bit chaotic, something isn't understandable, I can rephrase it.

CodePudding user response：

I will assume that the available CalculationType values is fixed, that each project will have at most one improvement calculation per type, and that you wish to define fixed dedicated columns for BaseLine and ImpactValue each calculation type.

One approach is to use nested joins that will effectively LEFT JOIN the INNER JOINed combination of ProjectImprovementCalculation and ImprovementCalculation once for each calculation type. The results of each can then be referenced individually in the final select list.

Something like:

SELECT ...
    IC_AAA.BaseLine, IC_AAA.ImpactValue,
    IC_BBB.BaseLine, IC_BBB.ImpactValue,
    ...
FROM ...
LEFT JOIN Signoff.ProjectImprovementCalculation as PIC_AAA
    JOIN Signoff.ImprovementCalculation as IC_AAA
        ON IC_AAA.ImprovementCalculationId = PIC_AAA.ImprovementCalculationId
        AND IC_AAA.CalculationType = 'AAA'
    ON PIC_AAA.ProjectId = project.ProjectId
LEFT JOIN Signoff.ProjectImprovementCalculation as PIC_BBB
    JOIN Signoff.ImprovementCalculation as IC_BBB
        ON IC_BBB.ImprovementCalculationId = PIC_BBB.ImprovementCalculationId
        AND IC_BBB.CalculationType = 'BBB'
    ON PIC_BBB.ProjectId = project.ProjectId
...

The syntax is somewhat odd with two JOINs followed by two ON clauses. It would be clearer if parentheses were allowed, but (as far as I know) that is not part of the syntax.

There are several alternatives that accomplish the same. The following uses an OUTER APPLY:

SELECT ...
    AAA.BaseLine, AAA.ImpactValue,
    BBB.BaseLine, BBB.ImpactValue,
    ...
FROM ...
OUTER APPLY (
    SELECT IC.*
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
        AND IC.CalculationType = 'AAA'
    WHERE PIC.ProjectId = project.ProjectId
) AAA
OUTER APPLY (
    SELECT IC.*
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
        AND IC.CalculationType = 'BBB'
    WHERE PIC.ProjectId = project.ProjectId
) BBB
...

Using a Common Table Expression (CTE) can reduce some of the duplication as well as making the query a bit more readable.

;WITH ImprovementCTE AS (
    SELECT PIC.ProjectId, IC.*
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
)
SELECT ...
    AAA.BaseLine, AAA.ImpactValue,
    BBB.BaseLine, BBB.ImpactValue,
    ...
FROM ...
LEFT JOIN ImprovementCTE AAA
    ON AAA.ProjectId = project.ProjectId
    AND AAA.CalculationType = 'AAA'
LEFT JOIN ImprovementCTE BBB
    ON BBB.ProjectId = project.ProjectId
    AND BBB.CalculationType = 'BBB'
...

You might also try using conditional aggregation in a single CROSS APPLY:

SELECT ...
    IC.BaseLineAAA, IC.ImpactValueAAA,
    IC.BaseLineBBB, IC.ImpactValueBBB,
    ...
FROM ...
CROSS APPLY (
    SELECT
        BaseLineAAA = SUM(CASE WHEN IC.CalculationType = 'AAA' THEN IC.BaseLine),
        ImpactValueAAA = SUM(CASE WHEN IC.CalculationType = 'AAA' THEN IC.ImpactValue),
        BaseLineBBB = SUM(CASE WHEN IC.CalculationType = 'BBB' THEN IC.BaseLine),
        ImpactValueBBB = SUM(CASE WHEN IC.CalculationType = 'BBB' THEN IC.ImpactValue),
        ...
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
    WHERE PIC.ProjectId = project.ProjectId
) IC

I expect there are additional approaches such as using a PIVOTs.

If the above appears to suit your needs, you should still run tests and examine the execution plans to see which performs best. Some may have a tendency to retrieve all ImprovementCalculation rows even when a subset of projects are selected.

To handle missing calculation types, you can use the ISNULL() function to provide a default. If you need to force a blank value in an otherwise numeric result, you might need to use something like ISNULL(CONVERT(VARCHAR(50), result), '').