SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partition by or Row num or Subquery - Assistance


Partition by or Row num or Subquery - Assistance

Author
Message
Danii_Pink
Danii_Pink
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 168
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');

John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35055 Visits: 16670
Where you have two events with the same CompletedDate, how do you determine which one comes first?

John
Danii_Pink
Danii_Pink
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 168
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.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35055 Visits: 16670
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
Danii_Pink
Danii_Pink
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 168
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 APPLY

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

John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35055 Visits: 16670
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
Danii_Pink
Danii_Pink
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 168
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?
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35055 Visits: 16670
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
Danii_Pink
Danii_Pink
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 168
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');
;

Jacob Wilkins
Jacob Wilkins
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7586 Visits: 10183
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search