CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How check if specific record exist in table or not

I have a table in which I have multiple dates stored which are the attendance dates of employee 

CREATE TABLE Attendance
(
EmpCode INT,
AttendanceDate DATETIME
)

INSERT INTO Attendance VALUES (24, '2018-12-01');
INSERT INTO Attendance VALUES (24, '2018-12-02');
INSERT INTO Attendance VALUES (24, '2018-12-03');
INSERT INTO Attendance VALUES (24, '2018-12-04');
INSERT INTO Attendance VALUES (24, '2018-12-06');

Now as there is not date saved for this employee on 5th Dec it should show Absent in that case.

SELECT * FROM Attendance

Upvotes: 0

Views: 68

Answers (1)

Chanukya
Chanukya

Reputation: 5873

It will give missed date for that emp from table if you want to insert you can insert by using existing code 

CREATE TABLE #Attendance
(
EmpCode INT,
AttendanceDate DATETIME
)

INSERT INTO #Attendance VALUES (24, '2018-12-01');
INSERT INTO #Attendance VALUES (24, '2018-12-02');
INSERT INTO #Attendance VALUES (24, '2018-12-03');
INSERT INTO #Attendance VALUES (24, '2018-12-04');
INSERT INTO #Attendance VALUES (24, '2018-12-06');

select * from #Attendance

 ;WITH CTE AS
    (
    SELECT CONVERT(DATE,'2018-12-01') AS DATE1
    UNION ALL
    SELECT DATEADD(DD,1,DATE1) FROM CTE WHERE DATE1<'2018-12-06'
    )
    --insert into #Attendance(EmpCode,AttendanceDate)
    SELECT DATE1 MISSING_ONE,'a' FROM CTE
    EXCEPT 
    SELECT AttendanceDate,'a' FROM #Attendance
    option(maxrecursion 0)

Upvotes: 1

Related Questions