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
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

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

Group: General Forum Members
Points: 33949 Visits: 16649
Seriously - can you not change the compatibility level to 2012? It's just too difficult if you can't use windowing functions.

John
Danii_Pink
Danii_Pink
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 151
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.
Jacob Wilkins
Jacob Wilkins
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7339 Visits: 10101
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 Smile

Cheers!
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33949 Visits: 16649
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 DateExtremes


John
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40656 Visits: 20000
Jacob Wilkins - Monday, July 17, 2017 7:15 AM
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!

The same question or variants thereof has been posted 8 times. In some cases the OP has started a new thread with responders questions left unanswered. Very inefficient, very frustrating for responders.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Danii_Pink
Danii_Pink
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 151
John Mitchell-245523 - Monday, July 17, 2017 9:21 AM
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 DateExtremes


John

WOW JOHN, thats almost it!
Just one row I cant figure out how you got the value:

Could you please help me out?
APPRECIATE ALL YOUR ASSISTANCE INCREDIBLY!

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

Group: General Forum Members
Points: 33949 Visits: 16649
Yes, I think that must have been the aspect that I ran out of time on. The reason I included the CTE that reverses the order was so that rows like that would always be numbered 1. What I didn't work out was how not to include items that were completed as well. I'm going to leave it to you to work out how to get over the line. I'm guessing you're not too familiar with syntax like this, so it'll be a good opportunity for you to understand how it all works. It'll be you supporting the code, after all. Good luck!

John
Danii_Pink
Danii_Pink
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 151
John Mitchell-245523 - Tuesday, July 18, 2017 2:23 AM
Yes, I think that must have been the aspect that I ran out of time on. The reason I included the CTE that reverses the order was so that rows like that would always be numbered 1. What I didn't work out was how not to include items that were completed as well. I'm going to leave it to you to work out how to get over the line. I'm guessing you're not too familiar with syntax like this, so it'll be a good opportunity for you to understand how it all works. It'll be you supporting the code, after all. Good luck!

John

Haha
Thanks John, appreciate the help.
I will pull my hair out figuring out that part but always up for learning something new.
As long as you are 100% it's possible.

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