Partition by or Row num or Subquery - Assistance

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 17, 2017 at 9:30 am

  #1951190

  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

• Danii_Pink

  Right there with Babe

  Points: 778

  More actions

  July 17, 2017 at 3:34 pm

  #1951284

  John Mitchell-245523 - Monday, July 17, 2017 9:21 AM

  This should get you started.  It's not completely as you asked, but I ran out of time.  If you've never used windowing functions then you'll need to read about them and understand how they work.

  WITH NumberedforCompletion AS (
    -- Get the no of completions so far per Identifier
    SELECT *
    , SUM(CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier ORDER BY CompletedDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CompsSoFar
    FROM #temp
    where SN_Type = 'Re-Activattion'
    ) --select * from NumberedforCompletion
, ReverseNumbered AS (
    -- Reverse the numbering so that the most recent is numbered 1
    SELECT *, DENSE_RANK() OVER (PARTITION BY Identifier ORDER BY COALESCE(CompssoFar,0) DESC) AS ReverseNo
    FROM NumberedforCompletion
    )
, DateExtremes AS (
    select distinct Identifier,
    MAX(CompletedDate) OVER (PARTITION BY Identifier, ReverseNo) AS FinishDate,
    MIN(CreatedDate) OVER (PARTITION BY Identifier, ReverseNo) AS StartDate,
    SUM(CASE WHEN SN_Status = 'SENT' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier, ReverseNo) AS SentCount,
    SUM(CASE WHEN SN_Status = 'N-CO' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier, ReverseNo) AS NCoCount
    from ReverseNumbered
    )
SELECT
    Identifier
,    StartDate
,    CASE WHEN StartDate > FinishDate
        THEN NULL
        ELSE DATEDIFF(day,StartDate,FinishDate)
    END AS RE_ACT_COMP_TIME
,    NCoCount
,    SentCount
FROM DateExtremes

  John

  WOW JOHN, thats almost it!
  Just one row I cant figure out how you got the value:
  
  Could you please help me out? 
  APPRECIATE ALL YOUR ASSISTANCE INCREDIBLY!

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 18, 2017 at 2:24 am

  #1951318

  Yes, I think that must have been the aspect that I ran out of time on.  The reason I included the CTE that reverses the order was so that rows like that would always be numbered 1.  What I didn't work out was how not to include items that were completed as well.  I'm going to leave it to you to work out how to get over the line.  I'm guessing you're not too familiar with syntax like this, so it'll be a good opportunity for you to understand how it all works.  It'll be you supporting the code, after all.  Good luck!

  John

• Danii_Pink

  Right there with Babe

  Points: 778

  More actions

  July 18, 2017 at 2:41 am

  #1951320

  John Mitchell-245523 - Tuesday, July 18, 2017 2:23 AM

  Yes, I think that must have been the aspect that I ran out of time on.  The reason I included the CTE that reverses the order was so that rows like that would always be numbered 1.  What I didn't work out was how not to include items that were completed as well.  I'm going to leave it to you to work out how to get over the line.  I'm guessing you're not too familiar with syntax like this, so it'll be a good opportunity for you to understand how it all works.  It'll be you supporting the code, after all.  Good luck!

  John

  Haha 
  Thanks John, appreciate the help. 
  I will pull my hair out figuring out that part but always up for learning something new.
  As long as you are 100% it's possible.