Sergiy (5/30/2016)
You never use values from derived table columns Noisy and Telephony in outer query.Should it be this?
SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN 'Telephony' THEN Telephony ELSE 0 END ELSE 0 END) EN,SUM(CASE WHEN DT.lang = 'BM' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN 'Telephony' THEN Telephony ELSE 0 END ELSE 0 END) BM
Works perfectly, thanks!
SELECT DT.date, (a.source + '-' + a.report_item) as report_item,
SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'No Dial Tone' THEN NoDialTone
WHEN 'Noisy' THEN Noisy
WHEN 'CKC End Call' THEN CKCEndCall
WHEN 'CKC Transfer CSR' THEN CKCTransferCSR
WHEN 'Tel - Speak to CSR' THEN TelSpeakToCSR
WHEN 'Inv Num - Del' THEN InvNumDel
WHEN 'Inv Num - Unifi' THEN InvNumUnifi
ELSE 0 END ELSE 0 END) EN,
SUM(CASE WHEN DT.lang = 'BM' THEN CASE a.report_item WHEN 'No Dial Tone' THEN NoDialTone
WHEN 'Noisy' THEN Noisy
WHEN 'CKC End Call' THEN CKCEndCall
WHEN 'CKC Transfer CSR' THEN CKCTransferCSR
WHEN 'Tel - Speak to CSR' THEN TelSpeakToCSR
WHEN 'Inv Num - Del' THEN InvNumDel
WHEN 'Inv Num - Unifi' THEN InvNumUnifi
ELSE 0 END ELSE 0 END) BM,
SUM(CASE WHEN DT.lang = 'MD' THEN CASE a.report_item WHEN 'No Dial Tone' THEN NoDialTone
WHEN 'Noisy' THEN Noisy
WHEN 'CKC End Call' THEN CKCEndCall
WHEN 'CKC Transfer CSR' THEN CKCTransferCSR
WHEN 'Tel - Speak to CSR' THEN TelSpeakToCSR
WHEN 'Inv Num - Del' THEN InvNumDel
WHEN 'Inv Num - Unifi' THEN InvNumUnifi
ELSE 0 END ELSE 0 END) MD
FROM ann_ReportItem a
INNER JOIN (
SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang,
SUM(CONVERT(INT, b.sel_tel_nodialtone)) NoDialTone,
SUM(CONVERT(INT, b.sel_tel_noisy)) Noisy,
SUM(CONVERT(INT, b.sel_tech_ckc_disconnect)) CKCEndCall,
SUM(CONVERT(INT, b.sel_tech_ckc_transfer)) CKCTransferCSR,
SUM(CONVERT(INT, b.sel_tel_csr)) TelSpeakToCSR,
SUM(CONVERT(INT, b.sel_inv_del)) InvNumDel,
SUM(CONVERT(INT, b.sel_inv_unifi)) InvNumUnifi
FROM ann_events_Tech_Details b
WHERE b.start_time >= @StartDate
AND b.start_time < dateadd(dd,0, datediff(dd,0,@EndDate)+1)
AND b.lang IN ('EN', 'BM', 'MD')
GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), b.source, b.lang
) DT ON a.source = DT.source
WHERE a.report_id =8 AND a.call_flow_name = @call_flow_name
AND a.report_item IN ('No Dial Tone','Noisy', 'CKC End Call', 'CKC Transfer CSR', 'Tel - Speak to CSR', 'Inv Num - Del', 'Inv Num - Unifi')
GROUP BY DT.DATE, A.SOURCE, a.report_item
Fastest query method with these indexes:
--ann_events_Tech_Details
CREATE CLUSTERED INDEX [IX_Clustered] ON [dbo].[ann_events_Tech_Details]
(
[start_time] ASC,
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [PK_ann_events_Tech_Details] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
--ann_ReportItem
ALTER TABLE [dbo].[ann_ReportItem] ADD CONSTRAINT [PK_ann_ReportItem] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
(2149 row(s) affected)
Table 'ann_ReportItem'. Scan count 7, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ann_events_Tech_Details'. Scan count 9, logical reads 66203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 9422 ms, elapsed time = 1469 ms.
Slower but uses less CPU time query method with these indexes:
--ann_events_Tech_Details
CREATE NONCLUSTERED INDEX [IX_QueryHelper] ON [dbo].[ann_events_Tech_Details]
(
[lang] ASC,
[start_time] ASC
)
INCLUDE ( [source],
[sel_tel_nodialtone],
[sel_tel_noisy],
[sel_tel_csr],
[sel_tech_ckc_disconnect],
[sel_tech_ckc_transfer],
[sel_inv_del],
[sel_inv_unifi]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--ann_ReportItem
CREATE NONCLUSTERED INDEX [ix_RItem] ON [dbo].[ann_ReportItem]
(
[source] ASC,
[call_flow_name] ASC,
[report_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ann_ReportItem] ADD CONSTRAINT [PK_ReportItem_ID] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
(2149 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ann_events_Tech_Details'. Scan count 3, logical reads 12602, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ann_ReportItem'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 4290 ms, elapsed time = 4427 ms.
Both methods are good enough to me, any suggestion/feedback would be appreciated.