a very COMPLEX aggregation query

Question

a very COMPLEX aggregation query

Viewing 15 posts - 46 through 59 (of 59 total)

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

polkadot SSChampion Points: 13664 More actions · Answer 1

Dwains does pretty much exactly what needs to be done even with the additional business rules, with the exception that it errors in two places:

WA_Traffic Proposed should be 30-60 days, his tallies under <30 days.

WA_Parent Relocation Proposed shld be 30-60 days, his tallies <30 days.

his code

DECLARE @Case_Review TABLE

(Case_ID int, Legal_Omn varchar(30),PPL_Area varchar(30),Revision int

,Review_Status varchar(30),Change_Date datetime);

insert into @Case_Review

SELECT 220, 'Civil', 'WA_Tenant', 1, 'Proposed', '2012-04-17 12:17'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant', 2, 'Proposed', '2012-04-17 14:05'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant',3, 'Proposed', '2012-04-18 17:03'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant',4, 'Proposed', '2012-04-18 17:05'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant', 5, 'Active', '2012-04-19 12:20'

UNION ALL SELECT 220,'Civil', 'WA_Tenant', 6, 'Active', '2012-04-19 12:45'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant',7, 'Active', '2012-04-20 08:05'

UNION ALL SELECT 220,'Civil', 'WA_Tenant', 8, 'Resolved', '2012-05-27 12:21'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant',9, 'Resolved', '2012-05-27 12:45'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant',10, 'Resolved', '2012-05-28 12:00'

UNION ALL SELECT 220, 'Civil', 'WA_Tenant',11, 'Resolved', '2012-05-28 12:01'

UNION ALL SELECT 230, 'Civil', 'WA_Traffic', 1, 'Proposed', '2012-04-17 01:00'

UNION ALL SELECT 230, 'Civil', 'WA_Traffic', 2, 'Proposed', '2012-05-29 07:00'

UNION ALL SELECT 230, 'Civil', 'WA_Traffic', 3, 'Active', '2012-05-29 12:00'

UNION ALL SELECT 230,'Civil', 'WA_Traffic', 4, 'Active', '2012-05-29 12:05'

UNION ALL SELECT 230, 'Civil', 'WA_Traffic', 5, 'Active', '2012-05-29 12:00'

UNION ALL SELECT 230, 'Civil', 'WA_Traffic', 6, 'Closed', '2012-06-01 13:00'

UNION ALL SELECT 230, 'Civil', 'WA_Traffic', 7, 'Closed', '2012-06-01 13:01'

UNION ALL SELECT 260, 'Civil', 'WA_Consumer', 1, 'Proposed', '2012-04-17 14:00'

UNION ALL SELECT 260, 'Civil', 'WA_Consumer', 2, 'Active', '2012-05-20 14:25'

UNION ALL SELECT 260, 'Civil', 'WA_Consumer',3, 'Active', '2012-07-10 15:00'

UNION ALL SELECT 260, 'Civil', 'WA_Consumer',4, 'Closed', '2012-10-20 15:20'

UNION ALL SELECT 270, 'Family', 'WA_Parent_Relocation', 1, 'Proposed', '2012-02-17 01:00'

UNION ALL SELECT 270, 'Family', 'WA_Parent_Relocation', 2, 'Proposed', '2012-03-29 07:00'

UNION ALL SELECT 270, 'Family', 'WA_Parent_Relocation', 3, 'Active', '2012-03-29 12:00'

UNION ALL SELECT 270,'Family', 'WA_Parent_Relocation', 4, 'Active', '2012-03-29 12:05'

UNION ALL SELECT 270, 'Family', 'WA_Parent_Relocation', 5, 'Active', '2012-03-29 12:00'

UNION ALL SELECT 270, 'Family', 'WA_Parent_Relocation', 6, 'Closed', '2012-04-01 13:00'

UNION ALL SELECT 270, 'Family', 'WA_Parent_Relocation', 7, 'Closed', '2012-04-01 13:01'

UNION ALL SELECT 280, 'Criminal', 'WA_Burglary', 1, 'Proposed', '2012-01-17 14:00'

UNION ALL SELECT 280, 'Criminal', 'WA_Burglary', 2, 'Active', '2012-04-20 14:25'

UNION ALL SELECT 280, 'Criminal', 'WA_Burglary',3, 'Active', '2012-05-10 15:00'

UNION ALL SELECT 280, 'Criminal', 'WA_Burglary',4, 'Closed', '2012-06-20 15:20';

;WITH UniqueCases AS (

SELECT Case_ID, Legal_Omn, PPL_Area, Review_Status

,CASE WHEN Review_Status = 'Active'

THEN DATEDIFF(day, Change_Date

,ISNULL(

(SELECT TOP 1 Change_Date

FROM @Case_Review c2

WHERE c1.Case_ID = c2.Case_ID and c2.Review_Status IN ('Closed', 'Resolved')

ORDER BY Change_Date)

,0)

)

WHEN Review_Status = 'Proposed'

THEN DATEDIFF(day, Change_Date

, ISNULL(

(SELECT TOP 1 Change_Date

FROM @Case_Review c2

WHERE c1.Case_ID = c2.Case_ID and c2.Review_Status = 'Active'

ORDER BY Change_Date)

,0)

)

ELSE 0 END As Days

,ROW_NUMBER() OVER (PARTITION BY Case_ID, PPL_Area, Review_Status

ORDER BY Case_ID, PPL_Area, Review_Status, Revision DESC) As rk

FROM @Case_Review c1)

,Counts AS (

SELECT Case_ID, Legal_Omn, PPL_Area, Review_Status

,CASE WHEN Days <30 AND Days >= 0 THEN 1 ELSE 0 END As [<30]

,CASE WHEN Days >=30 AND Days <= 60 THEN 1 ELSE 0 END As [30-60]

,CASE WHEN Days >60 THEN 1 ELSE 0 END As [>60]

FROM UniqueCases

WHERE rk = 1 and Review_Status NOT IN ('Closed', 'Resolved'))

SELECT PPL_Area, Review_Status, [<30], [30-60], [>60]

FROM (

SELECT PPL_Area, Review_Status, [<30], [30-60], [>60]

FROM (

SELECT PPL_Area, Review_Status

,SUM([<30]) As [<30], SUM([30-60]) As [30-60], SUM([>60]) AS [>60]

FROM Counts

GROUP BY PPL_Area, Review_Status) x1

UNION ALL

SELECT PPL_Area, Review_Status, [<30], [30-60], [>60]

FROM (

SELECT Legal_Omn AS PPL_Area, NULL AS Review_Status

,SUM([<30]) As [<30], SUM([30-60]) As [30-60], SUM([>60]) AS [>60]

FROM Counts WHERE Review_Status = 'Active'

GROUP BY Legal_Omn) x2) x3

ORDER BY CASE PPL_Area

WHEN 'WA_Consumer' THEN 3

WHEN 'WA_Traffic' THEN 2

WHEN 'WA_Tenant' THEN 1

ELSE 0 END

, CASE Review_Status

WHEN 'Active' THEN 2

ELSE 1 END

I can't see the problem.

--Quote me

drew.allen SSC Guru Points: 76987 More actions · Answer 2

polkadot (4/20/2012)
Drew,
please explain how 'Days' is introduced. Since you and Dwain are both using DATEDIFF to extract
day portion of the datetime, I would have expected your CASE statements to refer to 'day' and not 'Days'.
Where did you instantiate Days?

Days is the alias given to the second column in the CROSS APPLY.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76987 More actions · Answer 3

polkadot (4/20/2012)
Dwains does pretty much exactly what needs to be done even with the additional business rules, with the exception that it errors in two places:

The problem with Dwain's code is that he uses Revision DESC in the ORDER BY of the ROW_NUMBER() clause, which means that he's comparing the difference between the last date of one status and the first date of the next status instead of comparing the first dates of both statuses.

In most cases, the difference between the first date and the last date within a given status isn't sufficient to change which bucket it falls in.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

polkadot SSChampion Points: 13664 More actions · Answer 4

actually, I thought he was taking the first date of one status (top Active) and the first date of the second status (because of reverse ordering) when I first read his code. What you say explains how only Traffic and Parent_Relocation would be affected.

I don't see the solution withinn his code and I don't see the solution in yours for getting the counts instead of the days per ticket.

Can you give more clues?

--Quote me

drew.allen SSC Guru Points: 76987 More actions · Answer 5

polkadot (4/20/2012)
actually, I thought he was taking the first date of one status (top Active) and the first date of the second status (because of reverse ordering) when I first read his code. What you say explains how only Traffic and Parent_Relocation would be affected.
I don't see the solution withinn his code and I don't see the solution in yours for getting the counts instead of the days per ticket.
Can you give more clues?

What aggregate am I using in my main query? What aggregate are you looking for?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

Polkadot,

I'm back in action! I see a lot of activity but it doesn't seem to yet be resolved, so let me try to answer these questions first.

(1) dwain the code does almost exactly what it is supposed to. BUT (edit). WA_Traffic Proposed time should actually be 30-60 days and your code places it at <30 days. Any idea? Otherwise it looks good.

I need to refresh my memory before I answer this one. Hang in there. This is expected to be the first of 2 posts.

(2)
I understand almost all the code EXCEPT the last two SELECT statements.
In the first, you are grouping by PPL_Area, Review_Status) x1 <----what's the x1????

x1 is the alias (required) for the derived table. I recall having some issues with the ORDER BY across the UNION of the two results, so I had to push these "last two SELECTs" into derived table to ORDER BY the results I wanted. Not sure that's a good explanation but I may try to revise this to make it a little clearer. Hey, I was punting.

in the second, you are grouping by Legal_Omn) x2) x3 <----what's that???

Once again, x2 and x3 are simply the aliases for the derived tables I had to create to facilitate my sorting.

in the second you are also ordering by CASE PPL_Area
WHEN 'WA_Consumer' THEN 3 <---------?
WHEN 'WA_Traffic' THEN 2 <----------?
WHEN 'WA_Tenant' THEN 1 <------------?
....
WHEN 'Active' THEN 2 <-------------?
What are these lines doing? Thank you all for helping me!

This abomination is what establishes the sort order in the output result.

(3)
Can we modify the CASE statement to handle this issue dynamically? PPL_Areas will grow over time and the code needs to be able to report on all areas, as the table grows.

Yes but the only real way to do it is to put the PPL_Areas into a table with a column that specifies the sort order for you. I'll do this in my next example and try to explain how you can convert what I give you to something that is maintainable without modifying the query.

For now, my first focus is going to be on making the business rule (your question 1) work.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Dwains does pretty much exactly what needs to be done even with the additional business rules, with the exception that it errors in two places:
WA_Traffic Proposed should be 30-60 days, his tallies under <30 days.
WA_Parent Relocation Proposed shld be 30-60 days, his tallies <30 days.

I think my whole problem here was the DESC on the PARTITION BY statement. I need to clean up the result based on your new data.

This is post 2 of 3.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

I also changed the ORDER BY on the PARTITION to be Change_Date instead of revision, because I noticed one case where change date decreased on an increasing revision number. Look at revisions 4 and 5 of Family/WA_Parent_Relocation for the example.

Here's is the revised and slightly simplified query.