Here is my rewrite. For a quick test and comparison create two empty sandbox databases. Then in one sand box run your code and in the other run my code doing so in steps First run the data setup code (create tables and insert into scripts) then run the main code returning an actual execution plan. Compare the two plans.
IF OBJECT_ID('dbo.Personnel','U') IS NOT NULL
DROP TABLE dbo.Personnel
IF OBJECT_ID('dbo.DYT','U') IS NOT NULL
DROP TABLE dbo.DYT
GO
create table dbo.Personnel(
EMP_CODE VARCHAR(5) PRIMARY KEY, -- Employee code
EMP_DESC VARCHAR(50) -- Employee Name
);
create table dbo.DYT(
DYTM_HONO VARCHAR(5), -- Employee code
DYTM_DATE DATE,
DYTM_HOURS NUMERIC(4,1),
DYTM_STATUS CHAR(1),
DYTM_SITE VARCHAR(3)
);
-- Create a clustered index on the table dbo.DYT
create clustered index IDX1 on dbo.DYT(
DYTM_HONO,
DYTM_DATE
);
GO
insert into dbo.Personnel
values
('00010','JOSEPH'),
('02535','PETER'),
('50500','MATHEW');
insert into dbo.DYT
values
('00010','2014-08-01',8,'N','ADQ'),
('00010','2014-08-02',8,'N','ADQ'),
('02535','2014-08-01',9,'N','ADQ'),
('02535','2014-08-02',8,'N','ADQ'),
('02535','2014-08-03',10,'N','ADQ'),
('50500','2014-08-01',0,'A','ADQ'),
('50500','2014-08-03',8,'N','ADQ'),
('00010','2014-08-04',8,'N','ADQ'),
('00010','2014-08-05',8,'N','ADQ'),
('00010','2014-08-06',8,'N','ADQ'),
('02535','2014-08-04',9,'N','ADQ'),
('02535','2014-08-05',8,'N','ADQ'),
('02535','2014-08-06',10,'N','ADQ'),
('50500','2014-08-04',8,'N','ADQ'),
('50500','2014-08-05',8,'N','ADQ'),
('50500','2014-08-06',8,'N','ADQ'),
('00010','2014-08-07',8,'N','ADQ'),
('00010','2014-08-08',8,'N','ADQ'),
('00010','2014-08-09',8,'N','ADQ'),
('02535','2014-08-07',9,'N','ADQ'),
('02535','2014-08-08',8,'N','ADQ'),
('02535','2014-08-09',12,'N','ADQ'),
('50500','2014-08-07',8,'N','ADQ'),
('50500','2014-08-08',8,'N','ADQ'),
('50500','2014-08-09',8,'N','ADQ'),
('00010','2014-08-10',8,'N','ADQ'),
('00010','2014-08-11',8,'N','ADQ'),
('00010','2014-08-12',8,'N','ADQ'),
('02535','2014-08-10',9,'N','ADQ'),
('02535','2014-08-11',8,'N','ADQ'),
('02535','2014-08-12',10,'N','ADQ'),
('50500','2014-08-10',8,'N','ADQ'),
('50500','2014-08-11',8,'N','ADQ'),
('50500','2014-08-12',8,'N','ADQ'),
('00010','2014-08-13',8,'N','ADQ'),
('00010','2014-08-14',8,'N','ADQ'),
('00010','2014-08-15',8,'N','ADQ'),
('02535','2014-08-13',9,'N','ADQ'),
('02535','2014-08-14',8,'N','ADQ'),
('02535','2014-08-15',12,'N','ADQ'),
('50500','2014-08-13',8,'N','ADQ'),
('50500','2014-08-14',8,'N','ADQ'),
('50500','2014-08-15',8,'N','ADQ'),
('00010','2014-08-16',8,'N','ADQ'),
('00010','2014-08-17',8,'N','ADQ'),
('00010','2014-08-18',8,'N','ADQ'),
('02535','2014-08-16',9,'N','ADQ'),
('02535','2014-08-17',8,'N','ADQ'),
('02535','2014-08-18',10,'N','ADQ'),
('50500','2014-08-16',8,'N','ADQ'),
('50500','2014-08-17',8,'N','ADQ'),
('50500','2014-08-18',8,'N','ADQ'),
('00010','2014-08-19',8,'N','ADQ'),
('00010','2014-08-20',8,'N','ADQ'),
('00010','2014-08-21',8,'N','ADQ'),
('02535','2014-08-19',9,'N','ADQ'),
('02535','2014-08-20',8,'N','ADQ'),
('02535','2014-08-21',12,'N','ADQ'),
('50500','2014-08-19',8,'N','ADQ'),
('50500','2014-08-20',8,'N','ADQ'),
('50500','2014-08-21',8,'N','ADQ'),
('00010','2014-08-22',8,'N','ADQ'),
('00010','2014-08-23',8,'N','ADQ'),
('00010','2014-08-24',8,'N','ADQ'),
('02535','2014-08-22',9,'N','ADQ'),
('02535','2014-08-23',8,'N','ADQ'),
('02535','2014-08-24',10,'N','ADQ'),
('50500','2014-08-22',8,'N','ADQ'),
('50500','2014-08-23',8,'N','ADQ'),
('50500','2014-08-24',8,'N','ADQ'),
('00010','2014-08-25',8,'N','ADQ'),
('00010','2014-08-26',8,'N','ADQ'),
('00010','2014-08-27',8,'N','ADQ'),
('02535','2014-08-25',9,'N','ADQ'),
('02535','2014-08-26',8,'N','ADQ'),
('02535','2014-08-27',12,'N','ADQ'),
('50500','2014-08-25',8,'N','ADQ'),
('50500','2014-08-26',8,'N','ADQ'),
('50500','2014-08-27',8,'N','ADQ'),
('00010','2014-08-28',8,'X','ADQ'),
('00010','2014-08-29',0,'P','ADQ'),
('00010','2014-08-30',8,'N','ADQ'),
('02535','2014-08-28',9,'N','ADQ'),
('02535','2014-08-29',0,'Z','ADQ'),
('02535','2014-08-30',12,'N','ADQ'),
('50500','2014-08-28',8,'N','ADQ'),
('50500','2014-08-29',8,'N','ADQ'),
('50500','2014-08-30',8,'S','ADQ');
GO
SELECT
dyt.DYTM_HONO,
p.EMP_DESC,
dyt.DYTM_SITE,
cast(dateadd(month,datediff(month,0,dyt.DYTM_DATE),0) as date) CalendarMonth,
--UPPER(SUBSTRING(DATENAME(month, dyt.DYTM_DATE), 1, 3)) + SUBSTRING(CONVERT(VARCHAR(4), YEAR(dyt.DYTM_DATE)), 3, 2) AS Month,
-- summing up daily hours per per day per calendar month
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 1 THEN dyt.DYTM_HOURS ELSE 0
END) as [1],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 2 THEN dyt.DYTM_HOURS ELSE 0
END) as [2],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 3 THEN dyt.DYTM_HOURS ELSE 0
END) as [3],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 4 THEN dyt.DYTM_HOURS ELSE 0
END) as [4],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 5 THEN dyt.DYTM_HOURS ELSE 0
END) as [5],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 6 THEN dyt.DYTM_HOURS ELSE 0
END) as [6],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 7 THEN dyt.DYTM_HOURS ELSE 0
END) as [7],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 8 THEN dyt.DYTM_HOURS ELSE 0
END) as [8],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 9 THEN dyt.DYTM_HOURS ELSE 0
END) as [9],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 10 THEN dyt.DYTM_HOURS ELSE 0
END) as [10],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 11 THEN dyt.DYTM_HOURS ELSE 0
END) as [11],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 12 THEN dyt.DYTM_HOURS ELSE 0
END) as [12],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 13 THEN dyt.DYTM_HOURS ELSE 0
END) as [13],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 14 THEN dyt.DYTM_HOURS ELSE 0
END) as [14],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 15 THEN dyt.DYTM_HOURS ELSE 0
END) as [15],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 16 THEN dyt.DYTM_HOURS ELSE 0
END) as [16],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 17 THEN dyt.DYTM_HOURS ELSE 0
END) as [17],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 18 THEN dyt.DYTM_HOURS ELSE 0
END) as [18],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 19 THEN dyt.DYTM_HOURS ELSE 0
END) as [19],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 20 THEN dyt.DYTM_HOURS ELSE 0
END) as [20],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 21 THEN dyt.DYTM_HOURS ELSE 0
END) as [21],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 22 THEN dyt.DYTM_HOURS ELSE 0
END) as [22],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 23 THEN dyt.DYTM_HOURS ELSE 0
END) as [23],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 24 THEN dyt.DYTM_HOURS ELSE 0
END) as [24],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 25 THEN dyt.DYTM_HOURS ELSE 0
END) as [25],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 26 THEN dyt.DYTM_HOURS ELSE 0
END) as [26],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 27 THEN dyt.DYTM_HOURS ELSE 0
END) as [27],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 28 THEN dyt.DYTM_HOURS ELSE 0
END) as [28],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 29 THEN dyt.DYTM_HOURS ELSE 0
END) as [29],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 30 THEN dyt.DYTM_HOURS ELSE 0
END) as [30],
SUM(CASE DAY(dyt.DYTM_DATE)
WHEN 31 THEN dyt.DYTM_HOURS ELSE 0
END) as [31],
-- summing up Absent, Sick, Non-approved Sick, Standby, Offshore-standby, Special Standby, Accident and Offshore-special standby days per calendar month
SUM(CASE WHEN dyt.DYTM_STATUS IN ('A','S','Z','P','T','X','L','C','J') THEN 1 ELSE 0 END) AS ABS,
-- parsing attendance status per day / per calendar month
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 1 THEN dyt.DYTM_STATUS ELSE '' END
) AS S1,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 2 THEN dyt.DYTM_STATUS ELSE '' END
) AS S2,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 3 THEN dyt.DYTM_STATUS ELSE '' END
) AS S3,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 4 THEN dyt.DYTM_STATUS ELSE '' END
) AS S4,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 5 THEN dyt.DYTM_STATUS ELSE '' END
) AS S5,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 6 THEN dyt.DYTM_STATUS ELSE '' END
) AS S6,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 7 THEN dyt.DYTM_STATUS ELSE '' END
) AS S7,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 8 THEN dyt.DYTM_STATUS ELSE '' END
) AS S8,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 9 THEN dyt.DYTM_STATUS ELSE '' END
) AS S9,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 10 THEN dyt.DYTM_STATUS ELSE '' END
) AS S10,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 11 THEN dyt.DYTM_STATUS ELSE '' END
) AS S11,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 12 THEN dyt.DYTM_STATUS ELSE '' END
) AS S12,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 13 THEN dyt.DYTM_STATUS ELSE '' END
) AS S13,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 14 THEN dyt.DYTM_STATUS ELSE '' END
) AS S14,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 15 THEN dyt.DYTM_STATUS ELSE '' END
) AS S15,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 16 THEN dyt.DYTM_STATUS ELSE '' END
) AS S16,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 17 THEN dyt.DYTM_STATUS ELSE '' END
) AS S17,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 18 THEN dyt.DYTM_STATUS ELSE '' END
) AS S18,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 19 THEN dyt.DYTM_STATUS ELSE '' END
) AS S19,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 20 THEN dyt.DYTM_STATUS ELSE '' END
) AS S20,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 21 THEN dyt.DYTM_STATUS ELSE '' END
) AS S21,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 22 THEN dyt.DYTM_STATUS ELSE '' END
) AS S22,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 23 THEN dyt.DYTM_STATUS ELSE '' END
) AS S23,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 24 THEN dyt.DYTM_STATUS ELSE '' END
) AS S24,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 25 THEN dyt.DYTM_STATUS ELSE '' END
) AS S25,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 26 THEN dyt.DYTM_STATUS ELSE '' END
) AS S26,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 27 THEN dyt.DYTM_STATUS ELSE '' END
) AS S27,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 28 THEN dyt.DYTM_STATUS ELSE '' END
) AS S28,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 29 THEN dyt.DYTM_STATUS ELSE '' END
) AS S29,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 30 THEN dyt.DYTM_STATUS ELSE '' END
) AS S30,
MAX(CASE DAY(dyt.DYTM_DATE)
WHEN 31 THEN dyt.DYTM_STATUS ELSE '' END
) AS S31
FROM
dbo.DYT dyt
INNER JOIN dbo.Personnel p
ON dyt.DYTM_HONO = p.EMP_CODE
GROUP BY
dyt.DYTM_HONO,
p.EMP_DESC,
dyt.DYTM_SITE,
cast(dateadd(month,datediff(month,0,dyt.DYTM_DATE),0) as date);
go
drop table dbo.Personnel;
drop table dbo.DYT;
GO
Also, using SQL Sentry Plan Explorer to do some comparisons, your code against the sample data ran in 211 ms while mine ran in 6 ms.