Max Min With Sum Between

Danii_PINK · July 11, 2017, 11:26pm

Hi

I have the below query (which bitsmed and Ifor helped me out with).

I am trying to make these sums:

, sum(case when (SN_STATUS = 'SENT' and SN_TYPE = 'Re-Activattion')   then 1 else 0 end) 'RE-AN SENT #'
     , sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion')   then 1 else 0 end) 'RE-AN ACCE #'
      , sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
	   , sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'

Only sum when when they appear between:

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

The full code:

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','REJE')
INSERT INTO #temp
VALUES('02031861999','64074558792','20160812','20160812','Re-Activattion','SENT')
INSERT INTO #temp
VALUES('03031861999','64074558792','20160811','20160811','Re-Activattion','COMP')
;
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
       
       	, sum(case when (SN_STATUS = 'SENT' and SN_TYPE = 'Re-Activattion')   then 1 else 0 end) 'RE-AN SENT #'
	     , sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion')   then 1 else 0 end) 'RE-AN ACCE #'
	      , sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
		   , sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'
            
  from #temp
  WHERE Identifier = '64074558792'
 group by identifier
;

SENT is 0 as it did not happen between the specified date of when the Re-Activation was first created to when it was completed.

Any help would be appreciated.

Thanks,
Danii

stepson · July 12, 2017, 6:40am

How about creating a new field in your query , RE_ACT_SENT_TIME that is similar to 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='SENT'
											)
									   then CreatedDate
									   else null
									end
								   )
							   )
					  )
			 else '0'
		   end
		   as RE_ACT_SENT_TIME

and then a outer query extracting data from your original query + this field.

SELECT 
	Identifier
	,RE_ACT_COMPLETION_TIME
	,CASE WHEN RE_ACT_COMPLETION_TIME < RE_ACT_SENT_TIME THEN 0 ELSE 1 END AS [RE-AN SENT #]
	,[RE-AN ACCE #]
	,[RE-AN N-CO #]
	,[RE-AN REJE #]
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='SENT'
											)
									   then CreatedDate
									   else null
									end
								   )
							   )
					  )
			 else '0'
		   end
		   as RE_ACT_SENT_TIME

       		, sum(case when (SN_STATUS = 'SENT' and SN_TYPE = 'Re-Activattion' )  then 1 else 0 end) 'RE-AN SENT #'
			 , sum(case when (SN_STATUS = 'ACCE' and SN_TYPE = 'Re-Activattion')   then 1 else 0 end) 'RE-AN ACCE #'
			  , sum(case when (SN_STATUS = 'N-CO' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN N-CO #'
			   , sum(case when (SN_STATUS = 'REJE' and SN_TYPE = 'Re-Activattion') then 1 else 0 end) 'RE-AN REJE #'
            
			
	  from #temp
	  WHERE Identifier = '64074558792'
	 group by identifier
)A
;

Danii_PINK · July 13, 2017, 12:05am

Thanks stepson, looks good.

Danii_PINK · July 13, 2017, 1:41am

Hi Stepson

Sorry another question

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
;

stepson · July 13, 2017, 6:18am

SELECT 
	Identifier
	,RE_ACT_COMPLETION_TIME
	,COUNT(CASE WHEN RE_ACT_COMPLETION_TIME < RE_ACT_NCO_TIME THEN 0 ELSE 1 END) AS [RE-AN NCO #]
	,COMP_CompletedDate AS [Count_N-CO]
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

		   ,SUM(CASE WHEN t.CreatedDate <= OA.COMP_CompletedDate AND t.SN_Status ='N-CO' THEN 1 ELSE 0 END) AS COMP_CompletedDate
			
	  from #temp AS t
			OUTER  APPLY
			(SELECT TOP(1) ot.CompletedDate AS COMP_CompletedDate
				FROM #temp AS ot
				WHERE
					t.Identifier=ot.Identifier					
					AND ot.CreatedDate >= t.CreatedDate 
					AND ot.SN_TYPE = 'Re-Activattion'
					AND ot.SN_STATUS='COMP'
				ORDER BY ot.CompletedDate ASC
			 )OA
	  WHERE Identifier = '64074558792'
	 group by identifier
)A
GROUP BY 
	Identifier
	,RE_ACT_COMPLETION_TIME
	,COMP_CompletedDate

Danii_PINK · July 13, 2017, 7:04am

Thanks stepson

Took me a little while to understand but make sense now, thank you.

Running on a larger data-set, is there any way of doing it without the sub query because it seems quiet inefficient at retrieving the results?

stepson · July 13, 2017, 7:07am

Probably yes, I will have to think about it (this version was the first that come in my head )

Danii_PINK · July 13, 2017, 7:09am

Appreciate your help on this! Looking forward to seeing another way.

stepson · July 13, 2017, 7:48am

What version of sql server do you use ?

Danii_PINK · July 13, 2017, 7:50am

Microsoft SQL Server Management Studio 11.0.2100.60

stepson · July 13, 2017, 8:04am

You are using sql server 2012 , so I implemented this, re-write the original one ...

SELECT  
	t.Identifier
	,MIN(DATEDIFF(DAY,t.start_Time, COMP_Time)) AS [RE_ACT_COMPLETION_TIME]
	,COUNT(CASE WHEN t.CreatedDate <= t.COMP_Time AND t.SN_STATUS= 'N-CO' THEN 1 ELSE NULL END) AS [RE-AN NCO]
FROM
(
	SELECT 
		t.Identifier,t.CreatedDate,t.CompletedDate ,t.SN_Type,t.SN_Status
		,MIN(CASE WHEN SN_TYPE = 'Re-Activattion' AND  SN_STATUS<>'COMP' THEN t.CreatedDate ELSE NULL END) 
				OVER(PARTITION BY t.Identifier 
						ORDER BY t.CreatedDate, t.CompletedDate
						ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
			) AS start_Time
		,MIN(CASE WHEN SN_TYPE = 'Re-Activattion' AND  SN_STATUS='COMP' THEN t.CompletedDate ELSE NULL END) 
				OVER(PARTITION BY t.Identifier 
						ORDER BY t.CreatedDate, t.CompletedDate
						ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
			) AS COMP_Time

	FROM  #temp AS T 
)T
GROUP BY t.Identifier

Danii_PINK · July 13, 2017, 8:15am

Thanks, giving it a shot now but doesn't seem to like something near ROWS

stepson · July 13, 2017, 8:19am

> select compatibility_level from sys.databases where name ='YourDBName'

whatt is the compatiblity level of your db ?

ROWS | RANGE

Applies to: SQL Server 2012

Danii_PINK · July 13, 2017, 8:21am

stepson · July 13, 2017, 8:22am

yep, this was . This means is 2000 ,2005 . Back to redraw table

Danii_PINK · July 13, 2017, 12:29pm

Any luck?

stepson · July 13, 2017, 1:23pm

Sorry ,I didn't found something in one go , different then the approach with outer apply

Danii_PINK · July 14, 2017, 12:21am

How about pushing it in a temp table and querying of that to improve the performance?

stepson · July 14, 2017, 3:31am

To improve performance, first I'm thinking about index(es) , what indexes do you have?
Can you add index(es) to that table?

Danii_PINK · July 14, 2017, 6:10am

Cant add indexes (times out)

Putting performance to the side
If I wanted to add a tolerance of 2 weeks to each identifier so i know which groups of statuses to count.
How would I do that using your code?
The picture below will put it into perspective.

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