Sub-query inefficient (alternative help)

• Danii_Pink

  Right there with Babe

  Points: 778

  More actions

  July 13, 2017 at 8:29 am

  #402287

  Hi

  Is there any way of doing the count (number of N-CO(not completes) until COMP (completion)) without using the subquery because it seems quiet inefficient at retrieving the results?

  CREATE TABLE #temp
(
ServiceNumber varchar(20) NOT NULL
,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('01031861999','64074558792','20160729','20160805','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('02031861999','64074558792','20160810','20160810','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('02031861999','64074558792','20160812','20160812','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('03031861999','64074558792','20160811','20160811','Re-Activattion','COMP')
INSERT INTO #temp
VALUES('03031861999','64074558792','20160811','20160813','Re-Activattion','N-CO')
;

  SELECT
    Identifier
    ,RE_ACT_COMPLETION_TIME
    ,COUNT(CASE WHEN RE_ACT_COMPLETION_TIME < RE_ACT_NCO_TIME THEN 0 ELSE 1 END) AS [RE-AN NCO #]
    ,COMP_CompletedDate AS [Count_N-CO]
FROM
(
    select identifier
                ,case
             when sum(case when SN_STATUS='COMP' and SN_TYPE = 'Re-Activattion' then 1 else 0 end)>0
             then str(datediff(day
                             ,min(case
                                     when SN_TYPE = 'Re-Activattion'
                                     then CreatedDate
                                     else null
                                    end
                                 )
                             ,min(case
                                     when (SN_TYPE = 'Re-Activattion'
                                        and SN_STATUS='COMP'
                                            )
                                     then CompletedDate
                                     else null
                                    end
                                 )
                             )
                     )
             when sum(case when SN_TYPE='Re-Activattion' then 1 else 0 end)>0
             then 'NOT COMP'
             else 'NO RE-ACT'
         end
         as RE_ACT_COMPLETION_TIME
  
         ,case
             when sum(case when SN_STATUS='COMP' and SN_TYPE = 'Re-Activattion' then 1 else 0 end)>0
             then str(datediff(day
                             ,min(case
                                     when SN_TYPE = 'Re-Activattion'
                                     then CreatedDate
                                     else null
                                    end
                                 )
                             ,min(case
                                     when (SN_TYPE = 'Re-Activattion'
                                        and SN_STATUS='N-CO'
                                            )
                                     then CreatedDate
                                     else null
                                    end
                                 )
                             )
                     )
             else '0'
         end
         as RE_ACT_NCO_TIME

           ,SUM(CASE WHEN t.CreatedDate <= OA.COMP_CompletedDate AND t.SN_Status ='N-CO' THEN 1 ELSE 0 END) AS COMP_CompletedDate
            
     from #temp AS t
            OUTER APPLY
            (SELECT TOP(1) ot.CompletedDate AS COMP_CompletedDate
                FROM #temp AS ot
                WHERE
                    t.Identifier=ot.Identifier                    
                    AND ot.CreatedDate >= t.CreatedDate
                    AND ot.SN_TYPE = 'Re-Activattion'
                    AND ot.SN_STATUS='COMP'
                ORDER BY ot.CompletedDate ASC
             )OA
     WHERE Identifier = '64074558792'
     group by identifier
)A
GROUP BY
    Identifier
    ,RE_ACT_COMPLETION_TIME
    ,COMP_CompletedDate

  

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 13, 2017 at 9:14 am

  #1950768

  In your sample data, I reckon the third and fourth rows might contain a date error. Are you sure the count should be 3? I make it 2, because there are only two n-CO rows before the COMP row, whichever of the dates you choose to order by. I think you're struggling with a "Gaps'n'Islands" problem, and I also think you'd benefit from a substantially larger data set which would provide would-be helpers with a richer visual cue-space, which helps immensely with this type of problem.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden