Question Details

No question body available.

Tags

sql sql-server sql-server-2022

Answers (3)

Accepted Answer Available
Accepted Answer
December 9, 2025 Score: 2 Rep: 87,917 Quality: High Completeness: 80%

I'd use a window function to check if a MapID has an employee, then use the result to filter to just employee rows or non-employee rows.

Finally, it's just a pivot to move the two regions into primary and secondary.

CREATE TABLE Region
(
    MapID varchar(10),
    EmployeeID varchar(10),
    Zip5 varchar(10),
    Business Varchar(50),
    Region varchar(50)
);
INSERT INTO Region
    SELECT '9879', '100', Null,'B1', 'South'
    UNION
    SELECT '9879', '100', Null,'B2', 'South-1'
    UNION
    SELECT '9879', Null, '04987','B2', 'South-1'
    UNION
    SELECT '7890', '200', Null, 'B2','West S San Diago'
    UNION
    SELECT '7890', NULL, '45678','B1', 'West So. CA'
    UNION
    SELECT '7890', NULL, '45678', 'B2','West-1'
    UNION
    SELECT '5678', '400', Null,'B1', 'EastCentral'
    UNION
    SELECT '5678', '400', Null,'B2', 'Central-1'
    UNION
    SELECT '5678', Null, '56333','B1', 'EastCentral-1'
    UNION
    SELECT '5678', Null, '56333','B2', 'East-1'
;
WITH
  lookaround AS
(
  SELECT
    *,
    MAX(EmployeeID) OVER (PARTITION BY MapID)   AS mapemployee
  FROM
    Region
)
SELECT
  MapID,
  MAX(CASE WHEN Business = 'B2' THEN Region END) AS RegionPrimary,
  MAX(CASE WHEN Business = 'B1' THEN Region END) AS RegionSecondary
FROM
  lookaround
WHERE
  EmployeeID = mapemployee
  OR
  (
    map_employee IS NULL
    AND
    EmployeeID   IS NULL
  )
GROUP BY
  MapID
MapID RegionPrimary RegionSecondary
5678 Central-1 EastCentral
7890 West S San Diago null
9879 South-1 South

fiddle

It's not clear from your post when Business would ever be B1, so I haven't included that.

December 10, 2025 Score: 0 Rep: 80,583 Quality: Medium Completeness: 100%

This doesn't need any window functions at all, you can do it purely with normal aggregation functions.

SELECT
  r.MapID,
  MIN(CASE WHEN r.Business = 'B2' THEN r.Business END) AS Business,
  CASE WHEN COUNT(r.EmployeeID) > 0
    THEN
      MIN(CASE WHEN r.EmployeeID IS NOT NULL AND r.Business = 'B2' THEN r.Region END)
    ELSE
      MIN(CASE WHEN r.Zip5 IS NOT NULL AND r.Business = 'B2' THEN r.Region END)
    END AS Region,
  CASE WHEN COUNT(r.EmployeeID) > 0
    THEN
      MIN(CASE WHEN r.EmployeeID IS NOT NULL AND r.Business = 'B1' THEN r.Region END)
    ELSE
      MIN(CASE WHEN r.Zip5 IS NOT NULL AND r.Business = 'B1' THEN r.Region END)
    END AS SecondaryRegion
FROM #Region r
GROUP BY
  r.MapID;

What we are doing here is:

  • for each MapID, we are checking whether there are any EmployeeID (that's the COUNT).
  • if yes then use conditional aggregation to look for the row where EmployeeID is non-null and Business is B1 or B2 resp., then return the Region for that row.
  • if not then do the same aggregation but look for a non-null Zip5 row instead.
  • As mentioned by others, it's not clear when Business should ever not be B2.

dbfiddle

December 9, 2025 Score: 0 Rep: 8,669 Quality: Low Completeness: 80%

If you perform this task through multiple JOINS and then select a single row using DISTINCT, the conditions can be quite complicated. Also, in this case, it remains unclear what to do if there are no rows satisfying your task.

I suggest considering an approach where we first select the "base" rows and also calculate the SecondaryRegion by a subquery.

"Base" row is row where "Business='B2'" and "EmployeeId is not null".
If no row with "EmployeeId is not null", then we can take any row with "Business='B2'".
This rows are rn=1 for

  rownumber()over(partition by mapid order by employeeid desc) rn

In SQL Server windows function while sorting "desc" nulls is last.
So, row with rn=1 is first row with EmployeeId is not null or (else) first (any) row with EmployeeId is null.

Also, calculate "SecondaryRegionEmp" where EmployeeId is not null as

     (select region from Region t2 
      where t2.mapid=t.mapid and t2.business='b1' and t2.EmployeeId=t.EmployeeId
     ) secondaryRegionEmp

When calculated "SecondaryRegionEmp" is null (not found) we use JOIN and get SecondaryRegion.

With test data

mapid EmployeeID zip5 Business Region
9879 100 null B1 South
9879 100 null B2 South-1
9879 null 04987 B2 South-1
7890 200 null B2 West S San Diago
7890 null 45678 B1 West So. CA
7890 null 45678 B2 West-1
5678 400 null B1 EastCentral
5678 400 null B2 Central-1
5678 null 56333 B1 EastCentral-1
5678 null 56333 B2 East-1
9991 null 11111 B2 TestReg-1
9991 null 11111 B1 TestReg-2 
select t.mapid,t.EmployeeID,t.Region
  ,coalesce(t.SecondaryRegionEmp,zip.Region) SecondaryRegion
from(
  select *
    ,rownumber()over(partition by mapid order by employeeid desc) rn
    ,(select region from Region t2 
      where t2.mapid=t.mapid and t2.business='b1' and t2.EmployeeId=t.EmployeeId
     ) secondaryRegionEmp
  from Region t
  where t.business='B2'
)t
left join Region zip on zip.mapid=t.mapid and zip.zip5=t.zip5 and zip.business='B1'
where rn=1
mapid EmployeeID Region SecondaryRegion
5678 400 Central-1 EastCentral
7890 200 West S San Diago null
9879 100 South-1 South
9991 null TestReg-1 TestReg-2

For clarity, subquery result

  select *
    ,row_number()over(partition by mapid order by employeeid desc) rn
    ,(select region from Region t2 
      where t2.mapid=t.mapid and t2.business='b1' and t2.EmployeeId=t.EmployeeId
     ) secondaryRegionEmp
  from Region t
  where t.business='B2'
mapid EmployeeID zip5 Business Region rn secondaryRegionEmp
5678 400 null B2 Central-1 1 EastCentral
5678 null 56333 B2 East-1 2 null
7890 200 null B2 West S San Diago 1 null
7890 null 45678 B2 West-1 2 null
9879 100 null B2 South-1 1 South
9879 null 04987 B2 South-1 2 null
9991 null 11111 B2 TestReg-1 1 null

fiddle