Island Group, Regroup Sample Query


  

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
)
;
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','COMP');
INSERT INTO #temp
VALUES('64074558792','20160811','20160813','Re-Activattion','N-CO');
 
--INSERT INTO #temp
--VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
--INSERT INTO #temp
--VALUES('64074558792','20160911','20160911','Re-Activattion','COMP');
--INSERT INTO #temp
--VALUES('64074558792','20160912','20160913','Re-Activattion','N-CO');
--;

;WITH Src AS (
SELECT Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
 ROW_NUMBER() OVER(PARTITION BY Identifier,SN_Status ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) AS rn,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) -
 ROW_NUMBER() OVER(PARTITION BY Identifier,CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END ORDER BY CreatedDate, CompletedDate) AS grp
 FROM #temp
 
)
,Grouped as (
select * , dense_rank()Over(Partition by Identifier,SN_Status  Order by grp) grp2
from Src)

Select Identifier,    
Case when SUM(CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END) >0 then 
CAST( DATEDIFF(day,min(CreatedDate) ,max(CreatedDate)  )  AS VARCHAR(25))
ELSE 'NOT COMPLETED' END AS RE_ACT_COMPLETION_TIME 

,Sum(CASE WHEN SN_Status = 'N-CO' THEN 1 ELSE 0 END) as [RE-AN NCO #]

,Sum(CASE WHEN SN_Status = 'PARTIALLY' THEN 1 ELSE 0 END) as [RE-AN PART#]
 
from Grouped
Group by Identifier, grp2 
order by min(CreatedDate)


drop table #temp


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50edadfb-60d8-407c-b72e-d024e83fbf8e/rownumber-over-partition-assistance?forum=transactsql

Advertisements

One Comment on “Island Group, Regroup Sample Query”

  1. Danii says:

    Hi Jingyang Li

    Is it possible to add Tolerances to the islands?

    I am trying to group the identifiers when they are within a 2 weeks tolerance.

    (Table below)
    So 61030203647 Re-Act requests were create din 2016 and then in 2017 (past 2 weeks so they can be considered as 2 separate issues raised (should be 5 days not 294 (from 22nd (min created date) to 27th (max completed date))

    However 64074558792, completed on the 11.08 and created on the 31.07 THEN another Re-Act was created on the 12. This can me a mistake, this the 2 weeks tolerance, if its within 2 weeks of the completion date treat them as the same issue (unlike the example above which was past 2 weeks)

    Hope this make sense, I have been thinking about this for a while and asked a few people but so one has been able to steer me in the right direction when it comes to adding tolerance to groups.

    Trying to achieve this:

    Identifier RE_ACT_COMPLETION_TIME RE-AN NCO # RE-AN SENT#
    61030203647 5 2 0
    61030203647 0 0 0
    61030203688 NOT COMPLETED 0 1
    61030203699 NOT A RE-ACT 0 0
    64074558792 13 3 0

    CREATE TABLE #temp
    (
    Identifier varchar(20)NOT NULL
    ,CreatedDate DATETIME NOT NULL
    ,CompletedDate DATETIME NOT NULL
    ,SN_Type varchar(20) NOT NULL
    ,SN_Status varchar(20) NOT NULL
    )
    ;
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160731′,’20160805′,’Re-Activattion’,’N-CO’);
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160801′,’20160805′,’Re-Activattion’,’PARTIALLY’);
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160809′,’20160809′,’Re-Activattion’,’PARTIALLY’);
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160810′,’20160810′,’Re-Activattion’,’N-CO’);
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160810′,’20160810′,’Re-Activattion’,’N-CO’);
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160811′,’20160811′,’Re-Activattion’,’COMP’);
    INSERT INTO #temp
    VALUES(‘64074558792′,’20160812′,’20160814′,’Re-Activattion’,’N-CO’);
    INSERT INTO #temp
    VALUES (‘61030203647′,’20160427′,’20160427′,’Re-Activattion’, ‘COMP’);
    INSERT INTO #temp
    VALUES(‘61030203647′,’20160425′,’20160426′,’Re-Activattion’, ‘N-CO’);
    INSERT INTO #temp
    VALUES(‘61030203647′,’20160422′,’20160422′,’Re-Activattion’, ‘N-CO’);
    INSERT INTO #temp
    VALUES(‘61030203647′,’20170210′,’20170210′,’Re-Activattion’, ‘COMP’);
    INSERT INTO #temp
    VALUES(‘61030203688′,’20170409′,’20170210′,’Re-Activattion’, ‘SENT’);
    INSERT INTO #temp
    VALUES(‘61030203699′,’20170409′,’20170210′,’De-Activattion’, ‘COMP’);
    ;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s