Hello, 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
What I have tried:
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? I see what the information I have written is a bit chaotic, something isn't understandable, I can rephrase it.