Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

How to replace null value in a query Expand / Collapse
Author
Message
Posted Saturday, August 9, 2014 6:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 20,676, Visits: 32,268
As this is your thread, I'd just post it here.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1601395
Posted Saturday, August 9, 2014 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 10, 2014 10:10 PM
Points: 11, Visits: 22
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.



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





Thanks and best regards.
Post #1601430
Posted Saturday, August 9, 2014 3:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 20,676, Visits: 32,268
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1601462
Posted Sunday, August 10, 2014 12:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 10, 2014 10:10 PM
Points: 11, Visits: 22
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.
Post #1601546
Posted Sunday, August 10, 2014 6:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 20,676, Visits: 32,268
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1601558
Posted Sunday, August 10, 2014 9:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601622
Posted Sunday, August 10, 2014 10:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 10, 2014 10:10 PM
Points: 11, Visits: 22
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.
Post #1601629
Posted Monday, August 11, 2014 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 6,823, Visits: 13,271
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1601691
Posted Monday, August 11, 2014 7:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 20,676, Visits: 32,268
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1601845
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse