How to apply max function in a SQL query joining three tables-CodePudding

I am new to SQL Server, I have 3 tables consider an employee table

empid	name	location
1	abc	USA
2	efg	UK

Another table named location-table

location	holidaycode
uk	uk1
usa	usa1

And also holidaytable:

holiday-code	date	type
uk1	2022-01-01	LM
uk1	2022-01-01	RMC

Expected result is:

empid	location	holidaycode	date	type 1
2	uk	uk1	2022-01-01	RMC

suppose I query the tables based on empid and date say ,the holidays available for empid 2 from a particular from date such that on a particular day (2022-01-01) I have LM and RMC on that day , I need only RMC ,if RMC is not available else LMC.

CodePudding user response：

Please try this

select e.empid, e.location, h.holidaycode, [date], max([type])
from employee e inner join location_table l 
                           on e.location=l.location
                inner join holidaytable h 
                           on l.holidaycode=h.holidaycode
where e.empid=2 and h.[date]='2022-01-01'
group by e.empid, e.location, h.holidaycode, [date]

Example:

Schema and insert statements:

   create table employee(empid int, name varchar(50), location varchar(50));
   insert into employee values(1,   'abc',  'USA');
   insert into employee values(2,   'efg',  'UK');
   
   create table location_table(location varchar(50),holidaycode varchar(50));
   insert into location_table values('uk','uk1');
   insert into location_table values('usa','usa1');
   
   create table holidaytable(holidaycode    varchar(50), [date] date, [type] varchar(50));
   insert into holidaytable values('uk1',   '2022-01-01',   'LM');
   insert into holidaytable values('uk1',   '2022-01-01',   'RMC');

Query:

 select e.empid, e.location, h.holidaycode, [date], max([type]) [type 1]
   from employee e inner join location_table l 
                              on e.location=l.location
                   inner join holidaytable h 
                              on l.holidaycode=h.holidaycode
   where e.empid=2 and h.[date]='2022-01-01'
   group by e.empid, e.location, h.holidaycode, [date]

Output:

empid	location	holidaycode	date	type 1
2	UK	uk1	2022-01-01	RMC

db<>fiddle here

CodePudding user response：

You can use ROW_NUMBER to get only the first row of each grouping.

Either do ORDER BY type DESC or use ORDER BY CASE WHEN type = 'RMC' THEN 1 ELSE 2 END

SELECT
  e.empid,
  e.location,
  l.holidaycode,
  h.date,
  h.type
FROM employee e
JOIN location l ON l.location = e.location
JOIN (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY h.holidaycode, h.date ORDER BY h.type DESC)
    FROM holiday h
) h ON h.holidaycode = l.holidaycode AND h.rn = 1
WHERE h.date = '2022-01-01';

db<>fiddle