Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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:
SQL
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:
SQL
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:
SQL
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:
SQL
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.
Posted
Comments
PIEBALDconsult 28-Mar-22 18:54pm    
Do you need a PIVOT?
j snooze 29-Mar-22 17:30pm    
what version of SQL Server are you using? I believe they introduced a function called String_Agg() in 2017?
Here is a little write up on it. SQL Server STRING_AGG() Function By Practical Examples[^]
CHill60 1-Apr-22 6:49am    
Try providing some sample data, and either the rest of the tables in your query OR a query that only uses the tables you have shared.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900