Tie row to previous rows of data

Question

Tie row to previous rows of data

Say Hey Kid

Points: 690

November 3, 2017 at 5:00 am

I am trying to associate the number value that has a description of SK to the previous rows up to the next SK row. In the first rows of the example below, the value "270641" would be placed in a field for sequence numbers 37-47. Can I do this in a query or an update statement into a table? Thank you

Sequence	Number	Description

37	873839	CN
38	43772	CN
39	29643	CN
40	480171	CN
41	702525	CN
42	632336	CN
43	400707	CN
44	656546	CN
45	172415	CN
46	102565	CN
47	241011	CN
48	270641	SK
49	965181	CN
50	439002	CN
51	960643	CN
52	913517	CN
53	747449	CN
54	706935	CN
55	686192	CN
56	818341	CN
57	700803	CN
58	688351	CN
59	270058	SK
60	829483	CN
61	931514	CN
62	437628	CN
63	671635	CN
64	904216	CN
65	764151	CN
66	400180	CN
67	989253	CN
68	931008	CN
69	906772	CN
70	237658	SK
71	433050	CN
72	332802	CN
73	864142	CN
74	793205	CN
76	793216	CN
77	40302	CN
78	95043	CN
79	794445	CN
80	793005	CN
81	250143	SK

{�_v�

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

You should know that you need to supply data with DDL and in a consumable format.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Thom A - Friday, November 3, 2017 5:15 AM
You should know that you need to supply data with DDL and in a consumable format.

And when you do this, can you confirm that the only factor which needs to be considered when ordering the rows of data is the 'Sequence' column?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 3

If I'm understanding this correctly, you want to offset the sequence based on the description and the sequence? If that's true, you, you can try to add a new sequence per description and order it by sequence. Treat the sequence you have now as the GlobalSequence for all descriptions. Then create a new field called a LocalSequence, which defines the sequence order per description. For example, the GlobalSequence for this data with SK would be 59, 70, and 81. The LocalSequence would be 1, 2, 3.

Then if you want to tie the 70th GlobalSequence to the 59th GlobalSequence for SK only, you can use the LocalSequence to help you do that by doing a LEFT JOIN and OFFSET the LocalSequence like so:

SELECT * FROM MyTable a LEFT JOIN MyTable b ON a.[Description] = b.[Description] AND a.[LocalSequence] = b.[LocalSequence] + 1

This will join the next sequence onto the previous sequence. For example, LocalSequence 1 will have LocalSequence 2 joined on it, effectively attaching the 70th GlobalSequence to the 59th GlobalSequence (i.e.: 1 = 2, 2 = 3, 4 = 5, etc). You can do the opposite with subtracting to tie 70th to the 59th. (2 = 1, 3 = 2, etc).

Use ROW_NUMBER and PARTITION OVER Description ORDER BY Sequence to create that LocalSequence.

Then for an UPDATE statement, you can then just do a typical UPDATE JOIN statement to use the JOIN above. But, I would create a new field for the prior and new values just so you can readjust accordingly.

I may be a bit rusty, but I think that's how I offsetted data before in sequence. Joining the data on top of each other then adding and subtracting the sequence on the JOIN. Do similar to do Month over Month for example.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

xsevensinzx - Friday, November 3, 2017 6:28 AM
If I'm understanding this correctly, you want to offset the sequence based on the description and the sequence? If that's true, you, you can try to add a new sequence per description and order it by sequence. Treat the sequence you have now as the GlobalSequence for all descriptions. Then create a new field called a LocalSequence, which defines the sequence order per description. For example, the GlobalSequence for this data with SK would be 59, 70, and 81. The LocalSequence would be 1, 2, 3.
Then if you want to tie the 70th GlobalSequence to the 59th GlobalSequence for SK only, you can use the LocalSequence to help you do that by doing a LEFT JOIN and OFFSET the LocalSequence like so:
SELECT * FROM MyTable a LEFT JOIN MyTable b ON a.[Description] = b.[Description] AND a.[LocalSequence] = b.[LocalSequence] + 1
This will join the next sequence onto the previous sequence. For example, LocalSequence 1 will have LocalSequence 2 joined on it, effectively attaching the 70th GlobalSequence to the 59th GlobalSequence (i.e.: 1 = 2, 2 = 3, 4 = 5, etc). You can do the opposite with subtracting to tie 70th to the 59th. (2 = 1, 3 = 2, etc).
Use ROW_NUMBER and PARTITION OVER Description ORDER BY Sequence to create that LocalSequence.
Then for an UPDATE statement, you can then just do a typical UPDATE JOIN statement to use the JOIN above. But, I would create a new field for the prior and new values just so you can readjust accordingly.
I may be a bit rusty, but I think that's how I offsetted data before in sequence. Joining the data on top of each other then adding and subtracting the sequence on the JOIN. Do similar to do Month over Month for example.

That's the traditional solution, but it requires two scans of the table. I think this is a classic Gaps and Islands problem.

John

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 5

xsevensinzx

One Orange Chip

Points: 25560

November 3, 2017 at 7:13 am

#1966554

Ahh nice, that works too!

Thom A SSC Guru Points: 99130 More actions · Answer 6

John Mitchell-245523 - Friday, November 3, 2017 6:55 AM
That's the traditional solution, but it requires two scans of the table. I think this is a classic Gaps and Islands problem.
John

Was thinking islands, but didn't use that for my idea:

WITH VTE AS (
  SELECT *
   FROM (VALUES (1,123123,'CN'),(2,18123,'CN'),(3,12371,'SK'),(4,1242358,'CN'),(5,96734,'SK')) V(S,N,D)),
Islands AS (
   SELECT *,
    COUNT(CASE D WHEN 'SK' THEN N END) OVER (ORDER BY S ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Grp
   FROM VTE)
SELECT S, N, D,
   FIRST_VALUE(N) OVER (PARTITION BY Grp ORDER BY S DESC) AS NGrp
FROM Islands;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

drew.allen SSC Guru Points: 76985 More actions · Answer 7

That's the traditional solution, but it requires two scans of the table. I think this is a classic Gaps and Islands problem.

John

Was thinking islands, but didn't use that for my idea:

WITH VTE AS (
  SELECT *
   FROM (VALUES (1,123123,'CN'),(2,18123,'CN'),(3,12371,'SK'),(4,1242358,'CN'),(5,96734,'SK')) V(S,N,D)),
Islands AS (
   SELECT *,
      COUNT(CASE D WHEN 'SK' THEN N END) OVER (ORDER BY S ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Grp
   FROM VTE)
SELECT S, N, D,
    FIRST_VALUE(N) OVER (PARTITION BY Grp ORDER BY S DESC) AS NGrp
FROM Islands;

This requires two sorts: one for the COUNT() and one for the FIRST_VALUE(). The following only requires one sort.

WITH Vals AS ( SELECT * FROM (VALUES (1,123123,'CN'),(2,18123,'CN'),(3,12371,'SK'),(4,1242358,'CN'),(5,96734,'SK')) V(Seq,Num,Descr) ) SELECT *, CAST(SUBSTRING(MIN(CAST(Seq AS BINARY(4)) + CAST(CASE WHEN Descr = 'SK' THEN Num END AS BINARY(4))) OVER(ORDER BY Seq ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 5, 4) AS INT) FROM Vals ;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA