SQLTeam.com | Weblogs | Forums

Sum between dates


#1

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
;

#2

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;

#3

Hi Jason

Thanks for responding.

99% correct,

Just had to change

to CreatedDate;

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


#4

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:


#5

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.


#6

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)


#7

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


#8

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!