Display all records whether DateTime exit or not from two table using JOIN in SQL Server

Last Reply 28 days ago By pandeyism

Posted 28 days ago

i want to display all employee whether their Datetime (A_Date) exit or not.

here is my data.

create table #EmpMaster(EnrollNumber int,EmpName varchar(max))
Create table #AttendenceLOG(ID int,EnrollNumber int,A_Date datetime)

insert into #EmpMaster values 
(1001, 'Andrea'),
(1002, 'pandeyism'),
(1003, 'dharmendr'),
(1004, 'Mudassar'),
(1005, 'Akhter'),
(1006, 'Rehan'),
(1007, 'Irfan')

insert into #AttendenceLOG values 
(1,1001, '2019-05-28 17:18'),
(2,1002, '2018-05-28 17:18'),
(3,1003, '2018-05-28 17:18'),
(4,1006, '2018-05-28 17:18')

expected output is

 EnrollNumber  Name   A_Date
1001     Kashif  2019-05-28 17:18
1002  Kami  2018-05-28 17:18
1003  Ali  2018-05-28 17:18
1004  Farhan  
1005  Akhter  
1006  Irfan  2018-05-28 17:18
You are viewing reply posted by: pandeyism 28 days ago.
Posted 28 days ago Modified on 28 days ago

Hi akhter,

Refer below sample.

SQL

CREATE TABLE #EmpMaster(EnrollNumber int,EmpName varchar(max))
CREATE TABLE #AttendenceLOG(ID int,EnrollNumber int,A_Date datetime)
INSERT INTO #EmpMaster values(1001, 'Andrea')
INSERT INTO #EmpMaster values(1002, 'pandeyism')
INSERT INTO #EmpMaster values(1003, 'dharmendr')
INSERT INTO #EmpMaster values(1004, 'Mudassar')
INSERT INTO #EmpMaster values(1005, 'Akhter')
INSERT INTO #EmpMaster values(1006, 'Rehan')
INSERT INTO #EmpMaster values(1007, 'Irfan')
INSERT INTO #AttendenceLOG values(1,1001, '2019-05-28 17:18')
INSERT INTO #AttendenceLOG values(2,1002, '2018-05-28 17:18')
INSERT INTO #AttendenceLOG values(3,1003, '2018-05-28 17:18')
INSERT INTO #AttendenceLOG values(4,1006, '2018-05-28 17:18')
SELECT em.EnrollNumber,em.EmpName,ISNULL(Convert(VARCHAR,alg.A_Date,4),'') Date FROM #EmpMaster em
LEFT JOIN #AttendenceLOG alg ON alg.EnrollNumber = em.EnrollNumber
WHERE alg.A_Date BETWEEN @StartDate AND @EndDate OR alg.A_Date IS NULL
DROP TABLE #EmpMaster
DROP TABLE #AttendenceLOG

Output

EnrollNumber EmpName Date
1001 Andrea 28.05.19
1002 pandeyism 28.05.18
1003 dharmendr 28.05.18
1004 Mudassar  
1005 Akhter  
1006 Rehan 28.05.18
1007 Irfan