January 8, 2015 at 8:46 am
Hello,
I have inherited a lengthy query which I hope to optimize it, part of the query is posted here:
SELECT
[Incident Date] AS [Incident Date],
[Order] AS [Order],
[Location] AS [Location],
[Action] AS [Action],
[Channel Type] AS [Channel Type],
SUM([Bribery & Corruption]) AS [Bribery & Corruption],
SUM([Confidential] + [Confidential - Employee]) AS [Confidential],
SUM([Confidential - Employee]) AS [Confidential - Employee],
SUM([Copyright Infringements]) AS [Copyright Infringements],
SUM([Inappropriate]) AS [Inappropriate],
SUM([Irregular Practice] + [Irregular Practice - Employee]) AS [Irregular Practice],
SUM([Irregular Practice - Employee]) AS [Irregular Practice - Employee],
SUM([Mergers & Acquisition]) AS [Mergers & Acquisition],
SUM([PCI DSS] + [PCI DSS - Employee]) AS [PCI DSS],
SUM([PCI DSS - Employee]) AS [PCI DSS - Employee],
SUM([PII - Customer] + [PII - Employee]) AS [PII],
SUM([PII - Employee]) AS [PII - Employee],
SUM([Total]) AS [Total]
FROM (
SELECT
DD1.[Incident_x0020_Date] AS [Incident Date],
1 AS [Order],
DD1.[Int_x002f_Dom] AS [Location],
DD1.[ACTION] AS [Action],
DD1.[Channel] AS [Channel Type],
COUNT(*) AS [Bribery & Corruption],
0 AS [Confidential],
0 AS [Confidential - Employee],
0 AS [Copyright Infringements],
0 AS [Inappropriate],
0 AS [Irregular Practice],
0 AS [Irregular Practice - Employee],
0 AS [Mergers & Acquisition],
0 AS [PCI DSS],
0 AS [PCI DSS - Employee],
0 AS [PII - Customer],
0 AS [PII - Employee],
0 AS [Total]
FROM [dbo].[DLP Escalations] DD1
WHERE DD1.[Int_x002f_Dom] = 'Domestic' AND DD1.[DLP_x0020_VIOLATION] = 'Bribery & Corruption'
GROUP BY DD1.[Incident_x0020_Date], DD1.[Int_x002f_Dom], DD1.[ACTION], DD1.[Channel]
UNION ALL
SELECT
DD1.[Incident_x0020_Date] AS [Incident Date],
1 AS [Order],
DD1.[Int_x002f_Dom] AS [Location],
DD1.[ACTION] AS [Action],
DD1.[Channel] AS [Channel Type],
0 AS [Bribery & Corruption],
COUNT(*) AS [Confidential],
0 AS [Confidential - Employee],
0 AS [Copyright Infringements],
0 AS [Inappropriate],
0 AS [Irregular Practice],
0 AS [Irregular Practice - Employee],
0 AS [Mergers & Acquisition],
0 AS [PCI DSS],
0 AS [PCI DSS - Employee],
0 AS [PII - Customer],
0 AS [PII - Employee],
0 AS [Total]
FROM [dbo].[DLP Escalations] DD1
WHERE DD1.[Int_x002f_Dom] = 'Domestic' AND DD1.[DLP_x0020_VIOLATION] = 'Confidential'
GROUP BY DD1.[Incident_x0020_Date], DD1.[Int_x002f_Dom], DD1.[ACTION], DD1.[Channel]
) GROUP BY [Incident Date], [Order], [Location], [Action], [Channel Type]
Please note: there are 12 fields ([Bribery & Corruption], [Confidential], [Confidential - Employee], [Copyright Infringements],
[Inappropriate], [Irregular Practice], [Irregular Practice - Employee], [Mergers & Acquisition], [PCI DSS], [PCI DSS - Employee], [PII - Customer], [PII - Employee]) to report on and a total field will be calculated, the above code cut off all the unions except for the first two fields: [Bribery & Corruption], [Confidential]
If possible, please avoid any variables definition.
Thank you very much.
January 8, 2015 at 8:59 am
I guess the problem is that you're reading the table 12 times instead of once.
Use cross tabs to help with your problem. More info in here: http://www.sqlservercentral.com/articles/T-SQL/63681/
This is an example, you might need to complete the code.
SELECT
DD1.[Incident_x0020_Date] AS [Incident Date],
1 AS [Order],
DD1.[Int_x002f_Dom] AS [Location],
DD1.[ACTION] AS [Action],
DD1.[Channel] AS [Channel Type],
COUNT(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Bribery & Corruption' THEN 1 ELSE NULL END) AS [Bribery & Corruption],
COUNT(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Confidential' THEN 1 ELSE NULL END) AS [Confidential],
0 AS [Confidential - Employee],
0 AS [Copyright Infringements],
0 AS [Inappropriate],
0 AS [Irregular Practice],
0 AS [Irregular Practice - Employee],
0 AS [Mergers & Acquisition],
0 AS [PCI DSS],
0 AS [PCI DSS - Employee],
0 AS [PII - Customer],
0 AS [PII - Employee],
0 AS [Total]
FROM [dbo].[DLP Escalations] DD1
WHERE DD1.[Int_x002f_Dom] = 'Domestic'
GROUP BY DD1.[Incident_x0020_Date], DD1.[Int_x002f_Dom], DD1.[ACTION], DD1.[Channel]
January 8, 2015 at 4:41 pm
SELECT
DD1.[Incident_x0020_Date] AS [Incident Date],
1 AS [Order],
DD1.[Int_x002f_Dom] AS [Location],
DD1.[ACTION] AS [Action],
DD1.[Channel] AS [Channel Type],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Bribery & Corruption' THEN 1 ELSE 0 END) AS [Bribery & Corruption],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Confidential' THEN 1 ELSE 0 END) AS [Confidential],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Confidential - Employee' THEN 1 ELSE 0 END) AS [Confidential - Employee],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Copyright Infringements' THEN 1 ELSE 0 END) AS [Copyright Infringements],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Inappropriate' THEN 1 ELSE 0 END) AS [Inappropriate],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Irregular Practice' THEN 1 ELSE 0 END) AS [Irregular Practice],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Irregular Practice - Employee' THEN 1 ELSE 0 END) AS [Irregular Practice - Employee],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Mergers & Acquisition' THEN 1 ELSE 0 END) AS [Mergers & Acquisition],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'PCI DSS' THEN 1 ELSE 0 END) AS [PCI DSS],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'PCI DSS - Employee' THEN 1 ELSE 0 END) AS [PCI DSS - Employee],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'PII - Customer' THEN 1 ELSE 0 END) AS [PII - Customer],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'PII - Employee' THEN 1 ELSE 0 END) AS [PII - Employee]
FROM [dbo].[DLP Escalations] DD1
WHERE DD1.[Int_x002f_Dom] = 'Domestic'
AND DD1.[DLP_x0020_VIOLATION] IN (
'Bribery & Corruption',
'Confidential',
'Confidential - Employee',
'Copyright Infringements',
'Inappropriate',
'Irregular Practice',
'Irregular Practice - Employee',
'Mergers & Acquisition',
'PCI DSS',
'PCI DSS - Employee',
'PII - Customer',
'PII - Employee'
)
GROUP BY DD1.[Incident_x0020_Date], DD1.[Int_x002f_Dom], DD1.[ACTION], DD1.[Channel]
ORDER BY DD1.[Incident_x0020_Date], DD1.[Int_x002f_Dom], DD1.[ACTION], DD1.[Channel]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2015 at 9:16 am
Thanks Luis and Scott for the code.
Using Luis code, how do I get a total based on the 12 fields' count result?
January 9, 2015 at 10:24 am
sonchoix (1/9/2015)
Thanks Luis and Scott for the code.Using Luis code, how do I get a total based on the 12 fields' count result?
It depends.
Are those all the possible values?
If so, you can add a COUNT(*).
If not, you need to use IN() with the 12 values.
January 9, 2015 at 11:29 am
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply