This should be efficient as it reads the table only once.

WITH ClockCounts AS(
  SELECT COUNT(*)   AS ClockIn ,
    COUNT(EndTime) AS ClockOut
  FROM...

Even simpler. You're basically trying to query the current month for the day before.

DECLARE @StartDate date = DATEADD(mm, DATEDIFF( mm, 0, GETDATE() - 1), 0);
DECLARE...

Wild idea:
Why don't you simply update the table instead of deleting and re-inserting?

Actually, ROUND uses 3 parameters. The third parameter indicates if it needs to truncate or round.
There's a difference on how FLOOR and ROUND work with negatives.
I explain...

You also want to use  QUOTENAME() to handle complex names for your columns or objects.

DECLARE @Field_suffix char(1);
DECLARE @sql varchar(MAX);

SET @Field_suffix = '1';

Try this:

DECLARE @sql nvarchar(max)
SELECT @sql = N'SELECT H.UserID' + CHAR(10)
  + STUFF(( SELECT CHAR(10) + CHAR(9) + ',ISNULL( NULLIF( H.' + QUOTENAME(COLUMN_NAME) + ',...

Could you be missing the ROWS clause in the OVER()?

DECLARE @MaxRecords int = 500;

WITH MyLimits
AS
(
SELECT SUM(COUNT(colz.OBJECT_ID)) OVER( ORDER...

What about using an indexed computed column and simply query without spaces? Obviously that takes more storage space, but it might work.

EDIT: Seems like Anthony had the same...