SQLTeam.com | Weblogs | Forums

Partition by or Row num or Subquery - Assistance

sql2008
sql2012

#1

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 that time.
Below is an image of the sample data as well as the sql for the table.
Hope somebody can provide a little help.

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','SENT');
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 ('61030203647','20160427','20160427','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('61030203647','20160425','20160425','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('61030203647','20160422','20160422','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('61030203647','20170210','20170210','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('61030203688','20170409','20170210','Re-Activattion', 'SENT');
INSERT INTO #temp
VALUES('61030203699','20170409','20170210','De-Activattion', 'COMP');

Sum between dates
#2

I think the reason why no one has taken a shot at this yet is that the graphics, as nice as they are, don't seem to make any sense. For example, you say that there are 2 N-CO's between the first SENT and the first COMP, but the N-COs listed actually have dates that are one day later than the first COMP. In other words, the example makes no sense compared to what you wrote in the text of the post.