Post reply

Partition by or Row num or Subquery - Assistance

  • July 16, 2017 at 5:56 pm

    #402309

    Hi

    I have the below problem.

    I am trying to see how often a customer has requested Re-Activation of their Internet account.
    The problem is, we capture a limited set of data to group on.
    So my data set is below.
    I am trying to Count from the first time a Re-Activation request was created until the First time it was COMPLETED, once it has been completed finish the count of days it took for the request to complete and count the number of NON COMPLETIONS and SENT statuses which occurred between that time.
    Below is an image of the sample data as well as the sql for the table.
    Hope somebody can provide a little help.


    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','SENT');
    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 ('61030203647','20160427','20160427','Re-Activattion', 'COMP');
    INSERT INTO #temp
    VALUES('61030203647','20160427','20160427','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');

  • July 17, 2017 at 9:32 am

    #1951194

    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