July 7, 2015 at 12:13 pm
My query is like the following and I want to make it simpler, can anyone help please? Thank you in advance.
Select Policy =
Case
When
((Policy_Name = 'Credit Cards') OR
(Policy_Name = 'PCI') OR
(Policy_Name = 'PCI Audit'))
Then 'PII'
Else 'Others'
End,
Count(1) as Total,
From Reporting_DailyDlpDetail
Where (INSERT_DETECT_TS between '20150602' and '20150603')
AND Status = 'New' AND Actual_Severity = 1
Group By
Case
When
((Policy_Name = 'Credit Cards') OR
(Policy_Name = 'PCI') OR
(Policy_Name = 'PCI Audit'))
Then 'PII'
Else 'Others'
End
There are actually more than the above to be considered:
I have 30 sub policies to be rolled up to 6 policies. For each of the 6 policies, I have another two factors to be grouped based on: Status and Actual_Severity
Status: New, Closed, In Process, Escalated
Actual_Severity: 1 (High), 2 (Medium), 3 (Low)
The final result I am looking for is:
Policy TotalHigh(when Status = 'New' and Actual_Severity = 1) TotalMedLow (when Status = 'New' and combine Actual_Severity 2 and 3) Reviewed (when Status = 'Closed') Escalated (when Status = 'Escalated') InProcess (when Status = 'In Process')
i.e.
Policy TotalHigh TotalMedLow Reviewed Escalated InProcess
Policy1 123 12344 1233 111 222
Policy2 123 12344 1233 111 222
Policy3 123 12344 1233 111 222
Policy4 123 12344 1233 111 222
Policy5 123 12344 1233 111 222
Policy6 123 12344 1233 111 222
Policy1: when Policy_Name = Policy_Name 11 or Policy_Name 12 or Policy_Name 13
Policy2: when Policy_Name = Policy_Name 21 or Policy_Name 22 or Policy_Name 23
Policy3: when Policy_Name = Policy_Name 31 or Policy_Name 32 or Policy_Name 33
Policy4: when Policy_Name = Policy_Name 41 or Policy_Name 42 or Policy_Name 43
Policy5: when Policy_Name = Policy_Name 51 or Policy_Name 52 or Policy_Name 53
Policy6: when Policy_Name = Policy_Name 61 or Policy_Name 62 or Policy_Name 63
July 7, 2015 at 12:18 pm
I've worked it out:
Sum(CASE WHEN ACTUAL_SEVERITY = 1 and STATUS = 'New' THEN 1 ELSE 0 END) AS [New High],
and off course put Status and ACTUAL_SEVERITY in the Group By
Thanks for watching
July 7, 2015 at 12:33 pm
I would create a lookup table: lookup_Policies (Policy, SubPolicy)
The data in that table would be like:
Policy1, Policy_Name 11
Policy1, Policy_Name 12
Policy1, Policy_Name 13
Policy2, Policy_Name 21
Policy2, Policy_Name 22
...etc
Then I would do something like this:
SELECT
b.Policy,
SUM(CASE WHEN Status = 'New' AND Actual_Severity = 1 THEN 1 ELSE 0 END) as TotalHigh,
SUM(CASE WHEN Status = 'New' AND Actual_Severity in (2,3) THEN 1 ELSE 0 END) as TotalMedLow,
..etc
FROM Reporting_DailyDlpDetail a
INNER JOIN lookup_Policies b ON a.Policy_Name = b.SubPolicy
GROUP BY
b.policy
Alternately, if you're certain that the pattern of the sub-policies to policies will always be the leading digit, you could do something like this instead:
SELECT
'Policy' + SUBSTRING(Policy_Name, CHARINDEX('Name ', Policy_Name, 0)+5, 1) as Policy,
SUM(CASE WHEN Status = 'New' AND Actual_Severity = 1 THEN 1 ELSE 0 END) as TotalHigh,
SUM(CASE WHEN Status = 'New' AND Actual_Severity in (2,3) THEN 1 ELSE 0 END) as TotalMedLow,
..etc
FROM Reporting_DailyDlpDetail a
GROUP BY
'Policy' + SUBSTRING(Policy_Name, CHARINDEX('Name ', Policy_Name, 0)+5, 1)
July 7, 2015 at 12:33 pm
WHERE (INSERT_DETECT_TS between '20150602' and '20150603')
You cannot safely use between. You need to use the >= and < (not <=, as in between) that I used earlier:
WHERE (INSERT_DETECT_TS >= '20150602' and INSERT_DETECT_TS < '20150603')
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".
July 7, 2015 at 12:35 pm
The new issue pops up on this task:
For a particular policy, the rolled up sub policies are different in the case of High and the rest.
i.e. If the policy = 'Policy3' Then count High for Policy_Name31 and Policy_Name32, count Med/Low for Policy_Name31 and Policy_Name32 and Policy_Name33 and Policy_Name34
Plus:
I need to add another column to the result grid and I am really not sure if this can be done within the same one mega query:
Let me call the extra column 'Extra', it will be counted when policy_name = 'Special Policy', in the case of Status = 'New' and no matter what's the Actual_Severity, for all the policies, just assign 0 to it.
So the final result expected becomes:
Policy TotalHigh TotalMedLow Reviewed Escalated InProcess Extra
Policy1 123 12344 1233 111 222 0
Policy2 123 12344 1233 111 222 0
Policy3 123 12344 1233 111 222 nnn
Policy4 123 12344 1233 111 222 0
Policy5 123 12344 1233 111 222 0
Policy6 123 12344 1233 111 222 0
Thank you very much and any suggestion will be greatly appreciated
July 7, 2015 at 12:37 pm
ScottPletcher (7/7/2015)
WHERE (INSERT_DETECT_TS between '20150602' and '20150603')You cannot safely use between. You need to use the >= and < (not <=, as in between) that I used earlier:
WHERE (INSERT_DETECT_TS >= '20150602' and INSERT_DETECT_TS < '20150603')
Thanks Scott, will follow your advise
July 7, 2015 at 12:38 pm
cphite (7/7/2015)
I would create a lookup table: lookup_Policies (Policy, SubPolicy)The data in that table would be like:
Policy1, Policy_Name 11
Policy1, Policy_Name 12
Policy1, Policy_Name 13
Policy2, Policy_Name 21
Policy2, Policy_Name 22
...etc
Then I would do something like this:
SELECTb.Policy,
SUM(CASE WHEN Status = 'New' AND Actual_Severity = 1 THEN 1 ELSE 0 END) as TotalHigh,
SUM(CASE WHEN Status = 'New' AND Actual_Severity in (2,3) THEN 1 ELSE 0 END) as TotalMedLow,
..etc
FROM Reporting_DailyDlpDetail a
INNER JOIN lookup_Policies b ON a.Policy_Name = b.SubPolicy
GROUP BY
b.policy
Alternately, if you're certain that the pattern of the sub-policies to policies will always be the leading digit, you could do something like this instead:
SELECT'Policy' + SUBSTRING(Policy_Name, CHARINDEX('Name ', Policy_Name, 0)+5, 1) as Policy,
SUM(CASE WHEN Status = 'New' AND Actual_Severity = 1 THEN 1 ELSE 0 END) as TotalHigh,
SUM(CASE WHEN Status = 'New' AND Actual_Severity in (2,3) THEN 1 ELSE 0 END) as TotalMedLow,
..etc
FROM Reporting_DailyDlpDetail a
GROUP BY
'Policy' + SUBSTRING(Policy_Name, CHARINDEX('Name ', Policy_Name, 0)+5, 1)
Thank you, there is no any pattern to the name of policies
July 7, 2015 at 1:07 pm
The only thing left here now is the ORDER BY:
The following code throws me an error:
Column "Reporting_DailyDlpDetail.POLICY_NAME" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Select Policy =
Case
When
((Policy_Name = 'Policy_Name11') OR
(Policy_Name = 'Policy_Name12') OR
(Policy_Name = 'Policy_Name13')
)
Then 'Policy1'
When
((Policy_Name = 'Policy_Name21') OR
(Policy_Name = 'Policy_Name22') OR
(Policy_Name = 'Policy_Name23')
)
Then 'Policy2'
When
((Policy_Name = 'Policy_Name31') OR
(Policy_Name = 'Policy_Name32') OR
(Policy_Name = 'Policy_Name33')
)
Then 'Policy3'
When
((Policy_Name = 'Policy_Name41') OR
(Policy_Name = 'Policy_Name42') OR
(Policy_Name = 'Policy_Name43')
)
Then 'Policy4'
When
((Policy_Name = 'Policy_Name51') OR
(Policy_Name = 'Policy_Name52') OR
(Policy_Name = 'Policy_Name53')
)
Then 'Policy5'
When
((Policy_Name = 'Policy_Name61') OR
(Policy_Name = 'Policy_Name62') OR
(Policy_Name = 'Policy_Name63')
)
Then 'Policy6'
End,
Sum(CASE WHEN ACTUAL_SEVERITY = 1 and STATUS = 'New' THEN 1 ELSE 0 END) AS [New High],
Sum(CASE WHEN (ACTUAL_SEVERITY = 2 OR ACTUAL_SEVERITY = 3) and STATUS = 'New' THEN 1 ELSE 0 END) AS [New Med/Low],
Sum(CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END) AS [Reviewed],
Sum(CASE WHEN STATUS = 'New' THEN 1 ELSE 0 END) AS [NotReviewed],
Sum(CASE WHEN STATUS = 'In Process' THEN 1 ELSE 0 END) AS [In Process],
Sum(CASE WHEN STATUS = 'Escalated' THEN 1 ELSE 0 END) AS [Escalated],
Sum(CASE WHEN POLICY_NAME = 'SPECIAL POLICY' THEN 1 ELSE 0 END) AS Extra
From Reporting_DailyDlpDetail
Where (INSERT_DETECT_TS >= '20150602' and INSERT_DETECT_TS < '20150603')
Group By
Case
When
((Policy_Name = 'Policy_Name11') OR
(Policy_Name = 'Policy_Name12') OR
(Policy_Name = 'Policy_Name13')
)
Then 'Policy1'
When
((Policy_Name = 'Policy_Name21') OR
(Policy_Name = 'Policy_Name22') OR
(Policy_Name = 'Policy_Name23')
)
Then 'Policy2'
When
((Policy_Name = 'Policy_Name31') OR
(Policy_Name = 'Policy_Name32') OR
(Policy_Name = 'Policy_Name33')
)
Then 'Policy3'
When
((Policy_Name = 'Policy_Name41') OR
(Policy_Name = 'Policy_Name42') OR
(Policy_Name = 'Policy_Name43')
)
Then 'Policy4'
When
((Policy_Name = 'Policy_Name51') OR
(Policy_Name = 'Policy_Name52') OR
(Policy_Name = 'Policy_Name53')
)
Then 'Policy5'
When
((Policy_Name = 'Policy_Name61') OR
(Policy_Name = 'Policy_Name62') OR
(Policy_Name = 'Policy_Name63')
)
Then 'Policy6'
End
ORDER By
Case
When
((Policy_Name = 'Policy_Name11') OR
(Policy_Name = 'Policy_Name12') OR
(Policy_Name = 'Policy_Name13')
)
Then 1
When
((Policy_Name = 'Policy_Name21') OR
(Policy_Name = 'Policy_Name22') OR
(Policy_Name = 'Policy_Name23')
)
Then 2
When
((Policy_Name = 'Policy_Name31') OR
(Policy_Name = 'Policy_Name32') OR
(Policy_Name = 'Policy_Name33')
)
Then 3
When
((Policy_Name = 'Policy_Name41') OR
(Policy_Name = 'Policy_Name42') OR
(Policy_Name = 'Policy_Name43')
)
Then 4
When
((Policy_Name = 'Policy_Name51') OR
(Policy_Name = 'Policy_Name52') OR
(Policy_Name = 'Policy_Name53')
)
Then 5
When
((Policy_Name = 'Policy_Name61') OR
(Policy_Name = 'Policy_Name62') OR
(Policy_Name = 'Policy_Name63')
)
Then 6
End
July 7, 2015 at 1:28 pm
Create a Policy lookup table to assign the common policy id and the sort order:
CREATE TABLE Reporting_PolicyGrouping (
Policy_Name varchar(50) NOT NULL,
Policy varchar(50) NOT NULL,
Sort_Order tinying NOT NULL,
CONSTRAINT Reporting_PolicyGrouping__PK PRIMARY KEY CLUSTERED ( Policy_Name ) WITH ( FILLFACTOR = 99 ) ON [PRIMARY]
)
INSERT INTO Reporting_PolicyGrouping ( Policy_Name, Policy, Sort_Order )
SELECT 'Policy_Name11', 'Policy1', 1 UNION ALL
SELECT 'Policy_Name12', 'Policy1', 1 UNION ALL
SELECT 'Policy_Name13', 'Policy1', 1 UNION ALL
SELECT 'Policy_Name21', 'Policy2', 2 UNION ALL
SELECT 'Policy_Name22', 'Policy2', 2 UNION ALL
SELECT 'Policy_Name23', 'Policy2', 2 --UNION ALL
--...
Select aan.Policy,
Sum(CASE WHEN ACTUAL_SEVERITY = 1 and STATUS = 'New' THEN 1 ELSE 0 END) AS [New High],
Sum(CASE WHEN (ACTUAL_SEVERITY = 2 OR ACTUAL_SEVERITY = 3) and STATUS = 'New' THEN 1 ELSE 0 END) AS [New Med/Low],
Sum(CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END) AS [Reviewed],
Sum(CASE WHEN STATUS = 'New' THEN 1 ELSE 0 END) AS [NotReviewed],
Sum(CASE WHEN STATUS = 'In Process' THEN 1 ELSE 0 END) AS [In Process],
Sum(CASE WHEN STATUS = 'Escalated' THEN 1 ELSE 0 END) AS [Escalated],
Sum(CASE WHEN POLICY_NAME = 'SPECIAL POLICY' THEN 1 ELSE 0 END) AS Extra
From Reporting_DailyDlpDetail ddp
Left Outer Join Reporting_PolicyGrouping g ON
g.Policy_Name = ddp.Policy_Name
Cross Apply (
SELECT ISNULL(g.Policy, ddp.Policy_Name) AS Policy,
ISNULL(g.Sort_Order, 0) AS Sort_Order --or 255 if you want unassigned last
) AS aan --assign_alias_names
Where (INSERT_DETECT_TS >= '20150602' and INSERT_DETECT_TS < '20150603')
Group By aan.Policy
ORDER By aan.Sort_Order
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".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply