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 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','20160427','20160427','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'); -
Where you have two events with the same CompletedDate, how do you determine which one comes first?
John
-
Hi
The status does, as that what I would like to roll it up to.
Make sense?
If not which scenario do you mean? I can elaborate. -
To solve this, as far as I can see, we need to using windowing functions, which rely on an ORDER BY clause. My question is: if there's a tie on CompletedDate, what column do we then order on? If it's Status, what is the order of priority of the different statuses?
John
-
There is no particular order to the Statuses
It can be N-COMP before it is SENT or vise versa.
Its, a matter of ordering by completeddate column until it hits the first completion date. Summarice the datediff and counts of the Statuses which happened in between.
Then again if there are any statuses following that completiondate.
That being said, it may still not make much sense to you but I am open to suggestions of what you think may be the best way to proceed, providing you get the gist of what I am trying to do.
I tried something like this, however it stops once it hits the Completed status and does not provide the summary past that.
Also it inst the most efficient with the OUTER APPLYSELECT
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 -
Yes, I get the gist of what you're trying to do, but you still need a tiebreaker for rows that have the same CompletedDate. Why not include the time portion in that column? That would enable you to see what happened first.
John
-
Unfortunately the created date doesn't have a time stamp.
Given what you have in mind, what would the result look like given there is no timestamp to break the tie? -
It means, for example, that on 27th April, you don't know whether the COMP on the N-CO came first. IN such circumstances, depending on how the query is written, SQL Server will guess, perhaps using the intrinsic order of the data, perhaps using an index on one of the columns, or perhaps some other undocumented algorithm. There's no guarantee.
John
-
Hmmm I see, thats a brainier.
So let say for this example we change the N-Co date to prior 27th.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','20160426','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');
; -
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!
-
Apologize, I will stick to the same forum.
Perhaps it was just my urgency/frustration which made me resort to posting in multiple places.
I am using 2012 but apparently only 2005 compatibility.
So, 2005 is the answer. -
Seriously - can you not change the compatibility level to 2012? It's just too difficult if you can't use windowing functions.
John
-
Its work system/database
Unlikely I would be able to but I will give it a shot.
Meanwhile if you don't know any other way, could you give me a demo of how you would do it using this windowed function, I am not familiar with it at all. -
There are some decent general techniques for handling this, but I'm still not clear on the logic you want to implement.
For the Identifier ending in 792, for example, the image you attached explains that you want 2 N-COs counted as occurring until completion, but both the CreatedDate and CompletedDate for those 2 N-COs are August 12 2016, which is after the completion date for the COMP, which is August 11 2016.
In your most recent sample data set, you also include rows with a completed date months before the created date.
We really will need the logic you want to implement specified more clearly, along with consistent sample data and desired results to help much. Also, as John pointed out, if you don't have any clear business logic around handling the ties, you'll have to be prepared to accept erratic results.
If this is for a real-world problem, my guess is that you won't be able to just change the dates so there aren't ties any more 🙂
Cheers!
-
This should get you started. It's not completely as you asked, but I ran out of time. If you've never used windowing functions then you'll need to read about them and understand how they work.
WITH NumberedforCompletion AS (
-- Get the no of completions so far per Identifier
SELECT *
, SUM(CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier ORDER BY CompletedDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS CompsSoFar
FROM #temp
where SN_Type = 'Re-Activattion'
) --select * from NumberedforCompletion
, ReverseNumbered AS (
-- Reverse the numbering so that the most recent is numbered 1
SELECT *, DENSE_RANK() OVER (PARTITION BY Identifier ORDER BY COALESCE(CompssoFar,0) DESC) AS ReverseNo
FROM NumberedforCompletion
)
, DateExtremes AS (
select distinct Identifier,
MAX(CompletedDate) OVER (PARTITION BY Identifier, ReverseNo) AS FinishDate,
MIN(CreatedDate) OVER (PARTITION BY Identifier, ReverseNo) AS StartDate,
SUM(CASE WHEN SN_Status = 'SENT' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier, ReverseNo) AS SentCount,
SUM(CASE WHEN SN_Status = 'N-CO' THEN 1 ELSE 0 END) OVER (PARTITION BY Identifier, ReverseNo) AS NCoCount
from ReverseNumbered
)
SELECT
Identifier
, StartDate
, CASE WHEN StartDate > FinishDate
THEN NULL
ELSE DATEDIFF(day,StartDate,FinishDate)
END AS RE_ACT_COMP_TIME
, NCoCount
, SentCount
FROM DateExtremesJohn
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply