How to replace null value in a query

  • Obviously you did not read the article you were asked to read. The table definitions and sample data posted are unusable unless one takes the time to write it themselves. The following is what you should have posted:

    create table dbo.Personnel(

    EMP_CODE VARCHAR(5) PRIMARY KEY,

    EMP_NAME VARCHAR(50) -- Really shouldn't use NAME as a column name

    );

    create table dbo.TS(

    EMP_CODE VARCHAR(5), -- can't call this single column a PRIMARY KEY as it isn't unique to this table.

    CARD_DATE DATE,

    TS_HOURS NUMERIC, -- Really, care to provide the rest of the definition to include the precision and significant digits NUMERIC(10,2) for example?

    TS_STATUS CHAR(1) -- Again, really? Using 3 characters to store 1, use a CHAR(1) instead

    );

    insert into dbo.Personnel

    values ('00010','JOSEPH'),

    ('02535','PETER'),

    ('50500','MATHEW');

    insert into dbo.TS

    values ('00010','2014-08-01',8,'N'),

    ('00010','2014-08-02',8,'N'),

    ('02535','2014-08-01',9,'N'),

    ('02535','2014-08-02',8,'N'),

    ('02535','2014-08-03',10,'N'),

    ('50500','2014-08-01',0,'A'),

    ('50500','2014-08-03',8,'N');

  • In an empty, sandbox database try the following. By the way, your view needs to be reworked but to do it right we need to see the full DDL (CREATE VIEW statement) for the view.

    create table dbo.Personnel(

    EMP_CODE VARCHAR(5) PRIMARY KEY,

    EMP_NAME VARCHAR(50) -- Really shouldn't use NAME as a column name

    );

    create table dbo.TS(

    EMP_CODE VARCHAR(5), -- can't call this single column a PRIMARY KEY as it isn't unique to this table.

    CARD_DATE DATE,

    TS_HOURS NUMERIC, -- Really, care to provide the rest of the definition to include the precision and significant digits NUMERIC(10,2) for example?

    TS_STATUS CHAR(1) -- Again, really? Using 3 characters to store 1, use a CHAR(1) instead

    );

    go

    insert into dbo.Personnel

    values ('00010','JOSEPH'),

    ('02535','PETER'),

    ('50500','MATHEW');

    insert into dbo.TS

    values ('00010','2014-08-01',8,'N'),

    ('00010','2014-08-02',8,'N'),

    ('02535','2014-08-01',9,'N'),

    ('02535','2014-08-02',8,'N'),

    ('02535','2014-08-03',10,'N'),

    ('50500','2014-08-01',0,'A'),

    ('50500','2014-08-03',8,'N');

    go

    select * from dbo.Personnel;

    select * from dbo.TS;

    SELECT DISTINCT

    ts.EMP_CODE,

    p.EMP_NAME,

    UPPER(SUBSTRING(DATENAME(month, ts.CARD_DATE), 1, 3)) + SUBSTRING(CONVERT(varchar(4), YEAR(ts.CARD_DATE)), 3, 2) AS Month,

    (SELECT

    ISNULL(SUM(ts1.TS_HOURS), 0) AS [1]

    FROM

    dbo.TS AS ts1

    WHERE

    (ts1.EMP_CODE = ts.EMP_CODE) AND

    (DAY(ts1.CARD_DATE) = 1) AND

    (DATENAME(month, ts1.CARD_DATE) = DATENAME(month, ts.CARD_DATE)) AND

    (YEAR(ts1.CARD_DATE) = YEAR(ts.CARD_DATE))) AS [1],

    (SELECT

    ISNULL(SUM(ts2.TS_HOURS), 0) AS [2]

    FROM

    dbo.TS AS ts2

    WHERE

    (ts2.EMP_CODE = ts.EMP_CODE) AND

    (DAY(ts2.CARD_DATE) = 2) AND

    (DATENAME(month, ts2.CARD_DATE) = DATENAME(month, ts.CARD_DATE)) AND

    (YEAR(ts2.CARD_DATE) = YEAR(ts.CARD_DATE))) AS [2],

    (SELECT

    ISNULL(SUM(ts3.TS_HOURS), 0) AS [3]

    FROM

    dbo.TS AS ts3

    WHERE

    (ts3.EMP_CODE = ts.EMP_CODE) AND

    (DAY(ts3.CARD_DATE) = 3) AND

    (DATENAME(month, ts3.CARD_DATE) = DATENAME(month, ts.CARD_DATE)) AND

    (YEAR(ts3.CARD_DATE) = YEAR(ts.CARD_DATE))) AS [3],

    (SELECT

    ISNULL(max(ts1.TS_STATUS),'*') AS S1

    FROM

    dbo.TS AS ts1

    WHERE

    (ts1.EMP_CODE = ts.EMP_CODE) AND

    (DAY(ts1.CARD_DATE) = 1) AND

    (DATENAME(month, ts1.CARD_DATE) = DATENAME(month, ts.CARD_DATE)) AND

    (YEAR(ts1.CARD_DATE) = YEAR(ts.CARD_DATE))) AS S1,

    (SELECT

    ISNULL(max(ts2.TS_STATUS),'*') AS S2

    FROM

    dbo.TS AS ts2

    WHERE

    (ts2.EMP_CODE = ts.EMP_CODE) AND

    (DAY(ts2.CARD_DATE) = 2) AND

    (DATENAME(month, ts2.CARD_DATE) = DATENAME(month, ts.CARD_DATE)) AND

    (YEAR(ts2.CARD_DATE) = YEAR(ts.CARD_DATE))) AS S2,

    (SELECT

    ISNULL(max(ts3.TS_STATUS),'*') AS S3

    FROM

    dbo.TS AS ts3

    WHERE

    (ts3.EMP_CODE = ts.EMP_CODE) AND

    (DAY(ts3.CARD_DATE) = 3) AND

    (DATENAME(month, ts3.CARD_DATE) = DATENAME(month, ts.CARD_DATE)) AND

    (YEAR(ts3.CARD_DATE) = YEAR(ts.CARD_DATE))) AS S3

    FROM

    dbo.TS ts

    INNER JOIN dbo.Personnel p

    ON ts.EMP_CODE = p.EMP_CODE

    GROUP BY

    ts.EMP_CODE,

    ts.CARD_DATE,

    p.EMP_NAME;

    go

    drop table dbo.Personnel;

    drop table dbo.TS;

    go

  • Hi Lynn Pettis,

    Thanks for your help and sincere efforts. The view is working as desired.

    I have gone through your Forum Etiquette and understood it well. My future postings will be as per the rules defined.

    In fact due to office work pressure I could not spend time to go through the instructions earlier. My apologies for that.

    Thanks again.

  • New_2SQL (8/4/2014)


    Hi Lynn Pettis,

    Thanks for your help and sincere efforts. The view is working as desired.

    I have gone through your Forum Etiquette and understood it well. My future postings will be as per the rules defined.

    In fact due to office work pressure I could not spend time to go through the instructions earlier. My apologies for that.

    Thanks again.

    Glad to hear.

    As for the view working as desired, yes, it may be providing you with the answer(s) you require but if the code you posted is any indication of the full view it is most likely doing so in a very inefficient and resource intensive manner and can be improved.

    Just saying.

  • Hi Lynn Pettis,

    I already understand that when data will grow the processing will slow down. My query really needs to be groomed well as I need to add more columns. Should I post a new query or you can help me in the same thread?

    Thanks and best regards.

  • As this is your thread, I'd just post it here.

  • 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.

  • 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.

  • Hi Lynn Pettis,

    On my laptop the old source code took 546ms whereas your new source code took 124ms only. Very good improvement.

    Can you also explain to me if it's the index on DYT table what made the difference because I tried to use CASE WHEN then else END statements which did not give me good result. I had then resorted to having sub queries for each day with SELECT command because otherwise I was getting one record for each day per employee but the values placed in the right column which I would sum up again with a new view to get the desired result of one line per employee for a month.

    Thanks for your great help.

  • New_2SQL (8/10/2014)


    Hi Lynn Pettis,

    On my laptop the old source code took 546ms whereas your new source code took 124ms only. Very good improvement.

    Can you also explain to me if it's the index on DYT table what made the difference because I tried to use CASE WHEN then else END statements which did not give me good result. I had then resorted to having sub queries for each day with SELECT command because otherwise I was getting one record for each day per employee but the values placed in the right column which I would sum up again with a new view to get the desired result of one line per employee for a month.

    Thanks for your great help.

    No, the index on dbo.DYT is not what makes the difference. I have run my code against the sample data with and without the index and it runs the same in both cases with only a slight difference in the execution plans. Personally, I would use the clustered index I created for the test data.

    Since I haven't seen the code you wrote using the CASE structure I can't tell you why yours failed. Based on your description above it looks like you didn't complete the aggregation in the same query.

  • New_2SQL (8/10/2014)


    On my laptop the old source code took 546ms whereas your new source code took 124ms only. Very good improvement.

    For how many rows in the tables?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff Moden,

    This was a test run on about 88 rows as you can see in the source code posted above.

    During production rows will grow in millions.

  • New_2SQL (8/10/2014)


    Hi Jeff Moden,

    This was a test run on about 88 rows as you can see in the source code posted above.

    During production rows will grow in millions.

    If production conditions qill have to deal with millions of rows, your test setup should reflect that.

    I strongly recommend to test against a million row table.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (8/11/2014)


    New_2SQL (8/10/2014)


    Hi Jeff Moden,

    This was a test run on about 88 rows as you can see in the source code posted above.

    During production rows will grow in millions.

    If production conditions qill have to deal with millions of rows, your test setup should reflect that.

    I strongly recommend to test against a million row table.

    And at that point I would hope that you are restricting the query with a WHERE clause to limit the data being processed.

Viewing 14 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply