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

--------------------------------------------------------------------------------------------------------

How to Calculate Non Zero Rows Count, Average IF when Sum of the Rows is greater than Zero in SQL Server

How to Calculate Non Zero Rows Count and Average IF when Sum of the Rows is greater than Zero 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].[ResourceCostSample]

Now from the above Table, we need to Calculate the Average Cost details for each Month by Grouping data by [RoleIdentifier], [CostYear].
While calculating Average Cost for Month(eg. M1), per each [RoleIdentifier] by [CostYear],
we need to consider the Count of Rows only where the M1>0, means we have to Ignore the Zeros along with Null values.
We know that, the COUNT and SUM Functions will ignore NULL values in SQL SERVER.
But COUNT Function will consider Zero values, which we should not consider here.
Now we will see the COUNT and SUM of values for each Month by grouping as discussed.

SELECT [RoleIdentifier], [CostYear], 
SUM(M1) M1Sum,
SUM(Case When M1>0 Then 1 Else M1 End) M1Cnt,
SUM(M2) M2Sum,
SUM(Case When M2>0 Then 1 Else M2 End) M2Cnt,
SUM(M3) M3Sum,
SUM(Case When M3>0 Then 1 Else M3 End) M3Cnt,
SUM(M4) M4Sum,
SUM(Case When M4>0 Then 1 Else M4 End) M4Cnt,
SUM(M5) M5Sum,
SUM(Case When M5>0 Then 1 Else M5 End) M5Cnt,
SUM(M6) M6Sum,
SUM(Case When M6>0 Then 1 Else M6 End) M6Cnt,
SUM(M7) M7Sum,
SUM(Case When M7>0 Then 1 Else M7 End) M7Cnt,
SUM(M8) M8Sum,
SUM(Case When M8>0 Then 1 Else M8 End) M8Cnt,
SUM(M9) M9Sum,
SUM(Case When M9>0 Then 1 Else M9 End) M9Cnt,
SUM(M10) M10Sum,
SUM(Case When M10>0 Then 1 Else M10 End) M10Cnt,
SUM(M11) M11Sum,
SUM(Case When M11>0 Then 1 Else M11 End) M11Cnt,
SUM(M12) M12Sum,
SUM(Case When M12>0 Then 1 Else M12 End) M12Cnt
From [dbo].[ResourceCostSample]
Group By [RoleIdentifier], [CostYear]
GO

Result :

Now we will calculate the Average as discussed above. We will create a View here to show the Average Costing details, instead of updating the Source table.
CREATE VIEW vw_AvgCostingDetails
AS
(
SELECT  [Id], RC.[RoleIdentifier], [RoleID],[RoleName],[Function]
        ,[CostCenterCode],[CostGeo], RC.[CostYear]
        ,(CASE When T1.[M1Sum]>0 Then T1.[M1Sum]/T1.[M1Cnt] Else 0 End) AS [M1]
        ,(CASE When T1.[M2Sum]>0 Then T1.[M2Sum]/T1.[M2Cnt] Else 0 End) AS [M2]
,(CASE When T1.[M3Sum]>0 Then T1.[M3Sum]/T1.[M3Cnt] Else 0 End) AS [M3]
,(CASE When T1.[M4Sum]>0 Then T1.[M4Sum]/T1.[M4Cnt] Else 0 End) AS [M4]
,(CASE When T1.[M5Sum]>0 Then T1.[M5Sum]/T1.[M5Cnt] Else 0 End) AS [M5]
,(CASE When T1.[M6Sum]>0 Then T1.[M6Sum]/T1.[M6Cnt] Else 0 End) AS [M6]
,(CASE When T1.[M7Sum]>0 Then T1.[M7Sum]/T1.[M7Cnt] Else 0 End) AS [M7]
,(CASE When T1.[M8Sum]>0 Then T1.[M8Sum]/T1.[M8Cnt] Else 0 End) AS [M8]
,(CASE When T1.[M9Sum]>0 Then T1.[M9Sum]/T1.[M9Cnt] Else 0 End) AS [M9]
,(CASE When T1.[M10Sum]>0 Then T1.[M10Sum]/T1.[M10Cnt] Else 0 End) AS [M10]
,(CASE When T1.[M11Sum]>0 Then T1.[M11Sum]/T1.[M11Cnt] Else 0 End) AS [M11]
,(CASE When T1.[M12Sum]>0 Then T1.[M12Sum]/T1.[M12Cnt] Else 0 End) AS [M12]
FROM [dbo].[ResourceCostSample] RC
INNER JOIN
( SELECT [RoleIdentifier], CostYear, 
SUM(M1) M1Sum,
SUM(Case When M1>0 Then 1 Else M1 End) M1Cnt,
SUM(M2) M2Sum,
SUM(Case When M2>0 Then 1 Else M2 End) M2Cnt,
SUM(M3) M3Sum,
SUM(Case When M3>0 Then 1 Else M3 End) M3Cnt,
SUM(M4) M4Sum,
SUM(Case When M4>0 Then 1 Else M4 End) M4Cnt,
SUM(M5) M5Sum,
SUM(Case When M5>0 Then 1 Else M5 End) M5Cnt,
SUM(M6) M6Sum,
SUM(Case When M6>0 Then 1 Else M6 End) M6Cnt,
SUM(M7) M7Sum,
SUM(Case When M7>0 Then 1 Else M7 End) M7Cnt,
SUM(M8) M8Sum,
SUM(Case When M8>0 Then 1 Else M8 End) M8Cnt,
SUM(M9) M9Sum,
SUM(Case When M9>0 Then 1 Else M9 End) M9Cnt,
SUM(M10) M10Sum,
SUM(Case When M10>0 Then 1 Else M10 End) M10Cnt,
Sum(M11) M11Sum,
SUM(Case When M11>0 Then 1 Else M11 End) M11Cnt,
SUM(M12) M12Sum,
SUM(Case When M12>0 Then 1 Else M12 End) M12Cnt
From [dbo].[ResourceCostSample]
Group By [RoleIdentifier], [CostYear]
) T1
ON RC.[RoleIdentifier]=T1.[RoleIdentifier] AND
RC.[CostYear]=T1.[CostYear]
)
GO

Select * From vw_AvgCostingDetails
GO

Result :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to Calculate Columns Count IF when Sum of the Columns is greater than Zero in SQL Server

How to Calculate Columns Count, Average IF when Sum of the Columns is greater than Zero 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].[ResourceCostingSample]

Now in the above Table, we have to Calculate the field "AvgCost By RoleGeoYear" as per below :
First we need to find the Sum of the Costs of all Months, Sum(M1, M2,...M11, M12) by Grouping the data by [RoleIdentifier], [CostGeo], [CostYear].
Similarly we need to find the Count of the Months, where SUM(Cost)>0, which means we have to consider only where SUM(M1)>0, SUM(M2)>0...SUM(M11)>0, SUM(M12)>0.
Eg:
the Count of Months is 4 for Role77AnalytstHR ; US ; 2014
the Count of Months is 8 for Role77AnalytstHR ; US ; 2015

We can achieve the SUM, and COUNT as expected above using the below SQL Query :
SELECT [RoleIdentifier], [CostGeo], [CostYear],
SUM(ISNULL(M1,0)+ISNULL(M2,0)+ISNULL(M3,0)+ISNULL(M4,0)+ISNULL(M5,0)+
ISNULL(M6,0)+ISNULL(M7,0)+ISNULL(M8,0)+
ISNULL(M9,0)+ISNULL(M10,0)+
ISNULL(M11,0)+ISNULL(M12,0)) AS SumOfCostMonths,
( CASE WHEN SUM(M1)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M2)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M3)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M4)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M5)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M6)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M7)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M8)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M9)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M10)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M11)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M12)>0 THEN 1 ELSE 0 END )  AS CountOfCostMonths
From [dbo].[ResourceCostingSample]
Group By [RoleIdentifier], [CostGeo], [CostYear]

Result:

Next we need to Calculate and Update the  [AvgCost By RoleGeoYear] in the main Sample table by mapping it to the above summary table.
[AvgCost By RoleGeoYear] = [SumOfCostMonths]/[CountOfCostMonths]
We can use the below SubQuery method to perform the final update.

UPDATE [dbo].[ResourceCostingSample]
SET [AvgCost By RoleGeoYear] = S1.[SumOfCostMonths]/S1.[CountOfCostMonths]
From [dbo].[ResourceCostingSample] RC 
Inner Join 
(SELECT [RoleIdentifier], [CostGeo], [CostYear],
SUM(ISNULL(M1,0)+ISNULL(M2,0)+ISNULL(M3,0)+ISNULL(M4,0)+ISNULL(M5,0)+
ISNULL(M6,0)+ ISNULL(M7,0)+ISNULL(M8,0)+
ISNULL(M9,0)+ISNULL(M10,0)+
         ISNULL(M11,0)+ISNULL(M12,0)) AS SumOfCostMonths,
( CASE WHEN SUM(M1)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M2)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M3)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M4)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M5)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M6)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M7)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M8)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M9)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M10)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M11)>0 THEN 1 ELSE 0 END +
CASE WHEN SUM(M12)>0 THEN 1 ELSE 0 END ) AS CountOfCostMonths
From [dbo].[ResourceCostingSample]
Group By [RoleIdentifier], [CostGeo], [CostYear]
) S1
  ON RC.[RoleIdentifier]=S1.[RoleIdentifier] AND
RC.[CostGeo]=S1.[CostGeo] AND
RC.[CostYear]=S1.[CostYear]

Final Result :
Select * From [dbo].[ResourceCostingSample]

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to Find the Employees having same Salary By Department in SQL Server

SQL Sub Query with Count(*) Function, Over(Partition By..) Clause to Find the Employees having Same Salary By Department

Suppose we have the Employee and Department tables as follows..

Select * From [dbo].[Tbl_EmpDetails]

Select * From [dbo].[Tbl_Dept]

Now our objective here is to find and select the Employees who are drawing Same Salary.

We achieve that using the Count (*) Function with Over (Partition By..) Clause in Subquery as discussed below.

1) Employees Drawing the Same Salary :

We can find the Employees drawing the Same Salary by using the following Subquery on the Employee table as per below.

SELECT Emp_Id, EmpName, NetSal From
 (
 SELECT Emp_Id,EmpName, NetSal, Count(*) Over (Partition by NetSal) as SalaryCnt
 FROM [dbo].[Tbl_EmpDetails]
 ) S1
WHERE SalaryCnt>1

ORDER By NetSal Desc

Result :

2) Employees Drawing the Same Salary with Dept Details :
We can find the Employees drawing the Same Salary, with their Department Details using the following Subquery on the Employee and Department Tables as below.

SELECT S1.Emp_Id,S1.EmpName,S1.DeptName, S1.NetSal
FROM(Select E.*, D.DeptName,Count(*) Over (Partition by E.NetSal) as SalaryCnt
            From [dbo].[Tbl_EmpDetails] E
   Inner Join [dbo].[Tbl_Dept] D
   ON E.Dept_Id=D.DeptId
  ) S1
WHERE S1.SalaryCnt > 1
ORDER By S1.NetSal Desc

Result:

3) Employees Drawing the Same Salary By Department :
We can find the Employees drawing the Same Salary By Department using the following Subquery on the Employee and Department Tables as below.

SELECT S1.Emp_Id,S1.EmpName,S1.DeptName, S1.NetSal

From (Select E.*, D.DeptName,

                     Count(*) Over (Partition by D.DeptName,E.NetSal) as SalaryCnt

          From [dbo].[Tbl_EmpDetails] E
   Inner Join [dbo].[Tbl_Dept] D
   ON E.Dept_Id=D.DeptId
  ) S1
WHERE S1.SalaryCnt > 1
ORDER By S1.NetSal Desc

Result:

Note:

Since No two Employees in the IT Department is having the Same Salary so that that record has been excluded.

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

How to Categorize data and Find the Count for each Category in SQL Server

Categorize data and Find the Count for each Category in SQL Server

Suppose we have the two tables EMP, DEPT as below. Here our Objective is to Categorize the Employees as "IT" and "Non-IT" based on the DeptName, then we have to find Count of Employees in each group.

Select * From [dbo].[Tbl_Emp]

Select * From [dbo].[Tbl_Dept]

Now we have to Join the Emp table with Dept table , to get the DeptName.

Select E.*,D.DeptName From [dbo].[Tbl_Emp] E
Inner Join [dbo].[Tbl_Dept] D
On E.Dept_Id=D.DeptId

Order By DeptName

Now our Objective is to Categorize the Employees as "IT" and "Non-IT" based on the DeptName, the we have to find the Count of Employees in each group.

We can achieve this using the below sub-query.

Select ((Select Count (*) From [dbo].[Tbl_Emp])-S1.Cnt_NonIT) As Cnt_IT,S1.Cnt_NonIT
From (
   Select Count(Emp_Id) AS Cnt_NonIT From [dbo].[Tbl_Emp] E
   Inner Join [dbo].[Tbl_Dept] D
   On E.Dept_Id=D.DeptId and
   D.DeptName In ('BI&A','F&A')
  ) S1

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to use CROSS APPLY and OUTER APPLY Operators in SQL Server

The CROSS APPLY and OUTER APPLY Operators in SQL Server

The APPLY operator is similar to a Join clause and it allows to Join two table expressions like Joining a Left/Outer table expression with a Right/Inner table expression.

We mainly use the APPLY operator when we have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
The primary use of Apply operator in SQL Server is to invoke a table valued function (TVF) but it can also be used in some other important scenarios.

Apply Operator executes for each row of left expression and finds the matches in right expression and then returns the result set as per it’s type; Cross or Outer Apply.

SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY

CROSS APPLY :

The CROSS APPLY operator is similar to INNER JOIN which returns only those rows from the outer left table expression if it matches with the right table expression. In other words, the right table expression returns rows for left table expression based on match condition specified in the Where clause. 

The result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN.

Example :

Suppose we have the two Tables as follows, on which we apply APPLY operators.

Select * From [dbo].[Tbl_Emp]

Select * From [dbo].[Tbl_Dept]

Now will write a simple Query with Inner Join , and Cross Apply ; both will return the same result and the execution plans for these queries are similar and they have an equal query cost.

--Inner Join Query

Select E.*, D.DeptName
From [dbo].[Tbl_Emp] As E  
 Inner Join
 [dbo].[Tbl_Dept] As D 
 ON  E.Dept_Id=D.DeptId  

--Cross Apply Query

Select * From [dbo].[Tbl_Emp] As E  
CROSS  APPLY 
(Select D.DeptName  From [dbo].[Tbl_Dept] As D Where E.Dept_Id=D.DeptId) S1

Result :

OUTER APPLY :

The OUTER APPLY operator is similar to LEFT OUTER JOIN which returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression.

Example:

Now will write a simple Query with Left Join , and Outer Apply ; both will return the same result and the execution plans for these queries are similar and they have an equal query cost.

--Left Join Query

Select E.*, D.DeptName
From [dbo].[Tbl_Emp] As E  
 Left Join
 [dbo].[Tbl_Dept] As D 
 ON  E.Dept_Id=D.DeptId  

--Outer Apply Query

Select * From [dbo].[Tbl_Emp] As E  
OUTER  APPLY 

(Select D.DeptName  From [dbo].[Tbl_Dept] As D Where E.Dept_Id=D.DeptId) S1

Result :

APPLY with User Define function:
We can perform APPLY operation with a function that may be scalar or table valued function. This function will invoke each row and return result that will be associated with the outer table.
Now we will create a table-valued function which accepts Department ID as its parameter and returns all the Employee details who belong to this department. 

IF EXISTS (Select * From Sys.Objects 

Where Object_Id = Object_Id (N'[fn_GetEmpDtlsByDeptID]') AND Type IN (N'IF'))
Begin
   Drop Function dbo.fn_GetEmpDtlsByDeptID
End
GO

Notes :

There are several types of 'Object_Id' to be checked for functions.
FN = SQL scalar function
IF = SQL Inline Table-Valued Function
TF = SQL Table-Valued Function
FT = Assembly (CLR) Table-Valued Function

Create Function dbo.fn_GetEmpDtlsByDeptID(@DeptID AS INT)
Returns TABLE
AS
Return
     (
        Select D.DeptName From [dbo].[Tbl_Dept] As D Where D.DeptId= @DeptID
      )
GO

Now the following query selects data from the Employee table and uses a CROSS APPLY to Join with the Function we created above. It passes the DepartmentID for each row from the outer table expression (in our case Employee table) and evaluates the function for each row like a Correlated Subquery.

Select * From [dbo].[Tbl_Emp] E 
CROSS APPLY dbo.fn_GetEmpDtlsByDeptID(E.Dept_Id)
GO

Result:

Now the following query uses the OUTER APPLY in place of the CROSS APPLY and hence unlike the CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing values.

Select * From [dbo].[Tbl_Emp] E 
OUTER  APPLY dbo.fn_GetEmpDtlsByDeptID(E.Dept_Id)
GO

Result:

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

How to Update a Column based on Count of repeated Items in another Column in SQL Server

SQL Query to Update a Column based on Count of repeated Items in another Column
Scenario:
Suppose we have a table as follows
SELECT [Order_Id]
      ,[Geo_Id]
      ,[Cust_Name]
      ,[Sales_Region]
      ,[Deal_Id]
      ,[Net_Sales]
  FROM [dbo].[tbl_Sample]
GO

In this Table , we have to update the Column Geo_Id based on the following Conditions :
A). If we have only 2 Geo_Ids, one =-9999 and Other one <> -9999 ( Eg: -9999 , 1232 ) under the same Deal_Id ( Eg : 22222) Then we need to update the Geo_Id "-9999" with another Geo_Id , say 1232.
B). If we have only one Geo_Id , as -9999 under a Deal_Id ( Eg: 88888) then we should not perform any update.
C). If we have more than two Geo_Ids , one =-9999 and Others<> -9999  (Eg : -9999,1235 1236 ) under the same Deal_Id ( Eg : 77777) then we should not perform any update.

Now we can achieve this Scenario using the below Methods :

1) Sub Query Method :
Update [tbl_Sample]
Set  Geo_Id =S1.Geo_ID
From (
SELECT A.Order_Id,A.Geo_Id,A.Cust_Name,A.Sales_Region,B.Deal_Id,                                             A.Net_Sales,B.GeoCountByDeal
FROM [tbl_Sample] A
Inner Join (
SELECT COUNT(Distinct Geo_Id) as GeoCountByDeal, B.Deal_Id
         FROM [tbl_Sample] B
 GROUP BY B.Deal_Id
) AS B 
    ON A.Deal_Id = B.Deal_Id
   WHERE GeoCountByDeal=2 and A.Geo_Id<>'-9999' 
   -- ORDER by A.Deal_Id
) S1
Where ( [tbl_Sample].Deal_Id=S1.Deal_Id and [tbl_Sample].Geo_Id=-9999 and S1.GeoCountByDeal=2 )
Select * From [tbl_Sample]
GO

2) Common Table Expressions (CTEs) Method :
WITH MyCTE
AS
(
SELECT A.Order_Id,A.Geo_Id,A.Cust_Name,A.Sales_Region,B.Deal_Id,                                             A.Net_Sales,B.GeoCountByDeal
FROM [tbl_Sample] A
Inner Join (
SELECT COUNT(Distinct Geo_Id) as GeoCountByDeal, B.Deal_Id
         FROM [tbl_Sample] B
 GROUP BY B.Deal_Id
) AS B 
    ON A.Deal_Id = B.Deal_Id
   WHERE GeoCountByDeal=2 and A.Geo_Id<>'-9999' 
   -- ORDER by A.Deal_Id
) 
Update [tbl_Sample]
Set  [tbl_Sample].Geo_Id =MyCTE.Geo_ID
From MyCTE
Where ( [tbl_Sample].Deal_Id=MyCTE.Deal_Id and [tbl_Sample].Geo_Id=-9999 and MyCTE.GeoCountByDeal=2 )
Select * From [tbl_Sample]
;
GO

Output after Update:

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