Sum between dates

[Danii_PINK]

I am trying to do a count, to see how many times N-CO occurred until the Re-Act was completed.

Could you help out?

Expecting to see 3 not 1.

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
	,SUM(CASE WHEN RE_ACT_COMPLETION_TIME < RE_ACT_NCO_TIME THEN 0 ELSE 1 END) AS [RE-AN NCO #]

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

			
	  from #temp
	  WHERE Identifier = '64074558792'
	 group by identifier
)A
GROUP BY 
	Identifier
	,RE_ACT_COMPLETION_TIME
;

[Jason_A_Long]

Is this what you're after?

WITH 
	cte_OrderNCO AS (
		SELECT 
			*,
			RN = ROW_NUMBER() OVER (PARTITION BY t.Identifier ORDER BY t.CompletedDate DESC)
		FROM
			#temp t
		WHERE 
			t.SN_Status = 'N-CO'
		)
SELECT 
	nco.ServiceNumber, 
	nco.Identifier, 
	nco.CreatedDate, 
	nco.CompletedDate, 
	nco.SN_Type, 
	nco.SN_Status
FROM
	cte_OrderNCO nco
WHERE 
	nco.RN > 1
ORDER BY
	nco.ServiceNumber, 
	nco.Identifier, 
	nco.CreatedDate;

[Danii_PINK]

Hi Jason

Thanks for responding.

99% correct,

Just had to change

@Jason_A_Long

RN = ROW_NUMBER() OVER (PARTITION BY t.Identifier ORDER BY t.CompletedDate DESC)

to CreatedDate;

How would you summaries this now? to give something like:

[Danii_PINK]

Got it to count

However, for the sample data below its counting 1 instead of 2.

Why is that?

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 ('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')
;


SELECT *
FROM #temp
;




WITH 
	cte_OrderNCO AS (
		SELECT 
			*,
			RN = ROW_NUMBER() OVER (PARTITION BY t.Identifier ORDER BY t.CreatedDate DESC)
		FROM
			#temp t
		WHERE 
			t.SN_Status = 'N-CO'
		)
SELECT 
	nco.Identifier, 
	COUNT(*) as [NO OF N-CO]
FROM
	cte_OrderNCO nco
WHERE 
	nco.RN > 1
    GROUP By nco.Identifier
ORDER BY
	nco.Identifier 
	;

Query gives:

[Jason_A_Long]

1st question... In your original post, it appeared that you wanted 3 rows of data returned. An easily corrected misunderstanding that's addressed in the code below.

2nd question... The data sample in your original post indicated that you're only interested in "SN_Status = 'N-CO'" rows and the fact that it completed on an SN_Status = 'N-CO', led me to believe I could filter everything else out in the 1st step. Again, an easy fix

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

--==============================================================================

WITH 
	cte_OrderNCO AS (
		SELECT 
			*,
			RN = ROW_NUMBER() OVER (PARTITION BY t.Identifier ORDER BY t.CompletedDate DESC)
		FROM
			#temp t
		)
SELECT 
	nco.Identifier, 
	RE_ACT_COMPLETION = DATEDIFF(dd, MIN(nco.CreatedDate), MAX(nco.CompletedDate)),
	REANCO = COUNT(1)
FROM
	cte_OrderNCO nco
WHERE 
	nco.RN > 1
	AND nco.SN_Status = 'N-CO'
GROUP BY 
	nco.Identifier;

Hopefully, this will be closer to what you need.

[Danii_PINK]

Hi Jason,
Your way seems so much simpler than the way I have been trying, though it took me a while to understand your method.
Few more questions, if you could spare a moment.
1.I would like to add another status to be counted besides the N-CO
2.Tolerance, looking at when it was first created if it was completed within 2 weeks. Hope the diagram below explain it a little more.

(the second column is just the first (min(completeddate) status doesn't matter)

[Jason_A_Long]

It's way pas my bed time... I'll try to take a look at some point tomorrow...

[Danii_PINK]

Hi Jason, I reworded the question to make it more clear to answer, if you could have a look and let me know what your thoughts are would be greatly appreciated!

Partition by or Row num or Subquery - Assistance

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…