Post reply

Max Min With Sum Between

  • July 11, 2017 at 6:30 pm

    #343466

    Hi

    I have the below query (which bitsmed and Ifor helped me out with).

    I am trying to make these sums:

    , sum(case when (SN_STATUS = 'SENT' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN SENT #' 
    , sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN ACCE #'  
    , sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
    , sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'

    Only sum when when they appear between:

    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 ) )

    Complete code:
    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','REJE')
    INSERT INTO #temp
    VALUES('02031861999','64074558792','20160812','20160812','Re-Activattion','SENT')
    INSERT INTO #temp
    VALUES('03031861999','64074558792','20160811','20160811','Re-Activattion','COMP')
    ;
    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
      
           , sum(case when (SN_STATUS = 'SENT' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN SENT #'
          , sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN ACCE #'
          , sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
             , sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'
        
    from #temp
    WHERE Identifier = '64074558792'
    group by identifier
    ;

    SENT is 0 as it did not happen between the specified date of when the Re-Activation was first created to when it was completed.

    Any help would be appreciated.

    Thanks,
    Danii

  • July 17, 2017 at 9:32 am

    #1951195

    Jacob Wilkins - Monday, July 17, 2017 7:15 AM

    Also, just as a note, please do not post the same topic multiple times. 

    You've posted several versions of this question under sub-forums for different versions of SQL Server.

    Posting a brand new topic means any information from the older posts is gone, including very helpful things like answers you've given to other members' questions. At a minimum it clutters up the forums.

    On that note, what version of SQL Server are you actually using?

    Cheers!

    The same question or variants thereof has been posted 8 times. In some cases the OP has started a new thread with responders questions left unanswered. Very inefficient, very frustrating for responders.

    “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

Viewing 2 posts - 1 through 1 (of 1 total)

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