1. Find the starting INITIAL BEFORE VALUE and the LATEST AFTER VALUE

Question

1.00/5 (1 vote)

See more:

1. what is the INITIAL BEFORE VALUE - it should be found in the after values or earlier rows.
2. what is the LATEST AFTER VALUE - it should be found in the before value of subsequent rows
3. In this example there 4 input sets are separate and put in sequence but in actual scenario the rows can be populated in random

Input
Record 	Before Value	After value
1	101	102
2	102	105
3	105	108
4	108	289
5	289	109
6	109	109
7	109	110
8	110	110
		
9	201	201
10	201	202
11	203	301
12	301	150
13	150	150
		
14	86	86
15	86	123
16	123	97
17	97	97
18	97	784
		
19	269	269
20	38	38

Output
Initial Before Value	Latest After Value
101	110
201	150
86	784
269	269
38	38

What I have tried:

Output
Initial Before Value	Latest After Value
101	110
201	150
86	784
269	269
38	38

Posted 29-Apr-24 22:42pm

asha kumari 2024

Updated 29-Apr-24 22:49pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2024-04-29T23:11:00

You are missing so much context here that it is impossible for us to come up with a reasoned view of what you are trying to solve. In your example, I have to assume that the space between sections is indicating that these are the groups you need to find the first value and last value from but, there's no indication how this would work in a database context.

To be honest, this sounds like a homework question and you have missed out parts of what has been asked. As we can't see your screen, we have no idea what you need to do to complete this. Also, you haven't shown what you have tried - you say this is a SQL question but you haven't actually put any SQL in there.

M-Badger · Answer 2 · 2024-04-29T23:34:00

As Pete says, it is difficult to know what you are asking but here's my best guess.

You know that the "Before Value" of a row is taken from the "After Value" of the preceding row.

Row[N].Before_Value = Row[N-1].After_Value

A "group" of rows or an "input set" is terminated when this "rule" is not respected.

You can therefore traverse the rows of the table testing for this condition and when it is not true then you have found the end of one input set (the LATEST AFTER VALUE of the current input set) and the start of the next one (the INITIAL BEFORE VALUE of the next input set).

Doing that would give you the output that you have put into the question and the "What I have tried" section.

As an aside, there are 5 input sets and not 4 as you state in your question, this is shown in the output that you have provided.

As for what code would do that for you then you need to show what code you have tried (so people can help you improve it / fix it) or as an absolute minimum the language that you are supposed to be using.

OriginalGriff · Answer 3 · 2024-04-30T02:11:00

Solution 3

To add to what the others have said, you might want to look at LEAD and LAG - SQL Server | Microsoft Learn[^]

Unless this is homework, in which case you only want to use them if the tutor has covered them yet ...

Posted 30-Apr-24 2:11am

OriginalGriff

1. Find the starting INITIAL BEFORE VALUE and the LATEST AFTER VALUE

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0