SQL Server Compare two tables and show data with missing value

ffscu2ro 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(145)

I have two tables Master and Log as shown below. And require an output along with the a new entry of emp who is missing from log table for particular workweek and year when compared with master. I tried with multiple join, left, left outer, but not able to figure out to match and get workweek for emp missing data from log table. Any help or example will be helpful

Master table              Log table

empid|Name                empid|name|workweek|year|attendance
-----+-----               -----+----+--------+----+-----------
 1   | XYZ                   1 | XYZ|  1     |2023|Present
 2   | abz                   2 | abz|  1     |2023|Present
 3   | yzz                   2 | abz|  2     |2023|Present
                             3 | yzz|  2     |2023|Present

Desired output:

empid|name|workweek|year|attendance
 -----+----+--------+----+-------------
   1  | XYZ|  1     |2023|Present
   2  | abz|  1     |2023|Present
   3  | yzz|  1     |2023|Absent
   2  | abz|  2     |2023|Present
   3  | yzz|  2     |2023|Present
   1  | XYZ|  2     |2023|Absent

This is my query:

select *
from log l
left join Master M on l.empid = m.empid

sql-server

来源：https://stackoverflow.com/questions/75576401/compare-two-tables-and-show-data-with-missing-value

1条答案

按热度按时间

ru9i0ody1#

based on the expected output, you are looking to see all entries in master table by each workweek in 2023 to be in the final output, indicating if the employee was present or absent.

Here is a way to do this

with master_data
  as (select empid,name,workweek,year
        from (select distinct workweek,year from log) a
        join master b
          on 1=1 --This does a cartersian join
      )
  select md.empid,md.name,md.workweek,md.year
         ,case when l.attendence is null then
                   'Absent'
           else 'Present'
          end as Attendence
    from master_data md
left join log l
       on md.empid=l.empid
      and md.workweek=l.workweek
      and md.year=l.year

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server Compare two tables and show data with missing value

1条答案

相关问题

热门标签

最新问答