Selective Aggregation SQL; Not the regular Group by
Selective Aggregation SQL; Not the regular Group by
My Dataset looks like this . Alert levels follow a priority :
C > O > W
The open and closed Dates follow the alert level priorities .
Eg: An Alert level 'W' with an Closed Date greater than a Open Date of an Alert Level 'O' , will take the closed date of the open date of C. (I did this part using CTE) the resultant Data set is as below. Now , the challenge is the eliminate 2 consecutive same alert levels to only one with min of the 2 open dates and max of the 2 closed dates between them ( read first 2 'W')
Dataset :
ID axlenumber alertlevel closeReason OpenDate closeddate
100 1 W HIGHER_LEVEL_ALERT 10/15/2008 0:00 1/27/2009 0:00
100 1 W HIGHER_LEVEL_ALERT 1/22/2009 0:00 1/27/2009 0:00
100 1 O REPAIR 1/27/2009 0:00 3/27/2009 0:00
100 1 C REPAIR 1/27/2009 0:00 3/27/2009 0:00
100 1 W HIGHER_LEVEL_ALERT 5/27/2012 0:00 8/18/2012 0:00
100 1 W REPAIR 5/27/2012 0:00 8/18/2012 0:00
100 1 C REPAIR 5/27/2012 0:00 8/18/2012 0:00
100 1 W HIGHER_LEVEL_ALERT 6/22/2016 0:00 7/21/2017 0:00
100 1 O HIGHER_LEVEL_ALERT 7/2/2017 0:00 11/15/2017 0:00
100 1 C REPAIR 7/21/2017 0:00 11/15/2017 0:00
My desired Dataset should look like:
ID axlenumber alertlevel closeReason OpenDate closeddate
100 1 W HIGHER_LEVEL_ALERT 10/15/2008 0:00 1/27/2009 0:00
100 1 O REPAIR 1/27/2009 0:00 1/27/2009 0:00
100 1 C REPAIR 1/27/2009 0:00 3/27/2009 0:00
100 1 W REPAIR 5/27/2012 0:00 5/27/2012 0:00
100 1 C REPAIR 5/27/2012 0:00 8/18/2012 0:00
100 1 W HIGHER_LEVEL_ALERT 6/22/2016 0:00 7/2/2017 0:00
100 1 O HIGHER_LEVEL_ALERT 7/2/2017 0:00 7/21/2017 0:00
100 1 C REPAIR 7/21/2017 0:00 11/15/2017 0:00
Thanks in Advance Team,
Arvin
You could make it look like a dataset using create table ..., insert into ... statements. SqLFiddle would help you on that.
– Cetin Basoz
Jun 26 at 15:55
So you're saying that the first two rows of sample data are "consecutive", closing on 1/27/09 and opening on 1/22/09, and should be combined. The desired result row covers 10/15/08 to 1/27/09, not 10/15/08 to 3/27/09 as I would expect if the rows were combined. What am I missing? Please read this for some tips on improving your question.
– HABO
Jun 26 at 17:35
@HABO-- Thats my typo : its 1/27/2009 . My apologies.
– Itachiara
Jun 26 at 18:22
Okay. And "consecutive" means "overlapping in any way"? The fifth and sixth input rows are combined because they are both
alertlevel "W" and happen to have identical dates. The combination has a closeReason that is the shortest of the two rows' values, i.e. "REPAIR" rather than "HIGHER_LEVEL_ALERT", or is there some other basis for the choice?– HABO
Jun 26 at 21:46
alertlevel
closeReason
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Someone please help me with the edit and make it look like a dataset. Am not able to. Thanks and my apologies
– Itachiara
Jun 26 at 15:50