Hi Lynn Pettis,

Following is the query I'm working on. The status field is summed up for '' in the query. But I need to have a separate column for each type of status. This query is already too large and I don't yet know how can I reduce the source code.

[font="Courier New"]

IF OBJECT_ID('DBO.Personnel','U') IS NOT NULL

DROP TABLE Personnel

IF OBJECT_ID('DBO.DYT','U') IS NOT NULL

DROP TABLE DYT

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)

);

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 * from dbo.Personnel;

select * from dbo.DYT;

SELECT DISTINCT

dbo.DYT.DYTM_HONO, dbo.Personnel.EMP_DESC, dbo.DYT.DYTM_SITE, UPPER(SUBSTRING(DATENAME(month, dbo.DYT.DYTM_DATE), 1, 3))

+ SUBSTRING(CONVERT(varchar(4), YEAR(dbo.DYT.DYTM_DATE)), 3, 2) AS Month,

-- summing up daily hours per per day per calendar month

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [1]

FROM dbo.DYT AS DYT1

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 1) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [1],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [2]

FROM dbo.DYT AS DYT2

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 2) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [2],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [3]

FROM dbo.DYT AS DYT3

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 3) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [3],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [4]

FROM dbo.DYT AS DYT4

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 4) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [4],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [5]

FROM dbo.DYT AS DYT5

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 5) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [5],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [6]

FROM dbo.DYT AS DYT6

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 6) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [6],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [7]

FROM dbo.DYT AS DYT7

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 7) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [7],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [8]

FROM dbo.DYT AS DYT8

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 8) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [8],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [9]

FROM dbo.DYT AS DYT9

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 9) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [9],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [10]

FROM dbo.DYT AS DYT10

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 10) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [10],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [11]

FROM dbo.DYT AS DYT11

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 11) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [11],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [12]

FROM dbo.DYT AS DYT12

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 12) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [12],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [13]

FROM dbo.DYT AS DYT13

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 13) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [13],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [14]

FROM dbo.DYT AS DYT14

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 14) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [14],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [15]

FROM dbo.DYT AS DYT15

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 15) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [15],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [16]

FROM dbo.DYT AS DYT16

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 16) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [16],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [17]

FROM dbo.DYT AS DYT17

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 17) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [17],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [18]

FROM dbo.DYT AS DYT18

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 18) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [18],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [19]

FROM dbo.DYT AS DYT19

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 19) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [19],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [20]

FROM dbo.DYT AS DYT20

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 20) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [20],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [21]

FROM dbo.DYT AS DYT21

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 21) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [21],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [22]

FROM dbo.DYT AS DYT22

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 22) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [22],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [23]

FROM dbo.DYT AS DYT23

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 23) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [23],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [24]

FROM dbo.DYT AS DYT24

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 24) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [24],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [25]

FROM dbo.DYT AS DYT25

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 25) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [25],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [26]

FROM dbo.DYT AS DYT26

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 26) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [26],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [27]

FROM dbo.DYT AS DYT27

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 27) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [27],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [28]

FROM dbo.DYT AS DYT28

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 28) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [28],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [29]

FROM dbo.DYT AS DYT29

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 29) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [29],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [30]

FROM dbo.DYT AS DYT30

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 30) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [30],

(SELECT ISNULL(SUM(DYTM_HOURS), 0) AS [31]

FROM dbo.DYT AS DYT31

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 31) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS [31],

-- summing up Absent, Sick, Non-approved Sick, Standby, Offshore-standby, Special Standby, Accident and Offshore-special standby days per calendar month

(SELECT ISNULL(COUNT(DYTM_STATUS), 0) AS ABS

FROM dbo.DYT AS DYTABS

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DATENAME(month, DYTM_DATE) = DATENAME(month, dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE)

= YEAR(dbo.DYT.DYTM_DATE)) AND (DYTM_STATUS IN ('A', 'S', 'Z', 'P', 'T', 'X', 'L', 'C', 'J'))) AS ABS,

-- parsing attendance status per day / per calendar month

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S1

FROM dbo.DYT AS DYT1

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 1) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S1,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S2

FROM dbo.DYT AS DYT2

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 2) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S2,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S3

FROM dbo.DYT AS DYT3

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 3) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S3,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S4

FROM dbo.DYT AS DYT4

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 4) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S4,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S5

FROM dbo.DYT AS DYT5

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 5) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S5,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S6

FROM dbo.DYT AS DYT6

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 6) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S6,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S7

FROM dbo.DYT AS DYT7

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 7) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S7,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S8

FROM dbo.DYT AS DYT8

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 8) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S8,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S9

FROM dbo.DYT AS DYT9

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 9) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S9,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S10

FROM dbo.DYT AS DYT10

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 10) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S10,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S11

FROM dbo.DYT AS DYT11

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 11) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S11,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S12

FROM dbo.DYT AS DYT12

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 12) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S12,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S13

FROM dbo.DYT AS DYT13

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 13) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S13,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S14

FROM dbo.DYT AS DYT14

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 14) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S14,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S15

FROM dbo.DYT AS DYT15

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 15) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S15,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S16

FROM dbo.DYT AS DYT16

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 16) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S16,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S17

FROM dbo.DYT AS DYT17

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 17) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S17,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S18

FROM dbo.DYT AS DYT18

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 18) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S18,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S19

FROM dbo.DYT AS DYT19

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 19) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S19,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S20

FROM dbo.DYT AS DYT20

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 20) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S20,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S21

FROM dbo.DYT AS DYT21

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 21) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S21,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S22

FROM dbo.DYT AS DYT22

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 22) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S22,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S23

FROM dbo.DYT AS DYT23

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 23) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S23,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S24

FROM dbo.DYT AS DYT24

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 24) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S24,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S25

FROM dbo.DYT AS DYT25

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 25) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S25,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S26

FROM dbo.DYT AS DYT26

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 26) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S26,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S27

FROM dbo.DYT AS DYT27

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 27) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S27,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S28

FROM dbo.DYT AS DYT28

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 28) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S28,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S29

FROM dbo.DYT AS DYT29

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 29) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S29,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S30

FROM dbo.DYT AS DYT30

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 30) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S30,

(SELECT ISNULL(MAX(DYTM_STATUS), '') AS S31

FROM dbo.DYT AS DYT31

WHERE (DYTM_HONO = dbo.DYT.DYTM_HONO) AND (DAY(DYTM_DATE) = 31) AND (DATENAME(month, DYTM_DATE) = DATENAME(month,

dbo.DYT.DYTM_DATE)) AND (YEAR(DYTM_DATE) = YEAR(dbo.DYT.DYTM_DATE))) AS S31

FROM dbo.DYT INNER JOIN

dbo.Personnel ON dbo.DYT.DYTM_HONO = dbo.Personnel.EMP_CODE

GROUP BY dbo.DYT.DYTM_HONO, dbo.DYT.DYTM_DATE, dbo.Personnel.EMP_DESC,dbo.DYT.DYTM_SITE

go

drop table dbo.Personnel;

drop table dbo.DYT;

go

[/font]

Thanks and best regards.