December 2, 2016 at 7:57 am
sac.nan (12/2/2016)
John Mitchell-245523 (12/2/2016)
Yes, the answer was kind of "that's the requirement", which is fair enough, I suppose. I'd still love to know what this is for, though. The only way I could see of starting was by doing a self-join on ID = ID+1, but that didn't get me all the way there and I'm afraid I don't have all afternoon to spend on this.John
Unfortunately I am way down the value chain and in no position to question the requirements :crying:
And thank you both John and Livingston for the help.Greatly appreciate your efforts.
There is a fairly simple solution to this. You just also need to know where a group ends, and then add those values as additional records, and just take a good close look at how this is put together and you should see how it works.
WITH CHANGE_STARTS AS (
SELECT TOP 100 PERCENT id, p2pid, p2psource,
CASE WHEN LAG(p2psource, 1) OVER(ORDER BY id) = p2pid THEN 0 ELSE 1 END AS GRP,
CASE WHEN LEAD(p2pid, 1, -9999) OVER(ORDER BY id) <> p2psource THEN 1 ELSE 0 END AS END_GRP
FROM dbo.p2p
ORDER BY id
),
GROUPS AS (
SELECT CS.*, SUM(CS.GRP) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS GRP2
FROM CHANGE_STARTS AS CS
),
NUMBERED_GROUPS AS (
SELECT G.*,
ROW_NUMBER() OVER(PARTITION BY G.GRP2 ORDER BY id) AS RN
FROM GROUPS AS G
)
SELECT X.p2pid, X.RN
FROM (
SELECT NG.id, NG.p2pid, RN
FROM NUMBERED_GROUPS AS NG
UNION ALL
SELECT NG2.id, NG2.p2psource AS p2pid, NG2.RN + 1 AS RN
FROM NUMBERED_GROUPS AS NG2
WHERE NG2.END_GRP = 1
) AS X
ORDER BY X.id, X.RN;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 2, 2016 at 8:00 am
Please note that the value -9999 for the default value to use when the LEAD value for the desired field IS NULL, has to NOT EXIST as a valid value anywhere in your data. It appears your data contains no negative numbers, and it seems to be a safe assumption, but caveat emptor, so to speak...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 2, 2016 at 8:23 am
sac.nan (12/2/2016)
@sgmunsonWow...Fantastic..This is exactly what I need...
I would show this solution to the management so they understand how tough the solution is and I am sure it would take ages for them to decipher it..
Glad I could help. Be sure to look up how LEAD and LAG work. They both allow for an optional "default" value, which is chosen whenever the record that LEAD or LAG is trying to reference has a NULL value, which is going to be the case whenever there is no such record, such as when you want LEAD(field, 1) when the record you are on is the last one, so there is no next record, or for LAG(field, 1) when the record you are on is the first one, so there is no previous record.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 2, 2016 at 8:40 am
sgmunson (12/2/2016)
sac.nan (12/2/2016)
John Mitchell-245523 (12/2/2016)
Yes, the answer was kind of "that's the requirement", which is fair enough, I suppose. I'd still love to know what this is for, though. The only way I could see of starting was by doing a self-join on ID = ID+1, but that didn't get me all the way there and I'm afraid I don't have all afternoon to spend on this.John
Unfortunately I am way down the value chain and in no position to question the requirements :crying:
And thank you both John and Livingston for the help.Greatly appreciate your efforts.
There is a fairly simple solution to this. You just also need to know where a group ends, and then add those values as additional records, and just take a good close look at how this is put together and you should see how it works.
WITH CHANGE_STARTS AS (
SELECT TOP 100 PERCENT id, p2pid, p2psource,
CASE WHEN LAG(p2psource, 1) OVER(ORDER BY id) = p2pid THEN 0 ELSE 1 END AS GRP,
CASE WHEN LEAD(p2pid, 1, -9999) OVER(ORDER BY id) <> p2psource THEN 1 ELSE 0 END AS END_GRP
FROM dbo.p2p
ORDER BY id
),
GROUPS AS (
SELECT CS.*, SUM(CS.GRP) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS GRP2
FROM CHANGE_STARTS AS CS
),
NUMBERED_GROUPS AS (
SELECT G.*,
ROW_NUMBER() OVER(PARTITION BY G.GRP2 ORDER BY id) AS RN
FROM GROUPS AS G
)
SELECT X.p2pid, X.RN
FROM (
SELECT NG.id, NG.p2pid, RN
FROM NUMBERED_GROUPS AS NG
UNION ALL
SELECT NG2.id, NG2.p2psource AS p2pid, NG2.RN + 1 AS RN
FROM NUMBERED_GROUPS AS NG2
WHERE NG2.END_GRP = 1
) AS X
ORDER BY X.id, X.RN;
nice one Steve
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 2, 2016 at 8:44 am
J Livingston SQL (12/2/2016)
nice one Steve
Thank you, kind sir!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 2, 2016 at 8:55 am
sgmunson (12/2/2016)
Glad I could help. Be sure to look up how LEAD and LAG work. They both allow for an optional "default" value, which is chosen whenever the record that LEAD or LAG is trying to reference has a NULL value, which is going to be the case whenever there is no such record, such as when you want LEAD(field, 1) when the record you are on is the last one, so there is no next record, or for LAG(field, 1) when the record you are on is the first one, so there is no previous record.
Yes Sir I will do that..
Your solution is very elegant indeed..Hats of to you..
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply