Sum when between

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  July 14, 2017 at 12:52 pm

  #1950998

  I am working on this, I am just hitting snags.  Think I need to re-think how to solve this.
  I have it mostly working until a second COMP shows up.  That is complicating things for me.

  Unfortunately, I'm having difficulty getting my brain to see how to organize things into a manner where I can easily identify the individual requests.
  I am also failing to see the benefit of the "Service Number" column.  It doesn't seem to add any benefit to the table.

  I'm sorry I wasn't able to help you more, but this seems to be beyond what I am capable of :(.

  EDIT - the solution by Mark Crowne though looks like it does everything you require (presuming a "COMP" exists).  3 COMP's and 3 rows returned with the calculations looking correct to me, no?  It is working based on the assumption that a request starts with a N-CO and ends with a COMP mind you, not on the "2 weeks means it is a new request" as you stated.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• Danii_Pink

  Right there with Babe

  Points: 778

  More actions

  July 14, 2017 at 4:46 pm

  #1951017

  Hi bmg002
  Yeah the service number serves no purpose.
  I appreciate your input with this and trying to figure it out.

  Just one last thing before I let you go.

  Mark Crownes query, like you said its for providing if COMP exists, how can i ammend it to give me results below even when COMP does not exist.

  

  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','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('64074550792','20160811','20160813','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('6407455192','20160811','20160813','De-Activattion','N-CO');

  WITH Src AS (
SELECT Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
ROW_NUMBER() OVER(PARTITION BY Identifier 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 Identifier, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END AS IsCOMP,
MIN(CreatedDate) AS StartDate,
COUNT(*) AS [RE-AN NCO #],
MAX(rn) AS LastRn
FROM Src
GROUP BY Identifier, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, grp
)
SELECT s.Identifier,
DATEDIFF(day,g.StartDate,s.CreatedDate) AS RE_ACT_COMPLETION_TIME,
g.[RE-AN NCO #]
FROM Src s
INNER JOIN Grouped g ON g.Identifier = s.Identifier
AND g.IsCOMP = 0
AND g.LastRn + 1 = s.rn
WHERE s.SN_Status = 'COMP'
ORDER BY rn;

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  July 17, 2017 at 8:38 am

  #1951176

  Offhand, I am not entirely sure.  Mark would be a better one to ask, but looking at his code, I'm not sure there is a nice way to match that up.  His query, like mine, is based on the assumption that there is a COMP following the N-CO.

  What I would start with if you are using Mark's query is to add a ",*" and comment out the WHERE clause to the last select so you can see all of the data that is being returned by the query and then see if you can find a way to identify a N-CO without a COMP.  If you can, then put that in as an "OR" in the WHERE clause.  If you can't, you may need to build something a bit more complex for figuring out which is which "request".

  EDIT - got something, but I am expecting that this isn't exactly what you are looking for as your sample results keep adding in more and more columns.  But I'm hoping this gives you something to work with:
  
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('01031861999','64074558792','20160729','20160805','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('01031861999','64074558792','20160809','20160809','Re-Activattion','PARTIALLY');
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');
INSERT INTO #temp
VALUES ('318403853','61030203647','20160427','20160427','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('318616723','61030203647','20160427','20160427','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('318637013','61030203647','20160422','20160422','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('318639191','61030203647','20170210','20170210','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('318639191','61030203647','20170110','20170110','Re-Activattion', 'N-CO');
;
;
WITH Src AS (
SELECT ServiceNumber, Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
ROW_NUMBER() OVER(PARTITION BY Identifier 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 Identifier, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END AS IsCOMP,
MIN(CreatedDate) AS StartDate,
COUNT(*) AS [RE-AN NCO #],
MAX(rn) AS LastRn
FROM Src
GROUP BY Identifier, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, grp
),
grouped2 AS (SELECT Identifier, MAX(rn) AS maxRN
FROM [Src]
GROUP BY [Src].[Identifier])
    
SELECT s.Identifier,
    CASE WHEN isComp = 0
    THEN
        CAST(DATEDIFF(day,g.StartDate,s.CreatedDate) AS VARCHAR(25))
    ELSE
        'NOT COMPLETED'
    END AS RE_ACT_COMPLETION_TIME,
g.[RE-AN NCO #]
FROM Src s
INNER JOIN Grouped g ON g.Identifier = s.Identifier
AND g.LastRn + 1 = s.rn
JOIN grouped2 g2 ON [g2].[Identifier] = .[Identifier]
WHERE s.SN_Status = 'COMP'
    OR (SN_Status <> 'COMP' AND maxRN = .[rn])
ORDER BY rn;
DROP TABLE #temp

  If you could post sample data that contains all possible values for things (such as SN_Type which currently feels like a wasted column as it is always "Re-Activattion" which looks like a typo to me?), and what your final results are expected to look like, we can likely be more help.  But as each time you get a potential solution, you are adding a new requirement, it does make it tricky to give you a good answer.  
  I am also unsure how many people are like me, but when I see a forum thread has multiple pages of responses, it is less likely I am going to go in and look at it.
  I also agree with ChrisM@Work, opening multiple threads for the same problem does not usually get you more or better responses; it usually just makes things confusing and messy.

  Have you tried manipulating the queries that we have provided to get the results you are expecting?

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 17, 2017 at 9:31 am

  #1951193

  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