I need help in an sql query

Question

I have a table where the attendance of the employees is recorded. We only insert the present days. I want to show all dates of the month along with recorded attendance days. where not recorded days will show absent. Need Help. Here is the Table Employee Attendance. Please Note that We only insert Present days into this table. Absent and Holidays are not inserted into this table.

`CREATE TABLE [dbo].[EmployeeAttendance](
[CompanyCode] [int] NOT NULL,
[BranchCode] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[EmployeeCode] [int] NOT NULL,
[AttendanceCount] [int] NOT NULL,
[ShiftCode] [int] NOT NULL,
[ScheduledTimeIn] [datetime] NULL,
[ScheduledTimeOut] [datetime] NULL,
[BreakStartTime] [datetime] NULL,
[BreakEndTime] [datetime] NULL,
[FlexiLateTime] [int] NULL,
[FlexiEarlyTime] [int] NULL,
[RecordedTimeIn] [datetime] NULL,
[RecordedTimeOut] [datetime] NULL,
[RemarksIn] [varchar](500) NULL,
[RemarksOut] [varchar](500) NULL,
[AddByUserId] [int] NULL,
[AddDateTimeIn] [datetime] NULL,
[AddDateTimeOut] [datetime] NULL,
CONSTRAINT [PK_EmployeeAttendance_1] PRIMARY KEY CLUSTERED 
(
[CompanyCode] ASC,
[BranchCode] ASC,
[TransactionDate] ASC,
[EmployeeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]`

Answer 1

First: You need to create a "table" that has all the days of the month in it. Then you left join your recorded attendance data to that range of dates.

SELECT d.date, a.*
FROM [dates] AS d -- this is a set of rows, one per needed date 
LEFT JOIN EmployeeAttendance AS a ON d.[date] = a.[RecordedTimeIn]

Now, depending on the database being used, you can generate the needed date rows in a variety of ways (eg a recursive common table expression).

Regardless of method you need one row per date is your first step and this will be the "from" table.