Wednesday, January 15, 2020

How to create a Veiw with multiple Joins to retrieve the values for Source Table from Lookup Tables in SQL Server

How to retrieve the values in a View from Lookup Tables by Joining the Source Table with multiple Lookup Tables in SQL Server
Scenario :
Suppose we have a Sample table as below showing details of the Cost by Month associated with the Roles, Cost split by CostCenter, CostGeo and CostYear.
Select * From [dbo].[JobCostingDetails]


Now we have to calculate the values for [USA Flag], [MonthlyHours] and [CostProbability]
using the below logic :
[USA Flag]='Y' for a [RoleID] when at least one of the [CostGeo]='USA' in the [CostYear]
[MonthlyHours] Field will be calculated by mapping the source table with the Lookup table
[dbo].[Lkp_CostByGeo]

[CostProbability] Field will be calculated by mapping the source table with the Lookup table 
[dbo].[Lkp_CostProbability]


Now we will create a View to display the Source table along with the other Fields information using the above logic :
CREATE VIEW vw_CostingProbabilityDetails
AS
(
SELECT [Id]
      ,JC.[RoleIdentifier]
      ,[RoleID]
      ,JC.[RoleName]
      ,JC.[Function]
      ,JC.[JobGroup]
      ,[CostCenterCode]
      ,JC.[CostGeo]
      ,JC.[CostYear]
      ,(Case When T1.[GeoCnt]>0 Then 'Y' Else 'N' End) AS [USA Flag]
      ,T2.[MonthlyHours]
      ,(Case When JC.[JobGroup]='L1' Then 1 Else T3.[CostProbability] End) 
       AS [CostProbability]
FROM [dbo].[JobCostingDetails] JC
INNER JOIN
( Select [RoleIdentifier],[CostYear],
SUM(Case When [CostGeo]='USA' Then 1 Else 0 End) AS GeoCnt
From [dbo].[JobCostingDetails]
Group By [RoleIdentifier],[CostYear]
) T1
ON (JC.[RoleIdentifier]=T1.[RoleIdentifier] AND JC.[CostYear]=T1.[CostYear])
INNER JOIN [dbo].[Lkp_CostByGeo] T2
ON JC.[CostGeo]=T2.[CostGeo]
INNER JOIN [dbo].[Lkp_CostProbability] T3
ON JC.[Function]=T3.[Function] AND JC.[JobGroup]=T3.[JobGroup]
 )
GO

Select * From vw_CostingProbabilityDetails
GO

Result :

The Update version of the above View can be written as per below :
UPDATE  [dbo].[JobCostingDetails]
SET [USA Flag]=(Case When T1.[GeoCnt]>0 Then 'Y' Else 'N' End),
[MonthlyHours]=T2.[MonthlyHours],
[CostProbability]=(Case When JC.[JobGroup]='L1' Then 1 Else T3.[CostProbability] End)
FROM [dbo].[JobCostingDetails] JC
INNER JOIN
( Select [RoleIdentifier],[CostYear],
SUM(Case When [CostGeo]='USA' Then 1 Else 0 End) AS GeoCnt
From [dbo].[JobCostingDetails]
Group By [RoleIdentifier],[CostYear]
) T1
ON (JC.[RoleIdentifier]=T1.[RoleIdentifier] AND JC.[CostYear]=T1.[CostYear])
INNER JOIN [dbo].[Lkp_CostByGeo] T2
ON JC.[CostGeo]=T2.[CostGeo]
INNER JOIN [dbo].[Lkp_CostProbability] T3
ON JC.[Function]=T3.[Function] AND JC.[JobGroup]=T3.[JobGroup]
GO

Select * From [dbo].[JobCostingDetails]
GO

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.